Working With Named Ranges In Excel
This page describes the use of Named Ranges in Excel.
This page has been replaced by Defined Names In Excel.
What Are Named Ranges?
Named Ranges are a powerful tool in Excel that allows you to assign a meaningful name to a single cell or a range of cells. For example, you can assign the name "TaxRate" to cell C1 and then use the name "TaxRate" anytime you would normally use the cell C1, such as =A5*TaxRate.
There are 3 advantages to using Named Ranges:
A range name can contain letters, numbers, and underscores, but not spaces or special
punctuation characters. Moreover, it cannot be the same as a normal cell
reference. For example, "AA10" is not a valid range name because
"AA10" is the name of a normal cell reference (row 10, column "AA").
To create a new Named Range from Excel, use the following procedure:
See below for VBA procedures for adding and deleting names.
A Named Range does not have to refer to a cell or a range of cells. It can refer to a formula. However, all of the arguments to the formula are "static". They do not depend on the cell from which the name is invoked. You should always use absolute cell references in a named formula; otherwise the result can be very confusing.
For example, if you frequently use the formula =SUM($A$1:$A$10), you can create a named formula called MySum, referring to the formula =SUM($A$1:$A$10). Then, you can use the name anywhere you'd normally use the formula: =MySum/2
Another advantage is that you can overcome Excel's limitation on eight nested functions. See the Nested IFs page for more details.
It is often useful to create a name that refers to a range of cells, where the range
depends on the content of the cells. For example, you may want a name that refers to
the first N non-blank entries in column A. Excel's Name tool allows you to do
this. For example, creating a name called DynaRange,
If the first 20 rows of column A contain data (and the rest are blank), DynaRange will refer to the range A1:A20
See the on-line help for the =OFFSET function for a
description of the arguments. Setting the Width argument to 2 will allow us to use
this name in a =VLOOKUP function
Download a workbook illustrating dynamic ranges.
When you use the Data Validation tool, you can restrict valid inputs to the values in another range. However, the Data Validation dialog won't allow you to specify cells on another worksheet. But using a defined name, you can get around this limitation. Simply give the range containing the valid values a name, such as ValidRange, and then in the Data Validation dialog, enter =ValidRange as the Source for the validation list. Data Validation won't object, and everything will work as you expect.
You can use the same technique with Conditional Formatting. The
Conditional Formatting objects when you try to use a reference to a cell or
range on another worksheet. Just name the source cell or range, and use
the defined name in the Conditional Formatting formulas. CF won't
object, and everything will work as you expect. See the Conditional
Formatting page for more details.
Adding A Range Name
You can use the Add method of the Names collection object to add a new defined name.
If you set the Visible property of the name to False, the name will not be displayed in the Names dialog box, although the name can still be used normally in VBA procedures and worksheet formulas.
If you add a name that already exists, Excel will replace the old name with the new name. An error will not be generated.
Determining Whether A Name Exists
The following VBA procedure will return TRUE if the name
specified in TheName exists, or will return FALSE otherwise.
The Delete method of the Names collection can be used to delete a name.
If the name does not exist, a run time error will occur.
Determining The Name Of A Cell Or Range
You can use VBA procedures to determine the define named that contains a given cell or range. You must first decide whether you want to return a name that refers to some range that intersects with your range, or whether you want to return the name of the range that refers to exactly the same range or cell as your range. Below are two VBA procedures to do this.
Function NameOfParentRange(Rng As Range) As String
If you need an exact match, use the function below.
Function ExactRangeName(Rng As Range) As String
With this function, if the name Range1 refers to A1:A10, the function will return an empty string unless Rng refers to exactly A1:A10.
There is another class of Name in Excel, whose values are available to all open workbooks, and whose values are retained even after the workbook which created the name has been closed. See Hidden Name Space for more details.