All About “Named Ranges” in Excel

Hello Super Excel Users,

In this post I am going to cover every aspect of Named Ranges.

What is a named range?

Cells are generally named for a column and row that intersect, like A1 or C3. A range of cells is indicated by the colon between the beginning and ending cell of the range, like A1:C3, which would include all nine cells in between. A named range substitutes a user-specified name, like “Marks,” for marks numbers typed in cells A1 through C3.

Creating a named range

Select one or more cells, and then click in the Name Box. It’s located left of the formula bar at the top of the worksheet, below the Ribbon. If you’re not sure you’re in the right place, hover and the Name Box tip will pop up.

Rules for Creating Names

An Excel name can’t contain space characters, and there are other rules to follow when you’re creating a name.

  • The first character of a name must be a
    • letter
    • underscore (_)
    • backslash (\).
  • Remaining characters in the name can be
    • letters
    • numbers
    • periods
    • underscore characters
  • Spaces are not allowed as part of a name.
  • Names can contain uppercase and lowercase letters, and Excel does not distinguish between them. For example, North and NORTH are treated as the same name.
  • Names cannot be the same as a cell reference, such as A$35 or R2D2.
  • You cannot use C,c,R or r as a defined name — they are used as selection shortcuts.

Benefits of Defining Named Ranges

1. Data Validation

Using a named range to create a dropdown list in a cell makes data entry easier and cleaner.

Without a named range, the list of acceptable choices must be in the same worksheet as the target cell. Using a named range allows you to put that list anywhere in your workbook.

Making a tab called “lists,” which holds all the named ranges used in selection lists, is helpful.

Data Validation + Named Range

Data Validation + Named Range

 

  1. From the Data tab, in the Data Tools group, choose the Data Validation button.
  2. Choose List selection in the Allow: field.
  3. In the Source field type an “=” and the name of your range as illustrated.

The easiest mistake to make here is to forget the equals sign.

2. Formulas

A named range is the best choice when using an Absolute Cell Reference ($A$1) to refer your formula to the exact same cell (no matter where it’s copied).

In a VLookUp, for example, forgetting to set your Table Array to an absolute cell reference range can yield unpredictable results.

Simply setting a name range for your table array makes worrying about an absolute cell reference unnecessary. Even naming single cells can be helpful in creating a self-documenting formula.

3. Bookmarks

If you have elaborate workbooks, which contain target figures like net profit, average cost and gross income, you can highlight the result cells and name them for what they represent. Then, click in the name box from anywhere in the workbook, select the name, and it will take you there.

Also, You can quickly move to a named area of your worksheet by choosing a name in the name box.

4. A meaningful range name (such as Income) is much easier to remember than a range address (such as A1:A10).

5.  After you select a named cell or range, its name appears in the name box.

6.  Creating formulas is easier, because you can paste a cell or range name into a formula. They are easier to write. Particularly, if you are referencing cells in another worksheet.

7.  Names make your formulas more understandable and easier to use. For example, =Income-Expense is more intuitive than =A1-A10.

8.  They are easier to explain. Especially, if you are sending an Excel Workbook to a client or a colleague.

Create Named Range from selection with Headings

  • A quick way to create names is to base them on column/row headings. In the example shown in below video, the cells from row 2 onward will be named based on row 1 heading.
  • Select the cells with heading that are to be named. The headings can be above, below, left or right of the cells to be named. In below example it is above the range.
  • On the Ribbon, click the Formulas tab, then click Create from Selection.
  • In the Create From Selection window, add a check mark for the location of the Heading, then click OK. In this example, the Headings are at top of the column of the selected cells.
  • Select cells to see its name.

NOTE: If the labels contains spaces, they’re replaced with an underscore. Other invalid characters, such as & and # will be removed, or replaced by an underscore character.

Applying Names to existing Formulas

Using named ranges is a good practice. So we go ahead and created names for every cell/range in our complex workbook. But now, what about those formulas which still refer to cells by their addresses? Follow below steps to make formulas readable by replacing cell addresses with the names in one go.

  1. Select the cells containing formulas
  2. Go to Formulas > Define Name > Apply Names
  3. Hold Shift and select all the names
  4. Click OK
  5. Your formulas will now have names instead of cell references

See the video below to understand it.

Creating Dynamic Named Range

