Creating and Managing Named Ranges in Excel

We’ve all been annoyed when a seemingly minor change breaks existing formulas or ruins subtotals. Named ranges offer an easy means to ensure that spreadsheet structure and formulas remain intact.

Note: if you find this post useful and want a more robust tool for managing named ranges, consider downloading our free Name Manager Plus add-in.

Overview

You’re aware of how to identify a cell on a spreadsheet. Every cell has a specific address that lies within the grid. The cell on the first column of the first row has a cell address of A1. Down and to the right, the cell address is B2. Each individual cell within a sheet has a unique address.

Cells can hold a second identifying attribute: a cell name or, as this post will refer to it, a range name. A range name can be any value (within formatting limitations) and will remain as that name regardless of actions that happen around it (such value change or insertion or deletion of cells). The Excel name manager requires a few steps to change or remove the name of a cell, making it part of the workbook structure that can withstand significant content modifications.

Cell names are presented in the name textbox, to the left of the formula bar. If no name has been assigned to a cell, that cell’s address will appear in the name textbox. If you click on cell C4 in a new workbook, the name field will show “C4”.

2016-11-01-1
The selected cell has an address and name of “C4”.

As a user/developer, you have the ability to assign a name. By clicking in the name text box, a text cursor will appear allowing you to modify the name. Type a new name and press enter.

2016-11-01-2
Names ranges can be typed in to the left of the formula bar.

Now, instead of showing the cell address, the name textbox shows the cells custom name.

Benefits to Named Ranges

Formulas

Formulas can reference specific named ranges, making them easier to understand and trance.

Named ranges add clarity to mathematical formulas.
Named ranges add clarity to mathematical formulas.

In this example, without named ranges, the formula in cell E2 would be “=C2*D2”. The formula “=Price*Quantity” provides clarity. This approach can be even more valuable for long and complex formulas.

Sheet Modifications

Unlike cell addresses, named ranges will move when a row is inserted above. In the example below, the cell at address C4 given the name “This_Named_Range”.

2016-11-01-4
Initial address of named range “This_Named_Range” is C4.

 

Now, if a row is inserted above row 4, the named range moves down to the cell with address C5. The cell at address C5 now inherits the name “This_Named_Range”. The cell at address C4 no longer has a name.

2016-11-01-6
After inserting a new row above, the named range “This_Named_Range” now has an address of C5.

This is an extremely valuable feature when working with VBA. If a script references a value stored in cell address C4, and the value moves to address C5 because of a row insert, the script would need to be updated.

For example, Range(“C4”).Value would only return a value before the row is inserted, and would return a blank value after the row is inserted.

However, Range(“This_Named_Range”).Value would return the value in both instances, as the range name moves with the cell, regardless of cell address.

Grouping Cells Together

2016-11-01-7
A name can be assigned to a group of cells.

This technique is great for creating dropdown lists, creating arrays in VBA, or asking VBA search through grouped ranges.

Managing Named Ranges

Name Manager

The Name Manager is the primary console for managing named ranges. We’ll use it to access named ranges displaying New York City population statistics. To access the Name Manager, (1) select Formulas > (2) Name Manager in the ribbon.

Path to get to the name manager.
Path to get to the name manager.

The Name Manager shows several important details about named ranges that currently exist in the workbook.

2016-11-01-9
The Name Manager dialog box.
  • Column 1: the given name of a range.
  • Column 2: an array of the values currently in the range.
  • Column 3: the sheet and address the named range currently belongs to.
  • Column 4: the range scope – see below for an explanation of the scope of a named range.
  • Column 5: comments, which can be added when editing or creating a named range through the Name Manager.

The dialog box has 3 buttons at the top. The names are fairly obvious, but to go through all the features:

New

The New Name dialog box.
The New Name dialog box.

