Using Microsoft Excel
Using Microsoft Excel 2010
Advanced Skills
Using Microsoft Excel
Advanced Skills
Excel contains numerous tools that are intended to meet a wide range of requirements. Some of the more specialised tools are useful to only certain types of people while others have value for more general excel users. These exercises will cover some of the advanced features that may be useful for most excel users. These features will include cell naming, cell notes, conditional formatting, data validation and custom number formats. Naming Cells
In a large spreadsheet, cell referencing and selection may be simplified by making use of names. You can assign a unique name to an individual cell or to a range of cells.
This can make it quicker and easier to refer to the cells in charts and functions.
Additionally, functions that make use of names are easier to read. For instance, a formula that says =B4-B5 doesn’t make as much sense as a formula that says
=Sales-Expenses.
Exercise 1. Creating Cell Names
1) Create a new workbook in Excel and create a table like the one below.
© Steve O’Neil 2010
Page 1 of 24
http://www.oneil.com.au/pc/
Using Microsoft Excel 2010
Advanced Skills
2) Save the file as Budget.
3) Click on Cell A3 which will have the current date.
4) Click in the Names box which is to the left of the formula bar. Currently it will display the reference of the currently selected cell.
5) Type Date in the box and press [Enter] to create the name for that cell.
6) Click in another cell anywhere on the worksheet (or even in another worksheet).
7) Click on the dropdown arrow next to the Names box. A list of names for the current workbook will appear.
8) Click on the Date name. Excel will automatically go to, and select that named cell, even if you were on a different sheet.
Note
Whenever you select a cell or range of cells that is named, the name will appear in the names box instead of the cell reference.
9) Select the
Advanced Skills
Using Microsoft Excel
Advanced Skills
Excel contains numerous tools that are intended to meet a wide range of requirements. Some of the more specialised tools are useful to only certain types of people while others have value for more general excel users. These exercises will cover some of the advanced features that may be useful for most excel users. These features will include cell naming, cell notes, conditional formatting, data validation and custom number formats. Naming Cells
In a large spreadsheet, cell referencing and selection may be simplified by making use of names. You can assign a unique name to an individual cell or to a range of cells.
This can make it quicker and easier to refer to the cells in charts and functions.
Additionally, functions that make use of names are easier to read. For instance, a formula that says =B4-B5 doesn’t make as much sense as a formula that says
=Sales-Expenses.
Exercise 1. Creating Cell Names
1) Create a new workbook in Excel and create a table like the one below.
© Steve O’Neil 2010
Page 1 of 24
http://www.oneil.com.au/pc/
Using Microsoft Excel 2010
Advanced Skills
2) Save the file as Budget.
3) Click on Cell A3 which will have the current date.
4) Click in the Names box which is to the left of the formula bar. Currently it will display the reference of the currently selected cell.
5) Type Date in the box and press [Enter] to create the name for that cell.
6) Click in another cell anywhere on the worksheet (or even in another worksheet).
7) Click on the dropdown arrow next to the Names box. A list of names for the current workbook will appear.
8) Click on the Date name. Excel will automatically go to, and select that named cell, even if you were on a different sheet.
Note
Whenever you select a cell or range of cells that is named, the name will appear in the names box instead of the cell reference.
9) Select the