If the list that you want to name will change frequently, having items added and removed, you should create a dynamic named range. Unlike a static named range, a dynamic named range will automatically adjust in size, when the list changes.

A dynamic list is ideal if you are creating a drop down list, and want new items shown automatically, or if you are creating a pivot table, and add new records frequently.

In Excel 2007 and later, the easiest way to create a dynamic named range is to start by creating a “named Excel table”. Then, define a range based on one or more columns in that table.

In this example there is a list of parts on the worksheet, and a named table, and dynamic named ranges will be created.

First, to create the table:

  1. Select a cell in the Teams list
  2. On the Ribbon’s Insert tab, click Table
  3. Check that the correct range has been selected, and add a check mark to My Table Has Headers
  4. Click OK, to create the table.
Excel Table for Named Range

Excel Table for Named Range

 

Next, to create a dynamic list of Teams:

  1. Select cells A2:A15, which contain the Teams (not the heading)
  2. Click in the Formula Bar, and type a one-word name for the range: Teams
  3. Press the Enter key, to complete the name.
Named Range in Table

Named Range in Table

To see the name’s definition, click the Ribbon’s Formulas tab, and click Name Manager. There are two named items in the list — Teams Table, with the default name, Table1, and the Teams List, which is based on the Teams field in Table1.

Dynamic Named Range

Dynamic Named Range

Because the Teams named range is based on a named table, the list will automatically adjust in size if you add or remove Teams in the list.

NOTE: If you are using Excel 2003, which does not have named tables, you can use a dynamic formula to define a named range. As new items are added, the range will automatically expand.

Note: Dynamic named ranges will not appear in the Name Box dropdown list. However, you can type the names in the Name Box, to select the range on the worksheet.

    1. Choose Insert>Name>Define
    2. Type a name for the range, e.g. NameList
Excel names dialog box

Excel names dialog box

  1. In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in the column, e.g.:
    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
    In this example, the list is on Sheet1, starting in cell A1
    The arguments used in this Offset function are:

    • Reference cell: Sheet1!$A$1
    • Rows to offset: 0
    • Columns to offset: 0
    • Number of Rows: COUNTA(Sheet1!$A:$A)
    • Number of Columns: 1
    • Note: for a dynamic number of columns, replace the 1 with:
      COUNTA(Sheet1!$1:$1)
  2. Click OK

Thats all about NAMED RANGES. Please Let me know how you use Named Ranges in your workbook.

Thanks

CA Dhaval Paun

 

 

Share

Comments

  • Craig Hatmaker
    Reply

    Don’t forget Structured References. Instead of creating the name “Teams” use the automatically created dynamic named range that comes with tables. In this case: “Table1[Teams]”.

    Structured References are fantastic but have a few quirks. While they work well in most formulas. Ex:
    =INDEX(Table1[Matches],MATCH(“India”,Table1[Teams]))

    They don’t work with Data Validation without INDIRECT. Ex:
    =INDIRECT(“Table1[Teams]”)

    • Dhaval

      Yes you are right Craig, and thats why I am motivated to write a blog article dedicated to “Excel Data Tables”, keep watching this space for updates.

  • Barrington Salter
    Reply

    I agree Craig, structured references are fantastic. Very powerful for tables providing the data for dynamic and interactive dashboards. They’re like Vlookup on Steriods.

  • Lubek
    Reply

    Dhaval, your description is very good.
    But the base is a bit deficient, although traditional and by Microsoft traditionally used and so named.
    This feature is not only named range – static or dynamic. It is NAMED FORMULA (or NamedFormula) actually. It can be some value (=”love”), more complicated formula (=4+5+78*12+A1+(A2=$A$2) ) or (=SUM(A1:F111) ) and so on.
    And it can be “range”, it means formula of range, of course (=A3:D1) or (=$A$3:D1) etc, with absolute coordinates or not. Not as range but instead range, as formula calling some range, the same way as in cells. Or some value or some array. As formula can. And As Excel functions. It is almost the same.
    There is only one difference from between NamedFormulas and functions: function can have arguments to fulfil in using it, NamedFormula never. Not every function has arguments, but most of it. Both function and NamedFormula can have covered inputs besides open arguments (parameters).
    So there are three types of functions:
    1) producer (Microsoft) functions (nearly a half of a thousand),
    2) UDF (user defined function) via VBA,
    3) Names (NamedFormula) via Names Manager (or left part of formula row).

Subscribe for Newsletter