Excel Developer Tip

Return to The Spreadsheet Page

Excel page

Tip archives

Naming Techniques

Most Excel users know how to name cells and ranges. Using named cells and ranges can make your formulas more readable, and less prone to errors. Most users, however, don't realize that Excel lets you provide names for other types of items. This document describes some useful naming techniques that you may not be aware of.

Naming a constant

If formulas in your worksheet use a constant value (such as an interest rate), the common procedure is to insert the value for the constant into a cell. Then, if you give a name to the cell (such as InterestRate), you can use the name in your formulas. Here's how create a named constant that doesn't appear in a cell:

  1. Select the Insert Name Define command to display the Define Name dialog box.
  2. Enter the name (such as InterestRate) in the field labeled Names in workbook.
  3. Enter the value for the name in the Refers to field (this field normally holds a formula). For example, you can enter =.075.
  4. Click OK

Try it out by entering the name into a cell (preceded by an equal sign). For example, if you defined a name called InterestRate, enter the following into a cell: 

  =InterestRate

This formula will return the constant value that you defined for the InterestRate name. And this value does not appear in any cell.

Names are actually named formulas

Here's another way of looking at names. Whenever you create a name, Excel actually creates a name for a formula. For example, if you give a name (such as Amount) to cell D4, Excel creates a name for this formula: 

  =$D$4

You can use the Define Name dialog box and edit the formula for a name. And you can use all of the standard operators and worksheet functions. Try this:

  1. Create a name for cell D4. Call it Amount.
  2. Enter =Amount into any cell. The cell will display the value in cell D4.
  3. Use the Insert Name Define command and edit the refers to field so it appears as =$D$4*2

You'll find that entering =Amount now displays the value in cell D4 multiplied by 2.

Using relative references

When you create a name for a cell or range, Excel always uses absolute cell references for the range. For example, if you give the name Months to range A1:A12, Excel associates $A$1:$A$12 (an absolute reference) with the name Months. You can override the absolute references for a name and enter relative references. To see how this works, follow the steps below to create a relative name called CellBelow.

  1. Select cell A1.
  2. Select the Insert Name Define command to display the Define Name dialog box.
  3. Enter the name CellBelow in the field labeled Names in workbook.
  4. Replace the value in the Refers to field with =A2 (this is a relative reference)
  5. Click OK

Try it out by entering the following formula into any cell: 

  =CellBelow

You'll find that this formula always returns the contents of the cell directly below.

NOTE: It's important to understand that the formula you enter in Step 4 above depends on the active cell. Since cell A1 was the active cell, =A2 is the formula that returns the cell below. If, for example, cell C6 was the active cell when you created the name, you would enter =C7 in step 4.

Using mixed references

You can also used "mixed" references for you names. Here's a practical example of how to create a name that uses mixed references. This name, SumAbove, is a formula that returns the sum of all values above the cell.

  1. Activate cell A3.
  2. Select the Insert Name Define command to display the Define Name dialog box.
  3. In the Names in workbook field, enter SumAbove.
  4. In the Refers to field, enter =SUM(A$1:A2)

Notice that the formula in Step 3 is a mixed reference (the row part is absolute, but the column part is relative). Try it out by entering =SumAbove into any cell. You'll find that this formula returns the sum of all cells in the column from Row 1 to the row directly above the cell.