Using Cell and Range Names

It is often convenient to name a range of cells.  When a range is identified, you can jump to a range name and use range names in a formula, making them easier to read. 

A named variable or range, when used in a formula, is an absolute reference, i.e. you don't need to use the awkward $ symbols.

Defining A Range Name  

By defining a range name you assign a name to a specific cell or group of cells. If you have a lot of cells to name, refer to Creating Range Names below.

  1. Select the cell(s) you wish to name.


  2. Click the right mouse button then choose Name a Range or click in the cell address area of the Formula Bar (far left of bar) and enter a name for the selected cells then press Enter. Alternatively:


    1. Display the Formulas Ribbon and click on the Define Name button.


    2. Enter a name for the selected cell(s).


    3. Leave the scope as Workbook then choose OK.

    A name cannot contain spaces.  If you wish to use multiple words, either separate them with an underscore or capitalize the first letter of each word, i.e. Exp_description, or ExpDescription.  It is also good advice to keep descriptions short.

Go To A Named Range 

If you select a name from the list of named ranges, Excel jumps to the range and selects all cells within the range.

To select a named range:

  1. Click on the list button beside the current cell address in the formula bar.

  1. Click on the range name you wish to select.  Excel will place you in the range you selected.

List, Edit or Delete Named Ranges

To see a list of all the named ranges in your workbook follow the instructions indicated in Step 1 under Go To A Named Range or press the F3 key. For more options:

  1. Display the Formulas Ribbon and click on the Name Manger button. The following window is displayed showing details of your named ranges:
  2. To edit a named range, select the name from the list then click on the Edit button.


  3. To delete a named range, select the name from the list then click on the Delete button.

Creating Range Names  

If you create range names Excel will recognize data associated beside, under, or above row and column headings, and create the names automatically. Using the example below, if you wanted to name cell B1 with the text in cell A1 and cell B2 with the text in A2, etc. you could use this trick to quickly create these names.

  1. Select the range of cells you wish to name, including row and column headings. In the above example you would select A1 to B3.


  2. Display the Formulas Ribbon and click on the Create from Selection button. The following window is displayed:

  3. Select the choice(s) that indicate where the headings are located (usually the top row and/or left column of the selected area).


  4. Choose OK

Linking To A Named Range

If a name is defined for a single cell , i.e. JanTotal, you can paste the contents of the cell anywhere in the workbook by typing =Name i.e. =JanTotal

Using Named Ranges In Formulas

If you have named cells and/or ranges in your workbook, you can include their names rather than their cell addresses when creating formulas. When you want to insert the name reference, you can select the name from a list by pressing the F3 key.

An added bonus to using named cells and/or ranges is that their references do not change when copied; hence, an absolute reference is not required if the cell being referenced is referred to by its name.

Examples  

In the following example we will assume that cells B4 to B8 are named January.  Cells C4 to C8 are named February.

When creating a formula, rather than typing a name or cell reference, simply click in the cell, or drag over the range, and Excel will insert the name or cell reference for you.  This trick reduces errors in incorrect cell references.

Additional References