Named Ranges

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.
0) They improve readability and make your formulas much easier to understand meaning there is less chance of errors.
1) 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.
2) Inserting and deleting cells, rows or columns will not change the location of your named ranges. Moving cells, rows or columns will though.
3) Typing a descriptive name is much easier than remembering a specific cell address, therefore simplifying your formulas.
4) 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.
5) You can also create 3-D named ranges that represent the same cell or range of cells across multiple worksheets.
6) Allows you to define Named Constants which are single, frequently used values.
7) 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.
They are case insensitive. You cannot have another named range with the same letters but in a different case.
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.

Options

(Calculation tab, Accept labels in formulas) - Lets you use the Natural Language Formulas.

Important

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 non-contiguous 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.
If a formula uses a named range that is then deleted, the formula will return the #NAME? error.
If you create a named range with the same name as an existing named range, it is simply overwritten with no prompt.
If you want to insert several named ranges at once then you should use the Add button. If you only want to add one named range then or have no more named ranges to add you should press OK.