This is an alternate method of creating a new named range. Upon pressing the button, a New Name dialog box will appear. In this dialog, you can specify a Name, Scope, Comment, and Refers to (cell address). The address initially shown will be the spreadsheet selection prior to opening the Name Manager. You can change this by either typing in the address, or using the cell selection tool. You can also select another sheet to narrow the scope. After pressing OK, the named range will be set.

Edit

The Edit dialog box looks exactly like the New Name dialog box. This option allows you to change the attributes of an existing named range. This is the only screen (outside of VBA) that permits the user to alter a range name.

Delete

No major surprise with this button. Pressing it will prompt you to confirm deletion of a selected named range.

Refers to

There is a Refers to (address) formula bar at the bottom of the Name Manager. This allows you to adjust the cells in the selected named range. Doing so will make the Edit dialog box appear after pressing enter. There are no additional features here; just a different way of getting to the edit dialog box.

Scope of Named Range

When creating a named range through the New Name dialog box in the Name Manager, you can select a scope. The scope of a named range identifies the workbook and worksheet it belongs to. A named range can belong to a workbook, where it is a unique name within the entire workbook, or it can belong to a specific sheet, where it is a unique name within a single spreadsheet.

This feature allows you to build spreadsheets with duplicate named ranges, as long as the named ranges are on different sheets. This is a good feature if you have a VBA script which duplicates sheets containing named ranges, or if you have some duplicates of the same template in a workbook. Otherwise, it is recommended to keep the names of named ranges unique for easier tracking.

Note that you cannot change the scope of a named range after it has been created.

Moving Named Ranges

Cut/Paste

Named ranges can be moved with cut and paste functions. Copy and paste will not duplicate or move a named range (there can be only one named range per sheet). Cut and past will work ONLY if you select the same range of cells as the named range.

If the named range only contains one cell, you can cut and paste that one cell — the named range will move. However, if the named range contains multiple cells, you must cut ALL cells within the named range and paste them. If you do not cut the entire named range, the cell contents will move, but not the named range.

Expanding Named Ranges

Going back to our earlier screen capture of New York City statistics. If a sixth borough were to join New York City, the named range will need to be expanded to encompass 6 values instead of 5. If Nassau County were to become the 6th borough and we wanted to include it in the list alphabetically, we could simply insert a row (right click > insert) between Manhattan and Queens (rows 4 and 5) and the address of the named range would change from A1:A6 to A1:17.

If Yonkers were to join as the 6th borough, it would need to be added at the end (without any cell insertion), and the named range would not change. If a new item is being added to the end of a named range, the named range must be extended in the Name Manager. Also note that inserting a cell at the at the first blank cell immediately following the range will not expand the named range.

Shrinking/Deleting Named Ranges

Let’s say the borough of Staten Island were to secede from New York City, the named range will need to be shrunk by one cell. This can be done by deleting (right click > delete) and shifting the cells. If the named range exists in a column, this can be done by deleting the cells and shifting up. If the named range is a row, the cells should be shifted right or left. Be careful with this option as it may interfere with other components of the spreadsheet, especially the alignment of content that exists elsewhere.

Simply clearing the cells contents with the keyboards delete or backspace keys will not adjust the named range.

If you delete (right click > delete) ALL the cells within a named range, the named range will be deleted and will no longer appear in the Name Manager.

Other Notes

  • If you find the named range feature useful in your spreadsheets, consider downloading our free Name Manager Plus add-in.
  • You can also create a named range by selecting cells, right clicking, then selecting Define Name. This will open the New Name dialog box without going through the Name Manager.

Alternate method of accessing the New Name dialog box.

  • The names of named ranges cannot contain any spaces or special characters except for _ (underscore). The first character of a named range cannot be a number. A named range cannot be identical to any cell address within the spreadsheet.
  • A dropdown arrow in the right part of the range name textbox will display a list of all named ranges within the worksheet. If you click on a named range within the list, the cells in that range will be selected. This is a great method for finding ranges in busy workbooks with many worksheets.
  • If you type in a named range that already exists within the scope, the cell cursor will move to the named range.

Leave a Reply