About

When to Apply Tag:

Questions that specifically concern the creation, definition, or application of named ranges in a spreadsheet application should be applied . This tag should be applied in addition to the tag for the specific spreadsheet application being used, such as .

About Named Ranges:

In Microsoft Excel, named ranges may be defined in several ways. The Name Box, located to the left of the formula bar, may be used to apply a name to the selected range. The Name Manager (accessible on the Formulas ribbon or by pressing Ctrl+F3) may be used to define a named range as well.

Names defined in the Name Manager may be defined statically, i.e., as an address, such as

=Sheet1!A1:C3

Or dynamically, i.e., as a formula, such as

=INDIRECT("Sheet1!A1:A"&COUNTA(Sheet1!A:A))

Dynamically defined named ranges are useful for referring to ranges that may have changing dimensions or location, such as a column in a table where rows and columns are sometimes added or removed.

Named ranges may be used in formulas. For example, where the range A1:A5 is named MyNamedRange, the following formulas are the same:

=SUM(A1:A5)

and

=SUM(MyNamedRange)

Name ranges may also be used in VBA application.

Dim rng as Range
'MyNamedRange is previously defined named range.
Set rng = Range("MyNamedRange")
'Alternative way of defining range object using named range
Set rng = [MyNamedRange]

For more information on named ranges:

history | show excerpt | excerpt history