Skip to content
Dynamic Arrays

DEFINED NAMES

Introduction

Defined Names is an Excel feature where a name is assigned to a cell or range cells.  Think of it this way; you assign the name Sales_Report to the range B5:F42. So rather than thinking B5:F42 you simply think about the Sales_Report. The power of defined names is quite substantial as you will see in these pages.

Defined Names is the Excel official name of this feature. Named Ranges and Range Names are also sometimes commonly used. We will use Defined Names throughout this website.

Naming the Range

There are three basic ways to name a range. They follow with commentary on each one. More advanced ways to name a range are beyond the scope of this page.

See the Defined Names Rules page for naming rules, allowable names, etc.

See the Using Defined Name page for common uses of defined names and how they save significant amounts of time and help with reducing spreadsheet errors.

The Ribbon Menu

  • Select the range to be named
  • On the Formulas tab, in the Defined Names group, click Define Name.  (Figure 1)
  • In the New Name dialog box, in the Name box, type the name you want to use. Depending on the text in the selection, Excel may try to guess at the name and will per-populate the name. (Figure 2)
  • Accept the proposed name or type in the desired name
  • Press Enter or click OK
  • Alternatively jump straight into the menu (without selecting a range) and select range in the Refers to: box.
  • Defined Names Rules
 

Figure 1

Figure 2

Alternatively, use the Name Manager. See Figure 1.

Click New which takes you to the New Name dialog box.

The Name Manager is typically used for edit an existing name or the underlying range or other unique circumstances.

The Name Box

  • Select the range to be named
  • Click in the Name Box (Figure 3)
  • Type the new defined name and press Enter
  • Defined Names Rules

This method is may be considered the easiest, but has its limitations. Once you get familiar with naming ranges you can decide which method works best for your specific circumstances.

This only works with defining new names. Editing a name or the range for the name, is done using the Name Manager.

Figure 3

Using Existing Text in the Range

Let’s say you have a column data with the heading Sales with the data below the heading.

  • Select the entire range, from the heading to the last cell with data.
  • Press Ctrl+Shift+F3
  • Ensure Top rOW is selected, correct if necessary
  • Press Enter
  • The data (without the heading) is now named Sales
  • To verify the newly define range, click the drop down arrow in the name box
  • Click the desired name
  • The range will be selected upon click the name
  • Defined Names Rules
 
 

Naming Rules

The naming rules are quite simple and intuitive, but there are some traps. Many times Excel will prompt you when using illegal name.

  • Names can refer to single cell or range of cells.
  • Names must begin with a non-number; a letter of the alphabet, the underscore ( _ ) character or the backslash ( \ ) character
  • The names may contain numbers, but must begin with text
  • Periods ( . ) are allowed
  • Upper and lower case letters are interpreted the same in Excel.
    SalesReport, salesreport, and SALESREPORT are all the same name. By creating a name using caps, such as SaleReport, you may find it a bit easier to work with.
  • Spaces and most punctuation character are not allowed.
  • The underscore character helps to display a name with the visual spacing provided by the underscore character.
    Examples: Sales_Report, UnitsSold_Jan, March_Payroll, _Commissions
  • A name can contain up to 255 characters.
  • Many times Excel will prompt you when trying to use a non-allowable name
  • The name cannot look like a cell reference
    For example, you desire to name your payroll summary W2 (IRS form W2).
    Excel will think this is cell W2.
  • C, c, R, r cannot be used as names. Excel reserves for selection shortcuts
  • Names apply to the entire workbook unless sheet specific names are created when assigning the name. The resulting name will then include the sheet name.
 

 

Using Defined Names

Defined names are a great tool for saving time, helping to reduce spreadsheet errors, make it easier to follow and much much more.  Here are the more common uses:

Jumping (GoTo) a range

Let’s say you have five sales divisions in a very large spreadsheet:

  • Simply press GoTo (F5 or Ctrl+G), click the name and click OK, or
  • Click the down arrow on the Name Box and click the name
 

