DataWright Information Services

Consulting and Resources for Excel and Access




Introduction to range names

What are range names?

Range names are markers

A range name is a reference to a cell or group of cells in a
workbook. By default range names are absolute references. You can
use range names in a whole lot of ways. Here are just a few:

  • Identify a value that gets used in formulas throughout a
    workbook. Examples could be an inflation rate or the starting
    date of a forecast period
  • Define a table that is used with VLOOKUP or HLOOKUP
    functions
  • Define a dataset for a pivot table or chart (graph). This is
    particularly useful if the data set will grow, and you want to
    dynamically expand the pivot table’s range to match. See links
    at the bottom of the page for examples of these techniques.
  • Get a cleaner import into Access. Often, if you import a
    worksheet into Access, you end up creating a number of dummy
    fields to the right of the real data. If you define a range, and
    import that range, you won’t have that problem.

Range names can simplify your formulas

Let’s say that you are forecasting revenue, expenses and net
profit for the next year. If your estimated CPI Increase is in cell
C2, which is easier to understand at a glance?

  • =A3*(1+$C$2)
  • =A3*(1+CPI_Rate)

Similarly, naming a lookup table makes it easy to refer to in
lookup formulas.

  • =VLOOKUP(A3,$G$2:$J$56,3,FALSE)
  • =VLOOKUP(A3,Products,3,FALSE)

Creating range names

What’s in a valid range name?

A range name needs to obey certain conditions. Range names
cannot:

  • Contain entirely numbers, or start with a number.
  • Be a valid cell, sheet or column reference, e.g.: B5.
    Note: VIP is a valid range name in all versions of Excel up to
    2003. It is a problem if you switch to Office 2007, because the
    columns go out to XFD. So, watch out for three-letter acronyms
    in range names. Also, Sheet1 would be inappropriate for a range
    name.
  • Contain spaces. Total 2007 is invalid, but you
    could use Total_2007 or Total2007 instead. The reason for this
    is that the formula =Total 2007 will be
    interpreted as “find the intersection between the Total
    and 2007 ranges”. Rather than allow this potential confusion,
    Excel won’t create range names with spaces.
  • Contain most kinds of punctuation. The underscore
    or hyphen are OK; avoid other punctuation marks.

Typing the name

To create a range name, the simplest way is to do the following:

  • Select the cell(s) that make up the range
  • Click in the Name Box to the left of the Formula Bar, type
    the name, and press Enter

Creating names automatically

If you have a table, and want to make range names from all of the
headings, Excel can save you some time. Do this:

  • Select the whole table, including the headings
  • Insert > Name > Create will bring up the following dialog:
  • If you have text in the top row and left column of the table
    you will be asked whether you want to use row and column
    headings as names. Select the options that you want and click
    OK. Excel will create the range names for you; check in the Name
    box and you will see them all listed.

Note: If the headings have spaces in them, Excel will replace
spaces with underscores for the range names. The worksheet values
will not be affected, only the range names.

Using range names

Using range names in new formulas

To use an existing name in your formula, do this:

  • Start typing the formula. At the point where you want to
    insert the range name, press F3
  • Select the name from the list and press OK

Particularly if you have long range names, picking them from a
list prevents typographical errors.

Applying range name references to existing formulas

If you already have created your worksheet and you want to
retrospectively add range names, do this:

  • Select the cells where you want to apply the names. If you
    want to apply them across the whole sheet, just select a single
    cell
  • Insert > Name > Apply, and Ctrl- or Shift-click to select
    all of the names that you want to apply to the sheet. Press OK,
    and all cell references that match those named ranges will now
    use the range names.

Listing range names

If you use a lot of names, it can be useful to list them so that
you can check the references and / of identify names with broken
references (this will happen if you delete rows, columns or
worksheets, or use Cut and Paste to transfer data on top of an
existing named range). In this case you will see #REF! errors in the
range references. To remove range names using code, see the link
below on managing names with VBA.

To list the range names in a workbook, do this:

  • Go to a blank sheet, or a blank section of an existing
    worksheet
  • Press the F3 key, then click the
    Paste List
    button
  • You will now have an alphabetical listing of all range names
    and their range references

Some more uses for range names

Static range names are very useful. Sometimes, however, you need
more flexibility. Here are a few examples: