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:
- A range that expands to
accommodate extra data. This can be used for charts and for
pivot tables - A range that contains only the last n rows of a
table; for example, plotting only the last 12 months of climate
data - A range that always
refers to the cell above the current cell. This can be
useful for sub-totalling a worksheet where you regularly insert
rows - Worksheet-level names
let you consolidate complex workbooks easily. - Managing range names
with VBA.
