Naming Cells and Ranges
A very useful technique for making it easier to refer to cell ranges in formulas (and functions) is to name the cells. The Cell Name feature allows you to assign a name to a cell or cell range. The name then rather than the cell or range address can be used to refer to the cells in a formula.
To Name a Cell Range
Naming a cell or cell range is accomplished via the Name command on the Insert menu.
1. |
Select the cell or cell range that you will name. |
2. |
Select Insert, point at Names, and select Define. The Define Names dialog box will open (see Figure 5.7). Figure 5.7. Enter the name for the cell or cell range in the Define Names dialog box.
|
3. |
Type the name for the cell or cell range in the Name box. |
4. |
Click Add to add the name to the Cell Name list. |
5. |
Click OK to close the Define Names dialog box and return to the Calc sheet. |
Note
Cell names cannot contain spaces. Use the underscore to divide words in a multiword cell name.
To Create Cell Names from Existing Labels
Another option for creating cell names quickly is to create a cell range name from the header or footer of the range (the first or last entry in the range such as the column heading), or to create the names for several cells (typically cells holding values) based on information in the column at the left or right of the values.
For example, Figure 5.8 shows a sheet that lists products along with their current pricing. Since the actual price of the items will typically be used in formulas related to the sales of the items, you may wish to create labels for those values that can then be used in the various formulas that you create on the sheet.
Figure 5.8. Cell names can be created for several cells in a range based on the labels that exist in columns to the left or right of the values.
1. |
Select both the cell range that contains the cells to be named and the cell range that will supply the names for the cells. |
2. |
Select Insert, point at Names, and then select Create. The Create Names dialog box will appear (see Figure 5.9). Figure 5.9. Select the location of the labels that you will use to assign names to the selected cells.
|
3. |
Select the appropriate checkbox to determine where the cell names will be pulled from (Header, Left column, Footer, or Right column). |
4. |
Click OK to create the names. |
The new cell names will now be available on the Insert list that is used to insert the cell names into formulas (or functions).
To Insert Cell Names in a Formula
Once you've created cell names, you can use them to reference the cells in formulas (and in functions, which are discussed in the next chapter). The great thing about using cell names in formulas rather than actual cell addresses is that you have a descriptive label representing the values included in the formula.
1. |
Click on the cell that will hold the formula. |
2. |
Enter = to begin the formula. |
3. |
To enter the first cell name in the formula, select the Insert menu, point at Names, and then select Insert. The Paste Name dialog box will appear (see Figure 5.10). Figure 5.10. Cell names are inserted using the Paste Name dialog box.
|
4. |
Select the name in the Paste Name dialog box, and then click OK. The cell name will be entered in the formula. |
5. |
Insert operators and other cell references as needed into the formula. To insert additional cell names, use the Paste Name dialog box as detailed in Steps 3 and 4. |
6. |
When you have completed the formula, click the Accept button on the Formula toolbar. |
Figure 5.11 shows a simple multiplication formula that uses a cell name (referencing cell C6).
Figure 5.11. Cell names provide descriptive references in your formulas.
Note
Don't use cell names in formulas or functions that you wish to copy to additional cells. Using a cell name places an absolute reference in the formula, so the formula will not adjust to a new location and use relative referencing to supply the correct answer.
To Modify Cell Names
The cell names that you create can also be easily modified or deleted. You can change the cell or cells that the name refers to or edit the name itself.
1. |
Select Insert, point at Names, then select Define. The Define Names dialog box will appear. |
2. |
Click on an existing name in the Name list. The Modify and Delete buttons become active. |
3. |
To delete the cell name, click the Delete button. You will be asked if you really wish to delete the cell name. Click Yes to delete the name. |
4. |
To change the range that the name refers to, click the Shrink button at the right of the Refers to box (near the bottom of the dialog box). |
5. |
You will be returned to the sheet and the dialog box will roll up. Select the new cell or cell range on the sheet, and then select the Shrink button on the dialog rollup. You will be returned to the dialog box and the new cell or range address will appear in the Refers to box. |
6. |
To change the name for the cell address or range, click the Modify button. This clears the Name box. Enter a new name. The cell or range will now have a new name. To avoid a naming conflict, delete the old range name from the list that also describes the cell address or range address. |
7. |
When you have completed deleting or modifying names in the list, click OK to return to the sheet. |
Note
You can click the More button on the Define Names dialog box to add more parameters to a cell or cell range that has been named. Checkboxes are available that allow you to set the cell address area as a print area or to specify that a named row or column should repeat when the spreadsheet is printed. This is useful when printing a large sheet that will require multiple pages.