How to Work With Named Ranges in Microsoft Excel

Formulas in Excel often use data and values from other cells using references to those cells. If you have a lot of formulas, the cell references can get confusing, making it hard to understand your formulas.

The Defined Names feature in Excel makes your formulas and other data less confusing and easier to understand. Instead of referencing a cell containing a value or a formula or a range of cells by the row and column headers (A1, B2, etc.), you can use a defined name assigned to that cell or range of cells.

Today we'll cover how to work with named ranges of cells including creating names, rules and scope for names, and how to edit, delete, view, and use names.

Defined Names vs. Table Names in Excel

Don't confuse Defined Names with Table Names. An Excel Table is a collection of data stored in records (rows) and fields (columns). Excel assigns a default name (Table1, Table2, etc.) to each table you create but you can change the names.

We've previously introduced you to Excel Tables and provided a more thorough tutorial about using pivot tables. To learn more about tables in Excel, see Microsoft's support page about creating tables.

Rules for Defined Names in Excel

When creating defined names in Excel, you must follow certain rules. For example, defined names can't contain spaces and the first character must be a letter, underscore (_), or backslash (\).

For a full list of rules for defining names, see the "Learn about syntax rules for names" section of this Microsoft support page.

Scope for Defined Names in Excel

The scope of a defined name in Excel refers to where the name is recognized without qualification, like a sheet name or workbook file name. Each name can have a local worksheet-level scope or a global workbook-level scope.

For more information about the scope of a defined name, see the "The scope of a name" section of this Microsoft support page.

Create a Named Range in Excel

You have a few different options for creating named ranges in Excel. You can define names using the Name box on the Formula bar, using heading cell text for a group of selected cells, or using the New Name dialog box.

Names don't have to refer to cells. You can use a name to label a value or a formula you use in many places in your workbook.

When defining a name for a cell or range of cells, absolute cell references are used by default.

Define a Name Using the Name Box

Using the Name box is the easiest way to define a name for a group of cells. Defining a name using the Name box creates names with workbook-level scope only, meaning the name can be referenced anywhere in the workbook it was created in without adding any sheet names or workbook file names.

We'll cover another method that allows you to create worksheet-level names later.

To use the Name box to define a name, do the following:

Select the cell or range of cells you want to name.
Type the name you want in the Name box on the left side of the Formula bar and press Enter.

Make sure you follow the rules for names mentioned earlier.

Define a Name From Heading Cell Text

If you've added row or column headings to your data, you can use these headings as names.

To define a name from a heading, do the following:

Select the cells you want to name including the label on the row or column.
On the Formulas tab, click Create from Selection in the Defined Names section, or press Ctrl + Shift + F3.

If the label contains spaces or other invalid characters like an ampersand (&), they are replaced with an underscore.

Create a named range using the Create from Selection option

On the Create Names from Selection dialog box, select the location of the heading label you want to use as a name.

For example, the heading label in the above image is at the top of the selected column. So, we check the Top row box and click OK.

The name is a workbook-level name and applies to the all the selected cells except for the heading label cell. When you select the cells referred to by the name, without the heading label cell, you'll see the name in the Name box.

Name created from a selection with a heading label

Define a Name Using the New Name Dialog Box

The New Name dialog box provides more options when creating names, like specifying worksheet-level scope or adding a comment to a name. Also, the New Name dialog box allows you to define a name for a value or a formula.

To use the New Name dialog box to name a cell or range of cells, start with the following:

If you're defining a name for a cell or range of cells, select the cells you want to name. It doesn't matter which cells are selected if you're defining a name for a value or formula.
On the Formulas tab, click Define Name in the Defined Names section.

On the New Name dialog box, specify the following information:

Name: Enter a name following the rules for defined names.
Scope: By default, Workbook is selected as the scope for the name. If you want to give the name worksheet-level scope, select the worksheet you want from the Scope dropdown list.
Comment: Add any notes you want to the name.
Refers to: The currently selected cells and the name of the currently active worksheet (or tab) are automatically entered in the Refers to box. If you're defining a name for a value or formula, replace what's in the Refers to box with an equals sign (=) followed by the value or formula.

The button on the right side of the Refers to box allows you to minimize the New Name dialog box and select cells on the worksheet to enter a range of cells. We'll talk more about how to use that button in the next section, "Edit a Named Range".

