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: