May 292012

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010

This is the first part of a 3 part post

A Defined Name is a way of adding a meaningful descriptor to any of the following:

  1. A Cell
  2. A Range of Cells (including an entire row or entire column)
  3. A Constant (i.e. a number like 8.3)
  4. A Formula
First Name Last Name Email Address
Cell =C1 =TaxRate
Range of Cells =SUM(C20:C30) =SUM(Q1Sales)
Constant =1.1 =GrowthRate
Formula PRODUCT(SUM(C20:C30), C1) =Q1Tax

Once a name has been defined, this name can then be used in place of the cell or range reference, constant or formula making it easier to understand its purpose.

For this article we will focus on the first two items above which are also the most commonly used i.e. using a Defined Name to reference an individual Cell or Range of Cells. We will refer to these from now on as Named Ranges. We will continue to refer to Defined Names when the information is applicable to all types of Defined Names.

So Why Use Named Ranges?

The power of Named Ranges is huge and once mastered, makes it hard ever to consider using normal cell references again. Some of the key benefits are:

  1. A descriptive name can be far more meaningful and easier to remember than a specific cell or range reference thereby simplifying formulas. See example above.
  2. You can quickly move to a particular part of your workbook (or worksheet) by simply selecting the Named Range from the dropdown list of names in the Name box or by using the (Edit > Goto) dialog box.
  3. You can use Excel Find to search through a workbook for all formulas that use that Named Range without having to know what it refers to .
  4. If the Cell or Range reference needs to be changed, you can simply edit it and everything in Excel will be instantly updated as the change is global. This can save hours of time when making changes to Excel workbooks. This is especially useful in Charts, Pivot Tables, Conditional Formatting and Data Validation where it is harder to edit the actual references.
  5. Named Ranges can be used to prevent references from “breaking” when linking to external workbooks.
  6. Named Ranges can be used to prevent references from “breaking” when used within VBA code i.e. the code can be made fully dynamic.
  7. By default, Named Ranges are Absolute, i.e. the cell references will contain the “$” sign (eg “$A$1”) and the reference will not change when formulas are copied down or across the worksheet. This makes it easier to anchor ranges.
  8. One of the most important benefits is this. If you delete a range on a worksheet, any references to that range or any cells within that range are lost and Excel displays a #Ref in place of the reference. If you have many formulas referencing this range, you have to go through each formula and fix the reference which can be very tedious and time consuming. If you have used Named Ranges, you simply have to redefine the Named Range and all errors will be fixed. (Examples of this will follow)
  9. Following on from the above point, if you accidentally delete a Named Range, you can simply recreate it and all references will be fixed.

Generally, Named Ranges improve readability by making formulas easier to read and understand and workbooks easier to maintain, significantly reducing the chance of errors. This is critical to building robust models.

Syntax Rules for Defined Names

Valid characters: The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods and underscore characters. [Personally, I would avoid the backslash (\) as many users get confused with the “slashes” and thus often try to use a forward slash (“/”) which will fail]

NOTE. You cannot use the uppercase and lowercase characters “C”, “c”, “R”, or “r” as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in the Name or Go To text box.

Cell references are disallowed: Names cannot be the same as a cell reference, such as Z$100 or R1C1.

Spaces are not valid: Spaces are not allowed as part of a name. Use the underscore character (_) or period (.)  or capitlise the first letter of each word as word separators, such as, First_Quarter, First.Quarter or FirstQuarter.

Name length: A name can contain up to 255 characters.

Case: Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then try to create another name called SALES in the same workbook, Excel prompts you to choose a unique name as both of these two names are the same.

My advice here is to follow a simple naming convention that will make it easy to adhere to all the above rules, let you manage large numbers of names and also make it really easy to use Excel’s Find and Replace to make global changes to your formulas.

Scope of Names (from MS Online Help)

All Defined Names have a scope, either to a specific worksheet (worksheet scope) or to the entire workbook (workbook scope). The scope of a Defined Name is the location within which the name is recognized without qualification. For example:

A Defined Name such as Sales_FY scoped to Sheet1, if not qualified, is recognised only in Sheet1. To use this local worksheet name in another worksheet requires you to qualify it by preceding it with the worksheet name, e.g. Sheet1!Budget_FY08. Note the use of the “!”.

A Defined Name such, such as Sales_Summary, scoped to the workbook is recognized for all worksheets in that workbook, but not for any other workbook.

A name must always be unique within its scope. Excel prevents you from defining a name that is not unique within its scope. However you can use the same name in different scopes. For example, you can define a name, such as GrossProfit that is scoped to Sheet1, Sheet2 and Sheet3 in the same workbook. Although each name is the same, each name is unique within its scope.

You can even define the same name, GrossProfit, scoped to the workbook, but again the scope is unique. In this case, however, there can be a name conflict. To resolve this conflict, by default Excel uses the name that is defined for the worksheet because the local worksheet level takes precedence over the global workbook level.

Note: You can override this precedence by prefixing the workbook name, e.e. WorkbookFile!GrossProfit. Note the use of the “!”. You can override the local worksheet level for all worksheets in the workbook, with the exception of the first worksheet, which always uses the local name if there is a name conflict and cannot be overridden.

You choose the scope of the Defined Name when you create it and the scope cannot be changed without recreating the name. (See creating Defined Names below)

Click here to skip to recommendations for choosing of scope of a Named Range.

This is part 1 of a 3 part post. Continued in Defined Names 101: Creating and Managing

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>