Named Ranges in Microsoft Excel

Names ranges can be very useful. Some people argue that point, whether using named ranges in VBA or formulas. The most common problem regarding names ranges, everyone agrees, is user ignorance of their existence in the application.

Create a Named Range

Select a range of cells using the Shift key, Shift+Ctrl, or Ctrl keys (yes, you can select non-contiguous ranges, like A1, A3, and A5). Then hit Insert Name Define and type a name.

The name you choose cannot have spaces and cannot begin with a number. Certain other symbols are also not allowed.

Alternatively, you can type or change the name right into the Name box (indicated by the red arrow), as shown below. You can name cells, graphics, textboxes...just about anything.

Tip: You are not warned when you overwrite a named range.

Hyperlink to a Named Range

You can insert a hyperlink to a named range by inserting a hyperlink to a workbook, and simply adding #NamedRange to the end of the hyperlink. For instance, C:\Documents and Settings\YourName\Desktop\data.xls#Master, where Master is the named range you created.

Find the Range to Which a Name Refers

Use the Name box. It's just above cell A1. Hit the dropdown, and choose a name. You'll be taken to that range.

Another method: Hit Insert Name Define. Select the name, and look at the bottom of the dialog to see the range to which it's been assigned.

Common Problems

John creates a workbook that uses named ranges to create dropdowns. John is out sick. You decide you're going to figure out how to add a value to the dropdown list. You hit Data Validation, and find that it's using a list called "fruits".

You find the list of fruits on Sheet2, and add bananas to the bottom of the list. It doesn't appear in the dropdown. Why?

When you're looking for a named range, hit the Name drop-down box, to find the named range, and it becomes highlighted. The Name drop-down box appears just above column A, which is shown in the graphic above. Also shown is the selected named range, cells A2 through A6. To rectify the situation, simply select cells A2 through A7, and hit Insert Name Define and type "fruits" again.

A very cool use for a named range is that you can use one cell for your formula values. Suppose you want to mark up a cost to determine your price. Your costs are all in column B. If you copy the formula shown below, each row's formula will change. The reference to F1 will change to F2, then F3 as you copy it down each row.

Instead, your could use absolute references, and type the formula in C2 as:

=B2*$F$1

Or, you could choose cell F1 and Insert Name Define and type "markup". Then, you can write the formula in C2 as =B2*Markup. When you copy it down, only the B2 reference will change for each row, and your markup will always point to the appropriate cell. This is particularly nice when you're jumping around to different spreadsheets, and also keeps you from having to remember what cell the markup value was in.