Naming Ranges

The Range Name command allows us to name cells, ranges, values or formulas

These names are available in the name box on the Formula Bar.

You can automatically create names for cells based on existing row or column titles on your worksheet.

Advantages:

• Formulas that use names are easier to read and remember than formulas using cell references.
  

  Example, the formula =Assets-Liabilities is easier to read than the formula =F6-D6

  As a result Your worksheet then becomes self documenting.

• If you change your worksheet, you can update the reference in one place, and all formulas using the name are automatically updated.
  Replace references with that name in all or part of your worksheet by Applying the name.
  

  Example, by defining the name Margin as =$F$12, you can replace all references to cell $F$12 with the name Margin.

• Jump quickly to a named reference, replace references, or paste a reference in a formula using the Name box on the formula bar.

Excel Range Name Exercises

1. Enter the data shown and including the formula shown in cell B4

2. Highlight the cells A2:B3

3. Now use the menu option Insert Name Create to create suitable range names

The dialog box will allow you to chooses the labels you wish to use.

The default is fine here so click the OK button.

That's it you have created Range Names for Sales and Costs for cells B2 and B3.

However if you highlight the cell with the formula, B4. you will see the formula is unchanged.
To have the cell references in formulas replaced with their new names requires us to Apply the names.

4. Click on cell B4 and select the Insert Name Apply command.
Now if you check your formula bar it should read = Sales - Costs.

---

Practice Exercise

You should attempt the Range Name exercise from the Spreadsheet Exercises listed in the Topic Menu below.

---