Using Range Names

Objective: To name some cells and use their range names in formulae

Instructions: You will use the Define Name command on the formula menu.

Comment: Using cell references to write formulae with is all very well, but it's a bit like programming in assembly language. If we can give proper names to our numbers then the formulae are instantly more readable.

Activity 8.1 Select the cell G2, and then go to the command Define Name on the Formula menu. The following dialogue box should appear:

Excel immediately takes the nearest piece of likely-looking text and proposes it as the name for the cell. We can change this if we want but this label is fine so click on the button labelled <Add>. The name VAT appears in the Names in Sheet list box. Now click on <OK> to close the box. Cell G2 can now be referred to by the name VAT.

Activity 8.2 Select the cell F5. Press the function key <F2> so that the cell is now in Edit mode. Backspace over $G$2, and replace it with the name VAT. The formula should end up looking like this:

=F5+F5*VAT

Press <Enter>. The result displayed in the cell should remain the same, but here the figure in G2 is referred to by what it means rather than where it is.

Activity 8.3 We will now give names for all the data. Select the range C4:E18, and then select the command Create Names... from the Formula menu. The following dialogue box should appear:

This is a shortcut when naming a lot of ranges if the range names you want already serve as labels for the data on your worksheet. Here the names we want are the column labels in row 4. The box assumes the names we want are stored in the top row, so click <OK>. The names for the ranges should all be set up.

To check the range name, select the range C5:C18. The range name Travel should appear on the left hand box of the formula bar.

OSU | CoF | Community | Search | Forestry Computing Helpdesk