Leading the way in Microsoft Office Development

What is a Named Range ?

A named range is a short text description that can be used instead of the cell address to refer to individual cells or ranges of cells.

Providing cells with actual descriptive names can be used to simplify formulas and make them much more user friendly.

Descriptive names are also a lot easier to remember that actual cell addresses.

You can also provide descriptive names for your charts, and shapes although this is not discussed in this section.

You can also give descriptive names to constant values as well as formulas.

Any named ranges that are created are workbook specific and can only be used in the workbook they are created in.

There are two types of named ranges and it is possible to use both types in the same workbook.

Workbook - These can be referenced from any worksheet in the workbook.

Worksheet - These are worksheet specific and can only be referenced on that particular worksheet.

Using Named Ranges

If you define a name for a range of cells then you can use that name instead of the cell address.

Named Ranges can be used instead of the cell address references in calculations and formulas.

You can define more than one name to the same cell or range of cells, although if you use the same name to define two different ranges then the previous name is overwritten.

It is possible to paste your named ranges directly into the Formula Bar while you are in the middle of completing a formula.

Named ranges can be given to cells containing both numerical and text values.

Lets suppose you are using a worksheet to calculate a percentage from a particular number.

Cell "C5" contains the formula =C2*C3.

This method uses cell references in the formula giving the correct result.

Named ranges can be used to help clarify this formula.

You can insert a named range into a cell or formula using the (Insert > Name > Paste) dialog box and pressing OK.

You can also use the shortcut key (F3) to display the (Insert > Name > Paste) dialog box when editing a cell.

You can define a descriptive name for the cell containing the amount and another one for the cell containing the percentage discount.

Assume cell "C2" has been given the descriptive name "Total" and that the cell "C3" has been given the name "Percentage".

The formula in cell "C5" could now be written as "=Total*Percentage" which makes the formula a lot easier to understand.

Advantages of using Named Ranges

In addition to providing an alternative to repeatedly typing in cell addresses and cell ranges, using named ranges have several other advantages.

1) They improve readability and make your formulas much easier to understand meaning there is less chance of errors.

2) Moving or shifting cells that have a named range means that the formulas are adjusted automatically. There is no need to worry about whether the formulas use absolute or relative references.

3) Inserting and deleting cells, rows or columns will not change the location of your named ranges. Moving cells, rows or columns will though.

4) Typing a descriptive name is much easier than remembering a specific cell address, therefore simplifying your formulas.

5) You can quickly move to particular areas of your workbook (or worksheet) very quickly by either using the Name Box or the (Edit > Goto) dialog box.

6) You can also create 3-D named ranges that represent the same cell or range of cells across multiple worksheets.

7) Allows you to define Named Constants which are single, frequently used values.

8) Allows you to define Named Formulas which are common frequently used formulas (save re-typing them).

Rules for Named Ranges

A named range can be up to 255 characters long and can contain letters, numbers, periods and underscores (no spaces or special punctuation characters).

Named ranges are not case sensitive and they can contain both upper and lower case letters. They cannot resemble any actual cell addresses such as "B3" or "AA12".

All named ranges must begin with a letter, an underscore "_" or a backslash "\".

Named ranges can include numbers but cannot include any spaces.

You cannot use any named ranges that resemble actual cell addresses (e.g. A$5 or R3C8).

You cannot use any symbols except for an underscore and a full stop. It is possible to include a backslash and a question mark as long as they are not the first characters.

Named ranges can be just single letters with the exception of the letters R and C.

When you add a named range it is the cell that is named and not the cell contents.

By default named ranges are created as absolute references.

It is possible for a cell (or range) to have more than one named range so typing a new name using the Name Box will not change the named range but will create a new one.

Natural Language Formulas

This feature is an alternative to using named ranges.

You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows.

Labels can only be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet then you must use named range.

Using the (Insert > Name) submenu

Define - Allows you to define a named range. This can alternatively be done by typing directly into the Name box.

Paste - Allows you to enter names directly into the formula bar while you are in the process of entering a formula.

Create - Allows you to create name ranges for individual cells or ranges of cells.

Apply - Allows you to replace existing cell references with their corresponding named ranges.

Label - Allows you to automatically substitute any new cell references with natural language formulas.

Using the Name Box

The Name Box is basically a shortcut for creating and inserting named ranges.

A more comprehensive method is to use the (Insert > Name) submenu it is very useful for moving to different parts of a worksheet.

The drop-down list to the right of the Name Box allows you to quickly find and select the named ranges.

If you highlight a range of cells and this corresponds exactly to a named range then the name is displayed in the Name Box.

Using the (Edit > GoTo) dialog box

When you select (Edit > GoTo) all the named ranges are displayed in alphabetical order.

This dialog box can provide a very quick way to move around a workbook as selecting named range will take you directly to that cell or range of cells.

The GoTo dialog box remembers the last four cells or ranges of cells you visited. Your previous cell range will automatically appear in the Reference box when the dialog box is displayed.

The F5 key is a shortcut key for displaying the (Edit > GoTo) dialog box.

You can very quickly jump between two parts of a worksheet by pressing F5 and Enter.

Things to Remember

All named ranges must begin with a letter, a backslash ("\") or an underscore ("_").

Named ranges can contain numbers but spaces are not allowed (use the underscore instead).

Named ranges cannot resemble any actual cell references (e.g. A4 or H7 etc).

The named ranges "R" and "C" cannot be used. They are reserved to provide shortcuts for highlighting the row and column of the active cell.

You can define names that refer to noncontiguous cells. Hold down the Ctrl key to select these cells before displaying the (Insert > Name > Define) dialog box.

If you enter an invalid named range into a formula, the #NAME? error will be returned.

Excel actually defines a few named ranges automatically. These will not appear in your list and can be overwritten.

If a named range contains more than 253 characters, you cannot select it from the Name box.

Named ranges are not case sensitive so all named ranges (in a single workbook) must be different.

You can use the same name for both workbook and worksheet level named ranges although this will be very confusing and should be avoided.

Pressing (Ctrl + F3) displays the (Insert > Name > Define) dialog box.

If a formula uses a named range that has been deleted the formula will return the #NAME? error.