Click here for Introduction Click here for Table of Contents Click here for Examples and Special Topics
BarEnd.jpg (765 bytes)

  About naming ranges
When you are working in a group, and multiple people must use the same worksheet, it can be helpful to use range names when creating formulas. This makes it easier to figure out what your formulas mean, both for you and for your colleagues.

Why range names?

B2:D2 may make perfect sense to Excel, but if that row on your worksheet has revenue numbers in it, "Revenues" probably makes much more sense to a human.

Excel provides a way to assign names to worksheet ranges so that you can clear up this confusion.

Naming ranges:

There are two ways to name a worksheet range:

  • Use the Name Box. After selecting the cell or cells you wish to name, type a name into the Name Box. The Name Box is in the upper left-hand corner of your screen (it does double duty by showing the coordinates of the cell that you have selected).
  • Use the Insert Name Create command. This makes it possible to use existing captions on your worksheet to name several ranges in one step.

Using range names in formulas (F5):

To use a range name when building a formula, press the F5 key whenever you would normally point at a worksheet cell.  In these two examples:

  • "growth" provides an absolute reference for revenue growth. Build the formula once, then extend it using the drag handle.
  • "Revenues" and "Expenses" provide relative references for calculating profit in each the year. Build the formula once, then extend it using the drag handle.
Learn more
avi_icon.gif (229 bytes) The first example uses the Name Box to type a range name, then uses that name as an absolute reference in a formula.
  • It names cell B2 "growth", using the Name Box.
  • It then uses the cell named "growth" to calculate revenue growth for a series of years.
avi_icon.gif (229 bytes) The second example uses the Insert Name Create command to name two ranges, one for revenues and one for expenses. It then uses those named ranges as relative references in a formula that calculates profits.
  • It names cells B4..F4 "Revenues" and cells B5..F5 "Expenses", using the Insert Name Create command.
  • It uses the F5 key to call up a list of range names as a formula is built. By using this list, you should never need to retype a range name once you have entered it.