Using Microsoft Excel

Topics: Spreadsheet, Microsoft, Microsoft Office Pages: 23 (4305 words) Published: November 13, 2013
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 cell range B6:B7 which should contain the cells with the income amounts. 10) Click in the Names box, type Income and press [Enter].
Note

If you type a name in the names box without pressing [Enter] afterwards, the name might not be created.

11) Select the cell range B11:B25 which should contain the expense figures. 12) Click in the Names box, type Expenses and press [Enter]. 13) Test the new named ranges by selecting them from
the names dropdown list. Each range should become
selected when you select its range.
14) Click in cell D8. This cell will contain the formula to
calculate total income.
15) Enter the following formula.
=Sum(Income)
Excel will make use of the range name to add up all of the cells in that range. 16) Click in cell B26 which will contain the total expenses. 17) Click on the Autosum icon.
When the Autosum tool completes the function, it will use the range name you have created instead of the less meaningful
cell references.
18) Press [Enter] to complete the function.
19) Save the changes to the workbook.

© Steve O’Neil 2010

Page 2 of 24

http://www.oneil.com.au/pc/

Using Microsoft Excel 2010

Advanced Skills

Exercise 2. Creating Names Automatically
If you have a lot of cells you want to name, it is possible to have the names automatically created for you from table headings/labels.
1) Select A6:B8. These cells contain the income labels and amounts. 2) From the Formulas tab on the Ribbon click the Create from Selection icon.

3) We want the data cells to be named based on the cells in the left column so make sure the Left column option is selected and then click OK.
4) Click in cell B6, B7 or B8. Look in the Names box to see the names that have been created. Notice that names with more than one word have been created using an underscore. E.g. cell B8 will now have the name Total_Income. This is

because names cannot contain spaces. Names must
also begin with a letter.
5) Select the cell range B11:B26. This should contain...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Excel Evaluation Essay
  • Excel Essay
  • Week 6 Excel Essay
  • Essay about Excel Functions
  • Dbms – Access Versus Excel Essay
  • Essay about Excel Scenario Week 3 CIS 211
  • Essay about Matlab vs Excel
  • Week 4 Ms Excel Exercise Essay

Become a StudyMode Member

Sign Up - It's Free