When you're finished entering information for the name, click OK to return to the worksheet.

Edit a Named Range in Excel

Once you've defined a name, you can change the name, its comment, and what it refers to.

To edit a defined name, or named range, click Name Manager in the Defined Names section of the Formulas tab.

If you have a long list of names, you can filter them to find the one you want to edit.

Click Filter in the upper-right corner of the Name Manager dialog box. Then, select the types of names you want to show. You can select multiple filters by clicking Filter again and selecting another option.

Filter the list of names on the Name Manager dialog box

To clear all the filters and show all the names again, click Filter and select Clear Filter.

To change the name or comment, select the name you want to change and click Edit.

Change the Name or Comment, or both, and click OK.

You can't change the Scope of an existing name. Delete the name and define it again with the correct scope.

You can change the cells the name Refers to on the Edit Name box. But you can also do this directly on the Name Manager dialog box, which we will do next. The method for changing the Refers to cell reference is the same on both dialog boxes.

To change the cell or range of cells this name refers to on the Name Manager dialog box, first select the name you want to change the cell reference for. Then, click the up-arrow button on the right side of the Refers to box.

The Name Manager dialog box shrinks down to just the Refers to box. To change the cell reference, do the following:

Select the cell or range of cells on the worksheet.
Click the button on the right side of the Refers to box.

You'll see the new cell reference in the Refers to box and the restored full Name Manager dialog box.

To accept the new cell reference, click the green check mark button. Or to revert the change back to the original cell reference, click the black X button.

Click Close to close the Name Manager dialog box.

Click the check mark to save the Refers to changes

View All Defined Names in an Excel Workbook

The Name Manager dialog box lists all the worksheet-level and workbook-level names you've defined in your workbook no matter which worksheet is currently active. But while the dialog box is open, you can't work on your worksheet.

It can be handy to have a list of names directly on the current worksheet. That way you can move around and decide which names need editing, or which names you want to remove while referring to your list.

You can generate a list of directly on an empty area of a worksheet. This list includes workbook-level names and worksheet-level names that have the scope of the currently active worksheet.

To begin generating a list of names, do the following:

Locate an empty area of the current worksheet where two columns are available and select the cell that will be the upper-left corner of the list.
In the Defined Names section of the Formula tab, click Use in Formula and select Paste Names, or press F3.

On the Paste Name dialog box, click Paste List.

The names and their respective cell references, values, and formulas are pasted into the cells on the worksheet.

Now you can review your list and decide what to do with the names. You can delete this list on the worksheet once you don’t need it anymore.

Delete a Named Range in Excel

If you have some names you're not using anymore, it's a good idea to delete them. Otherwise, your names list gets cluttered and hard to manage.

To open the Name Manager, click Name Manager in the Defined Names section of the Formulas tab.

On the Name Manager dialog box, select the name you want to remove and click Delete. Click OK on the confirmation dialog box.

If a name refers to a cell range, you can use that name to quickly navigate to and select that cell range.

To jump to a named range, click the down arrow on the Name box on the left side of the Formula bar and select the name you want.

Worksheet-level names only display in the Name box dropdown list if the worksheet they were created on is the currently active worksheet.

Also, you can type a name for a cell range you want to jump to in the Name box and press Enter. But make sure you've already defined the name. If not, you'll be applying that name to the currently selected cell or range of cells.

Use Names in Excel Formulas

Using defined names in formulas make your formulas easier to understand if you use descriptive names.

There are several ways you can enter a name into a formula. If you know exactly which name you want to use, you can type the name directly into the formula.

You can also use Formula AutoComplete. As you're typing your formula, Excel automatically lists valid names, and you can select a name to enter it into the formula. For example, the name Total is suggested to us on the following worksheet because the selected range of cells has that name.

You can also start typing your formula and then click Use in Formula in the Defined Names section of the Formula tab. Then, select the name you want to use in your formula from the dropdown list.

One last method of using a name in a formula is to start typing your formula and press F3. Then, select the name you want to use on the Paste Name dialog box and click OK, or press Enter.

Select a name on the Paste Name dialog box

Make Your Excel Worksheets Easier to Understand

Defined names in Excel can help keep your workbooks organized and easy to understand. Use names for not only cell ranges, but also to refer to values and formulas you use often.

To learn more, see our beginner's guide to Microsoft Excel.