Printing

Excel’s printing features are quite robust when there is good spreadsheet layout and creation of print areas. However, more complex spreadsheets may need additional features.  Simply name the ranges that will be printed as a part of your spreadsheet layout. When printing your steps may be something like:

  • GoTo the print range
  • Press Ctrl+P (File Print in the menu)
  • Click Settings…. Print Selection
  • Print
 

Of course there are many variations of these steps, pick the one that works best with your style.

Wait a minute, you may say. We’re in a paperless world and very seldom print. Here’s three of many scenarios where this still may apply.

  • The project requirements require printed documents, such as court submissions and the several meetings leading up the final stage. Board of Director and similar meeting still tend to use printed reports.
  • While paper may not be the final product, printing to a PDF file many times is.
  • For the larger and complex projects, including in these steps in macro driven custom menus can be a huge time saver.
 

Formulas

The more complex the formulas or the spreadsheet the more value names add.

  • Example: Let’s say you have a loan amortization schedule, referring to the cell named Interest is much easier to interpret that cell C5
  • Referring to Sales_2022 (in the formula) helps to ensure you are not referring to Sales_2021 in error.  This also helps when reviewing or modifying a spreadsheet.
  • =SUM(Sales) is much easier to read than =SUM(C5:C40)
  • Names are Absolute in formulas eliminating the need for entering the $ signs, such as $B$5
  • Instead of typing the name in a formula, use the F3 key. Type =, then press F3 and double click on the desired name and continue with the formula
 
 

War Stories

Financial Modeling

Client developed a very sophisticated financial modeling spreadsheet, over 35 worksheets, each with complex formulas. Defined names were not used. The tool was enhanced and updated continuously over a period of years.

While working with the client I noted how much time he spent navigating the worksheets. This was not just a few seconds here or there, it could have been 10x fold that amount of time.  Why does this matter. He was always working in a frustrated mode.

He was showing me the flow of a NPV page combined with IRR and I happened to see a reference to the wrong worksheet caused by a major worksheet revision. With so much going on, and the expectation of similar results on each page, it was very hard to see the error(s).

Client had been suspicious of some of the results from the calculations but never had the time to dig into it….tomorrows projects.

While the projects all would have been profitable, over $224,000 was left on the table.

 

Loan Payment and Amortization Schedules

The spreadsheet and calculations were very well designed both technically and easy to follow. Reference to the Interest Rate was an absolute cell reference such as $C$5 which is required for this formula. No problem so far.

There were several scenarios with different interest rates, loan period, balloon payments, loan amounts and so on. The entire range of cells was simply copied to a new range where new facts were used. Since the Interest Rate in cell $C$5 was absolute, each new scenario referred back to the original scenario.  Since defined names are also absolute, by themselves, would not have prevented the error. But a well thought through name would have, such as (simplified) Interest_Rate_01, Interest_Rate_02, etc. would have. A simple glance at the formula would jump out rather the $C$5.  There’s more to this example but I tried to keep it simple.

This resulted in some of the scenarios being a no-go as they looked like loosing projects when, if fact, they were profitable.

Commission Tables

Complex VLookup formulas were used in a complex sales and commission structure.  The formulas were copied for new scenarios.  With hindsight defined names would have helped to eliminate the errors. The formulas were riddled with errors, referring to the wrong division, the wrong incentive program, the wrong commission table and so on.

This resulted in lack of trust within the teams and stalled sales programs. I got involve early which brought the program back to being very successful.

More

Spreadsheet errors are very common and it’s many times hard to weed them out. The uses of names noted in this page, such as time savings and ease of creating and reviewing formulas are just two ways two minimize errors. I’m sure you can think of many more.

 

 

 

 

Author: Jim Colville

Date: 9/8/1998 – Updated: 8/21/2023

 

 

 

Articles

Excel

Other