change define name excel

• Shop 'Til You Drop
• Our Picks: The Best Tech Gifts This Year

How to Define and Edit a Named Range in Excel

Give descriptive names to specific cells or ranges of cells

What to Know

• Highlight the desired range of cells and type a name in the Name Box above column A in the worksheet.
• Alternatively, highlight the desired range, select the Formulas tab on the ribbon, then select Define Name .
• To manage range names, go to the Formulas tab, select Name Manager , choose a name, then select Delete or Edit .

This article explains how to define and edit names for ranges in Excel for Microsoft 365, Excel 2019, 2016, 2013, and 2010.

Defining and Managing Names with the Name Box

One way, and possibly the easiest way, of defining names is using the Name Box , located above column A in the worksheet. You can use this method to create unique names that are recognized by every sheet in a workbook.

To create a name using the Name Box as shown in the image above:

Highlight the desired range of cells in the worksheet.

Type the desired name for that range in the Name Box , such as Jan_Sales .

Press the Enter key on the keyboard. The name is displayed in the Name Box .

The name is also displayed in the Name box whenever the same range of cells is highlighted in the worksheet. It is also displayed in the Name Manager .

Naming Rules and Restrictions

Syntax rules to remember when creating or editing names for ranges are as follows:

• A name can't contain spaces.
• The first character of a name must be either a letter, underscore, or backslash.
• The remaining characters can only be​ letters, numbers, periods, or underscore characters.
• The maximum name length is 255 characters.
• Uppercase and lowercase letters are indistinguishable to Excel, so Jan_Sales and jan_sales are seen as the same name by Excel.
• Cell reference cannot be used as names such as A25 or R1C4 .

What Is a Named Range?

A named range , range name , or defined name all refer to the same object in Excel; it's a descriptive name — such as  Jan_Sales  or  June_Precip — that is attached to a specific cell or range of cells in a worksheet or workbook . Named ranges make it easier to use and identify data when creating charts , and in formulas such as:

Additionally, since a named range does not change when a formula is copied to other cells , it provides an alternative to using absolute cell references in formulas. There are three methods for defining a name in Excel: using the name box, the new name dialog box, or the name manager.

Defining and Managing Names with the Name Manager

A second method for defining names is to use the New Name dialog box . This dialog box is opened using the Define Name option located in the middle of the Formulas tab of the ribbon . The New Name dialog box makes it easy to define names with a worksheet level scope.

To create a name using New Name dialog box:

Select the Formulas tab of the ribbon.

Select the Define Name option to open the New Name dialog box.

Enter the Name , Scope , and Range in the dialog box.

Once completed, select OK to return to the worksheet. The name displays in the  Name Box whenever the defined range is selected.

The Name Manager can be used to both define and manage existing names; it is located next to the Define Name option on the Formulas tab of the ribbon.

When defining a name in the Name Manager it opens the New Name dialog box outlined above. The complete list of steps are as follows:

Select the Name Manager icon in the middle of the ribbon to open the Name Manager .

In the Name Manager , select the New button to open the New Name dialog box.

Enter a Name , Scope , and Range . Select OK to return to the worksheet. The name displays in the  Name Box whenever the defined range is selected.

Deleting or Editing Names

With the Name Manager open:

In the window containing the list of names, select the name to be deleted or edited.

To delete the name, select the Delete button above the list window.

To edit the name, select the Edit button to open the Edit Name dialog box.

In the Edit Name dialog box, you can edit the chosen name, add comments about the name, or change the existing range reference.

The scope of an existing name cannot be changed using the edit options. To change the scope, delete the name and redefine it with the correct scope.

Filtering Names

The Filter button in the Name Manager makes it easy to:

• Find names with errors – such as an invalid range.
• Determine the scope of a name – whether worksheet level or workbook.
• Sort and filter listed names – defined (range) names or table names.

The filtered list is displayed in the list window in the Name Manager .

Defined Names and Scope in Excel

All names have a scope which refers to the locations where a specific name is recognized by Excel. A name's scope can be for either individual worksheets ( local scope ) or for an entire workbook ( global scope ). A name must be unique within its scope, but the same name can be used in different scopes.

The default scope for new names is the global workbook level. Once defined, the scope of a name cannot easily be changed. To change the scope of a name, delete the name in the Name Manager and redefine it with the correct scope.

Local Worksheet Level Scope

A name with a worksheet level scope is valid only for the worksheet for which it was defined. If the name Total_Sales has a scope of sheet 1 of a workbook, Excel will not recognize the name on sheet 2 , sheet 3 , or any other sheet in the workbook. This makes it possible to define the same name for use on multiple worksheets – as long as the scope for each name is restricted to its particular worksheet.

Using the same name for different sheets might be done to ensure continuity between worksheets and ensure that formulas that use the name Total_Sales always refer to the same range of cells in multiple worksheets within a single workbook.

To distinguish between identical names with different scopes in formulas, precede the name with the worksheet name, such as:

Names created using the Name Box  always have a global workbook level scope unless both sheet name and the range name are entered into the name box when the name is defined.

• Name: Jan_Sales, Scope — global workbook level
• Name: Sheet1!Jan_Sales, Scope — local worksheet level

Global Workbook Level Scope

A name defined with a workbook level scope is recognized for all worksheets in that workbook. A workbook level name can, therefore, only be used once within a workbook, unlike the sheet level names discussed above.

A workbook level scope name is not, however, recognized by any other workbook, so global level names can be repeated in different Excel files. For example, if the Jan_Sales name has a global scope, the same name could be used in different workbooks titled 2012_Revenue , 2013_Revenue , and 2014_Revenue .

Scope Conflicts and Scope Precedence

It is possible to use the same name at both the local sheet level and workbook level because the scope for the two would be different. Such a situation, however, would create a conflict whenever the name was used.

To resolve such conflicts, in Excel, names defined for the local worksheet level take precedence over the global workbook level. In such a situation, a sheet-level name of 2014_Revenue would be used instead of a workbook level name of 2014_Revenue .

To override the rule of precedence, use the workbook level name in conjunction with a specific sheet-level name such as:

The one exception to overriding precedence is a local worksheet level name that has a scope of sheet 1 of a workbook. Scopes linked to sheet 1 of any workbook cannot be overridden by global level names.

Get the Latest Tech News Delivered Every Day

• How to Create a Drop-Down List in Excel
• Range Definition and Use in Excel Spreadsheets
• What Is the Name Box in Excel?
• How to Format Excel Spreadsheets Using Cell Styles
• How to Move Around and Between Worksheet Tabs in Excel
• How to Create a Drop-Down List Using Data From Another Excel Worksheet
• Do's and Don'ts of Entering Data in Excel
• Quickly Find Multiple Fields of Data With Excel's VLOOKUP Function
• How to Rename a Worksheet in Excel
• Using Excel's ISNUMBER Function
• Understand the Basic Excel Screen Elements
• Excel Column and Row Headings
• How to Lock Cells in Excel Worksheets and Protect Data
• Hide/Unhide Scroll Bars and Reset the Vertical Slider Range in Excel
• Excel Step by Step Basic Tutorial
• Relative, Absolute, and Mixed Cell References in Excel and Sheets

By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts.

How-To Geek

How to assign a name to a range of cells in excel.

When creating formulas in Excel, you can reference cells from another part of the worksheet in your formulas.

Quick Links

How to create a name for a cell or a range of cells using the name box, how to edit names using the name manager, how to delete a name using the name manager, how to create a name using the "new name" dialog box, how to use a name to represent a constant value.

When creating formulas in Excel, you can reference cells from another part of the worksheet in your formulas. But if you have a lot of formulas, all those cell references can get confusing. There's an easy way to remove the confusion.

Excel includes a feature, called "Names", that can make your formulas more readable and less confusing. Instead of referencing a cell or range of cells, you can assign a name to that cell or range and use that name in formulas. This will make your formulas much easier to understand and maintain.

Related: How to Use VLOOKUP in Excel

In the formula below, we reference a range of cells (in bold) from another worksheet, called "Product Database", in the same workbook. In this case, the name of the worksheet gives us a good idea as to what's contained in the range of cells, "A2:D7". However, we could use a name for this range of cells to make the formula shorter and easier to read.

=IF(ISBLANK(A11),"",VLOOKUP(ALL,' Product Database'!A2:D7 ,2,FALSE))

NOTE: For more information about the VLOOKUP function used in the formula above, see our article about using VLOOKUP in Excel . You can also learn how to use the "IF" function and other useful functions .

To assign a name to a range of cells, select the cells you want to name. The cells don't have to be contiguous. To select non-contiguous cells, use the "Ctrl" key when selecting them.

Click the mouse in the "Name Box" above the cell grid.

Type a name for the range of cells in the box and press "Enter". For example, we called the selected cells on our "Product Database" worksheet "Products". There are syntax rules you must abide by when choosing a name. You can only begin a name with a letter, an underscore (_), or a backslash (\). The rest of the name can consist of letters, numbers, periods, and underscores. There are additional syntax rules about what's valid and not when defining names.

Remember the formula from the beginning of this article? It contained a reference to the "Products Database" worksheet in the workbook and a range of cells on the that worksheet. Now, that we created the "Products" name to represent the range of cells on our "Products Database" worksheet, we can use that name in the formula, shown in bold below.

=IF(ISBLANK(A11),"",VLOOKUP(ALL, Products ,2,FALSE))

NOTE: When creating a name using the "Name Box", the scope of the name defaults to the workbook. That means that the name is available to be used on any worksheet in the current workbook without referencing a specific worksheet. You can choose to limit the scope to a specific worksheet so the worksheet name has to be used when referring to the name, such as in the example at the beginning of this article.

Excel provides a tool, called "Name Manager", that makes it easy to find, edit, and delete the names in your workbook. You can also use the Name Manager to create names, if you want to specify more details about the name. To access the Name Manager, click the "Formulas" tab.

In the "Defined Names" section of the "Formulas" tab, click "Name Manager".

The Name Manager dialog box displays. To edit an existing name, select the name in the list and click "Edit". For example, we're going to edit the "Products" name.

The "Edit Name" dialog box displays. You can change the "Name" itself as well as add a "Comment" to the name, providing more details about what the name represents. You can also change the range of cells to which this name is assigned by clicking the "Expand Dialog" button on the right side of the "Refers to" edit box.

NOTE: You'll see that the "Scope" drop-down list is grayed out. When you edit an existing name, you cannot change the "Scope" of that name. You must choose the scope when you first create the name. If you want the scope to be a specific worksheet, rather than the entire workbook, you can create a name in a way that allows you to specify the scope initially. We'll show you how to do that in a later section.

As an example, say we added another product to our "Product Database" and we want to include it in the cell range named "Products". When we click on the "Expand Dialog" button, the "Edit Name" dialog box shrinks down to contain only the "Refers to" edit box. We select the range of cells directly on the "Product Database" worksheet, including the row containing the newly added product. The worksheet name and cell range are automatically entered into the "Refers to" edit box. To accept your selection and return to the full "Edit Name" dialog box, click the "Collapse Dialog" button. Click "OK" on the "Edit Name dialog box to accept the changes to the name.

If you decide you don't need a name anymore, it's easy to delete it. Simply, access the "Name Manager" dialog box as we discussed in the previous section. Then, select the name you want to delete in the list of names and click "Delete".

On the confirmation dialog box that displays, click "OK" if you're sure you want to delete the selected name. You are returned to the "Name Manager" dialog box. Click "Close" to close it.

When you create a new name by selecting one or more cells and then entering a name in the "Name Box", the default scope of the name is the entire workbook. So, what do you do if you want to limit the scope of a name to just a specific worksheet?

Select the cells to which you want to assign the name. Click the "Formulas" tab and then click "Define Name" in the "Defined Names" section.

NOTE: You don't have to select the cells first. You can also select them using the "Expand Dialog" button later on, if you want.

The "New Name" dialog box displays. Notice that it's very similar to the "Edit Name" dialog box mentioned earlier. The main difference is that now you can change the scope of the name. Say we want to limit the scope of the name to just the "Invoice" worksheet. We would do this if we wanted to be able to use the same name for a range of cells on another worksheet.

First, we'll enter the name we want to use, which in our case is "Products". Remember the syntax rules when creating your name. Then, to limit the scope of the "Products" name to only the "Invoice" worksheet, we select that from the "Scope" drop-down list.

NOTE: The "New Name" dialog box can also be accessed by clicking "New" on the "Name Manager" dialog box.

Enter more details about the name, if desired, in the "Comment" box. If you didn't select the cells to which you're assigning the name, click the "Expand Dialog" button to the right of the "Refers to" edit box to select the cells the same way we did when we edited the name earlier. Click "OK" to finish creating the new name.

The name is automatically inserted into the same "Name Box" we used to assign a name to a range of cells at the beginning of this article. Now, we can replace the cell range reference ('Product Database'!$A$2:$D:7) with the name (Products) in the formulas on the "Invoice" worksheet, like we did earlier in this article.

You don't have to refer to cells when creating a name. You can use a name to represent a constant, or even a formula. For example, the worksheet below shows the exchange rate used to calculate the price in Euros for the various sizes of widgets. Because the exchange rate changes often, it would be useful if it was located in a place that's easy to find and update. Since names are easy to edit, as discussed earlier, we can create a name to represent the exchange rate and assign a value to the name.

Notice the formula contains an absolute cell reference to a cell containing the current exchange rate. We'd rather use a name that will refer to the current exchange rate so it's easier to change and formulas using the exchange rate are easier to understand.

To create a name that will be assigned to a constant value, open the "New Name" dialog box by clicking the "Formulas" tab and then clicking "Define Name" in the "Defined Names" section. Enter a name to represent the constant value, such as "ExchangeRate". To assign a value to this name, enter an equal sign (=) in the "Refers to" edit box followed by the value. There should not be a space between the equal sign and the value. Click "OK" to finish creating the name.

NOTE: If there's a formula you use in many places in your workbook, you can enter that formula into the "Refers to" edit box so you can simply enter the name in every cell where you need to use the formula.

Now, we can use the new name in formulas where we want to use the exchange rate. When we click on a cell with a formula that contains an absolute cell reference , notice the result is "0.00". That's because we removed the exchange rate from the cell being referenced. We'll replace that cell reference with the new name we created.

Highlight the cell reference (or other part of the formula you want to replace with a name) and start typing the name you created. As you type, any matching names display in a popup box. Select the name you want to insert into the formula by clicking on it in the popup box.

The name is inserted into the formula. Press "Enter" to accept the change and update the cell.

Note that the result is updated using the exchange rate referred to by the name.

Names are very useful if you create complex Excel workbooks with a lot of formulas. When you need to distribute your workbooks to others, using names makes it easier for others, as well as yourself, to understand your formulas.

Named Ranges in Excel

Named ranges make formulas easier to read, faster to develop, and more portable. They're also useful for data validation, hyperlinks, and dynamic ranges. This article shows you how you can use named ranges to build better spreadsheets, and better formulas.

Named ranges are one of these crusty old features in Excel that few users understand. New users may find them weird and scary, and even old hands may avoid them because they seem pointless and complex.

But named ranges are actually a pretty cool feature. They can make formulas *a lot* easier to create, read, and maintain. And as a bonus, they make formulas easier to reuse (more portable).

In fact, I use named ranges all the time when testing and prototyping formulas. They help me get formulas working faster. I also use named ranges because I'm lazy, and don't like typing in complex references :)

The basics of named ranges in Excel

What is a named range.

A named range is just a human-readable name for a range of cells in Excel. For example, if I name the range A1:A100 "data", I can use MAX to get the maximum value with a simple formula:

The beauty of named ranges is that you can use meaningful names in your formulas without thinking about cell references. Once you have a named range, just use it just like a cell reference. All of these formulas are valid with the named range "data":

Video: How to create a named range

Creating a named range is easy

Creating a named range is fast and easy. Just select a range of cells, and type a name into the name box. When you press return, the name is created:

To quickly test the new range, choose the new name in the dropdown next to the name box. Excel will select the range on the worksheet.

Excel can create names automatically (ctrl + shift + F3)

If you have well structured data with labels, you can have Excel create named ranges for you. Just select the data, along with the labels, and use the "Create from Selection" command on the Formulas tab of the ribbon:

You can also use the keyboard shortcut control + shift + F3.

Using this feature, we can create named ranges for the population of 12 states in one step:

When you click OK, the names are created. You'll find all newly created names in the drop down menu next to the name box:

With names created, you can use them in formulas like this

Update named ranges in the Name Manager (Control + F3)

Once you create a named range, use the Name Manager (Control + F3) to update as needed. Select the name you want to work with, then change the reference directly (i.e. edit "refers to"), or click the button at right and select a new range.

There's no need to click the Edit button to update a reference. When you click Close, the range name will be updated.

Note: if you select an entire named range on a worksheet, you can drag to a new location and the reference will be updated automatically. However, I don't know a way to adjust range references by clicking and dragging directly on the worksheet. If you know a way to do this, chime in below!

See all named ranges (control + F3)

To quickly see all named ranges in a workbook, use the dropdown menu next to the name box.

If you want to see more detail, open the Name Manager (Control + F3), which lists all names with references, and provides a filter as well:

Note: in older versions of Excel on the Mac, there is no Name Manager, and you'll see the Define Name dialog instead.

Copy and paste all named ranges (F3)

If you want a more persistent record of named ranges in a workbook, you can paste the full list of names anywhere you like. Go to Formulas > Use in Formula (or use the shortcut F3), then choose Paste names > Paste List:

When you click the Paste List button, you'll see the names and references pasted into the worksheet:

See names directly on the worksheet

If you set the zoom level to less than 40%, Excel will show range names directly on the worksheet:

Thanks for this tip, Felipe!

Names have rules

When creating named ranges, follow these rules:

• Names must begin with a letter, an underscore (_), or a backslash (\)
• Names can't contain spaces and most punctuation characters.
• Names can't conflict with cell references – you can't name a range "A1" or "Z100".
• Single letters are OK for names ("a", "b", "x", etc.), but the letters "r" and "c" are reserved.
• Names are not case-sensitive – "home", "HOME", and "HoMe" are all the same to Excel.

Named ranges in formulas

Named ranges are easy to use in formulas.

For example, lets say you name a cell in your workbook "updated". The idea is you can put the current date in the cell (Ctrl + ;) and refer to the date elsewhere in the workbook.

The formula in B8 looks like this:

You can paste this formula anywhere in the workbook and it will display correctly. Whenever you change the date in "updated", the message will update wherever the formula is used. See this page for more examples.

Named ranges appear when typing a formula

Once you've created a named range, it will appear automatically in formulas when you type the first letter of the name. Press the tab key to enter the name when you have a match and want Excel to enter the name.

Named ranges can work like constants

Because named ranges are created in a central location, you can use them like constants without a cell reference. For example, you can create names like "MPG" (miles per gallon) and "CPG" (cost per gallon) with and assign fixed values:

Then you can use these names anywhere you like in formulas, and update their value in one central location.

Named ranges are absolute by default

By default, named ranges behave like absolute references. For example, in this worksheet, the formula to calculate fuel would be:

The reference to D2 is absolute (locked) so the formula can be copied down without D2 changing.

If we name D2 "MPG" the formula becomes:

Since MPG is absolute by default, the formula can be copied down column D as-is.

Named ranges can also be relative

Although named ranges are absolute by default, they can also be relative.  A relative named range refers to a range that is relative to the position of the active cell at the time the range is created . As a result, relative named ranges are useful building generic formulas that work wherever they are moved.

For example, you can create a generic "CellAbove" named range like this:

• Select cell A2
• Control + F3 to open Name Manager
• Tab into 'Refers to' section, then type: =A1

CellAbove will now retrieve the value from the cell above wherever it is it used.

Important: make sure the active cell is at the correct location before creating the name.

Apply named ranges to existing formulas

If you have existing formulas that don't use named ranges, you can ask Excel to apply the named ranges in the formulas for you. Start by selecting the cells that contain formulas you want to update. Then run Formulas > Define Names > Apply Names.

Excel will then replace references that have a corresponding named range with the name itself.

You can also apply names with find and replace:

Key benefits of named ranges

Named ranges make formulas easier to read.

The biggest single benefit to named ranges is they make formulas easier to read and maintain. This is because they replace cryptic references with meaningful names. For example, consider this worksheet with data on planets in our solar system.  Without named ranges, a VLOOKUP formula to fetch "Position" from the table is quite cryptic:

However, with B3:E11 named "data", and H4 named "planet", we can write formulas like this:

At a glance, you can see the only difference in these formulas in the column index.

Named ranges make formulas portable and reusable

Named ranges can make it much easier to reuse a formula in a different worksheet. If you define names ahead of time in a worksheet, you can paste in a formula that uses these names and it will "just work". This is a great way to quickly get a formula working.

For example, this formula counts unique values in a range of numeric data:

To quickly "port" this formula to your own worksheet, name a range "data" and paste the formula into the worksheet. As long as "data" contains numeric values, the formula will work straightway.

Tip: I recommend that you create the needed range names *first* in the destination workbook, then copy in the formula as text only (i.e. don't copy the cell that contains the formula in another worksheet, just copy the text of the formula). This stops Excel from creating names on-the-fly and l ets you to fully control the name creation process. To copy only formula text, copy text from the formula bar, or copy via another application (i.e. browser, text editor, etc.).

Named ranges can be used for navigation

Named ranges are great for quick navigation. Just select the dropdown menu next to the name box, and choose a name. When you release the mouse, the range will be selected. When a named range exists on another sheet, you'll be taken to that sheet automatically.

Named ranges work well with hyperlinks

Named ranges make hyperlinks easy. For example, if you name A1 in Sheet1 "home", you can create a hyperlink somewhere else that takes you back there.

To use a named range inside the HYPERLINK function , add a hash (#) in front of the named range:

You can use this same syntax to create a hyperlink to a table:

Note: in older versions of Excel you can't link to a table like this. However, you can define a name equal to a table (i.e. =Table1) and hyperlink to that.

Named ranges for data validation

Names ranges work well for data validation, since they let you use a logically named reference to validate input with a drop down menu. Below, the range G4:G8 is named "statuslist", then apply data validation with a List linked like this:

The result is a dropdown menu in column E that only allows values in the named range:

Dynamic Named Ranges

Names ranges are extremely useful when they automatically adjust to new data in a worksheet. A range set up this way is referred to as a "dynamic named range". There are two ways to make a range dynamic: formulas and tables.

Dynamic named range with a Table

A Table is the easiest way to create a dynamic named range. Select any cell in the data, then use the shortcut Control + T:

When you create an Excel Table, a name is automatically created (e.g. Table1), but you can rename the table as you like. Once you have created a table, it will expand automatically when data is added.

Dynamic named range with a formula

You can also create a dynamic named range with formulas, using functions like OFFSET and INDEX. Although these formulas are moderately complex, they provide a lightweight solution when you don't want to use a table. The links below provide examples with full explanations:

• Example of dynamic range formula with INDEX
• Example of dynamic range formula with OFFSET

Table names in data validation

Since Excel Tables provide an automatic dynamic range, they would seem to be a natural fit for data validation rules, where the goal is to validate against a list that may be always changing. However, one problem with tables is that you can't use structured references directly to create data validation or conditional formatting rules. In other words, you can't use a table name in conditional formatting or data validation input areas.

However, as a workaround, you can define named a named range that points to a table, and then use the named range for data validation or conditional formatting. The video below runs through this approach in detail.

Video: How to use named ranges with tables

Deleting named ranges

Note: If you have formulas that refer to named ranges, you may want to update the formulas first before removing names. Otherwise, you'll see #NAME? errors in formulas that still refer to deleted names. Always save your worksheet before removing named ranges in case you have problems and need to revert to the original.

Named ranges adjust when deleting and inserting cells 

When you delete *part* of a named range, or if  insert cells/rows/columns inside a named range, the range reference will adjust accordingly and remain valid. However, if you delete all of the cells that enclose a named range, the named range will lose the reference and display a #REF error. For example, if I name A1 "test", then delete column A, the name manager will show "refers to" as:

Delete names with Name Manager

To remove named ranges from a workbook manually, open the name manager, select a range, and click the Delete button. If you want to remove more than one name at the same time, you can Shift + Click or Ctrl + Click to select multiple names, then delete in one step.

Delete names with errors

If you have a lot of names with reference errors, you can use the filter button in the name manager to filter on names with errors:

Then shift+click to select all names and delete.

Named ranges and Scope

Named ranges in Excel have something called "scope", which determines whether a named range is local to a given worksheet, or global across the entire workbook. Global names have a scope of "workbook", and local names have a scope equal to the sheet name they exist on. For example, the scope for a local name might be "Sheet2". 

The purpose of scope

Named ranges with a global scope are useful when you want all sheets in a workbook to have access to certain data, variables, or constants. For example, you might use a global named range a tax rate assumption used in several worksheets.

Local scope

Local scope means a name is works only on the sheet it was created on. This means you can have multiple worksheets in the same workbook that all use the same name. For example, perhaps you have a workbook with monthly tracking sheets (one per month) that use named ranges with the same name, all scoped locally. This might allow you to reuse the same formulas in different sheets. The local scope allows the names in each sheet to work correctly without colliding with names in the other sheets.

To refer to a name with a local scope, you can prefix the sheet name to the range name:

Range names created with the name box automatically have global scope. To override this behavior, add the sheet name when defining the name:

Global scope

Global scope means a name will work anywhere in a workbook. For example, you could name a cell "last_update", enter a date in the cell. Then you can use the formula below to display the date last updated in any worksheet.

Global names must be unique within a workbook.

Locally scoped named ranges make sense for worksheets that use named ranges for local assumptions only. For example, perhaps you have a workbook with monthly tracking sheets (one per month) that use named ranges with the same name, all scoped locally. The local scope allows the names in each sheet to work correctly without colliding with names in the other sheets.

Managing named range scope

By default, new names created with the namebox are global, and you can't edit the scope of a named range after it's created. However, as a workaround, you can delete and recreate a name with the desired scope.

If you want to change several names at once from global to local, sometimes it makes sense to copy the sheet that contains the names. When you duplicate a worksheet that contains named ranges, Excel copies the named ranges to the second sheet, changing the scope to local at the same time. After you have the second sheet with locally scoped names, you can optionally delete the first sheet.

Jan Karel Pieterse and  Charles Williams  have developed a utility called the Name Manager that provides many useful operations for named ranges. You can download the Name Manager utility here .

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Get Training

Quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

• Skip to main content
• Skip to header right navigation
• Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

Define Name in Excel

As you work with data in Excel, you’ll likely find yourself using data in many different categories. Let’s learn how to use Define Name in Microsoft Excel.

As you work with data in Excel, you’ll likely find yourself using data in many different categories. Values exist in ranges, and you may want to identify these ranges by name. Fortunately, that’s quite easy to do in Excel. Let’s learn how to use Define Name in Microsoft Excel .

How to Use Define Name in Excel

Naming ranges in Excel helps streamline your formulas. Instead of selecting the entire range and placing it in the formula, you can simply use the name of your choice. To use Define Name and give your range a name of its own, begin by clicking and dragging your cursor to select the cell range that you want to name. In this example, the range is C1:C5.

Then, find the Formulas tab and click on it. Formulas sits on Excel’s ribbon at the top of your screen. Here, you’ll see a variety of options. The one you’re looking for is the Define Name button near the center.

Click on it, and Excel will open the Define Name menu. Here, you can choose from pre-selected names already found inside your workbook. Or you can type in your own custom name in the box on the right side. Go ahead and type in a name, like Revenue , then click OK. 

Excel will define a name for the selected range. To put it to use, click into an empty cell. Imagine that you want to sum the values in the named range. To do so, you can simply type the following formula:

Hit Enter on your keyboard, and Excel will sum the values. Since the range is named, Excel knows where to look based on the name. You can avoid clicking and dragging to select the range with this technique.

As you can see, Define Name in Excel is a quick and easy way to classify, name, and work with data ranges.

About Andrew Childress

• Ablebits blog
• Excel formulas

How to create and use dynamic named range in Excel

In this tutorial, you will learn how to create a dynamic named range in Excel and how to use it in formulas to have new data included in calculations automatically.

In last week's tutorial, we looked at different ways to define a static named range in Excel . A static name always refers to the same cells, meaning you would have to update the range reference manually whenever you add new or remove existing data.

If you are working with a continuously changing data set, you may want to make your named range dynamic so that it automatically expands to accommodate newly added entries or contracts to exclude removed data. Further on in this tutorial, you will find detailed step-by-step guidance on how to do this.

How to create a dynamic named range in Excel

For starters, let's build a dynamic named range consisting of a single column and a variable number of rows. To have it done, perform these steps:

• On the Formula tab, in the Defined Names group, click Define Name . Or, press Ctrl + F3 to open the Excel Name Manger , and click the New… button.
• In the Name box, type the name for your dynamic range.
• In the Scope dropdown, set the name's scope . Workbook (default) is recommended in most cases.
• In the Refers to box, enter either OFFSET COUNTA or INDEX COUNTA formula.
• Click OK. Done!

OFFSET formula to define an Excel dynamic named range

The generic formula to make a dynamic named range in Excel is as follows:

• first_cell - the first item to be included in the named range, for example $A$2.
• column - an absolute reference to the column like $A:$A.

At the core of this formula, you use the COUNTA function to get the number of non-blank cells in the column of interest. That number goes directly to the height argument of the OFFSET(reference, rows, cols, [height], [width]) function telling it how many rows to return.

Beyond that, it's an ordinary Offset formula, where:

• reference is the starting point from which you base the offset (first_cell).
• rows and cols are both 0, since there are no columns or rows to offset.
• width is equal to 1 column.

For example, to build a dynamic named range for column A in Sheet3, beginning in cell A2, we use this formula:

=OFFSET(Sheet3!$A$2, 0, 0, COUNTA(Sheet3!$A:$A), 1)

Note. If you are defining a dynamic range in the current worksheet, you do not need to include the sheet name in the references, Excel will do it for you automatically. If you are building a range for some other sheet, prefix the cell or range reference with the sheet's name followed by the exclamation point (like in the formula example above).

INDEX formula to make a dynamic named range in Excel

Another way to create an Excel dynamic range is using COUNTA in combination with the INDEX function.

This formula consists of two parts:

• On the left side of the range operator (:), you put the hard-coded starting reference like $A$2.
• On the right side, you use the INDEX(array, row_num, [column_num]) function to figure out the ending reference. Here, you supply the entire column A for the array and use COUNTA to get the row number (i.e. the number of non-entry cells in column A).

For our sample dataset (please see the screenshot above), the formula goes as follows:

=$A$2:INDEX($A:$A, COUNTA($A:$A))

Since there are 5 non-blank cells in column A, including a column header, COUNTA returns 5. Consequently, INDEX returns $A$5, which is the last used cell in column A (usually an Index formula returns a value, but the reference operator forces it to return a reference). And because we have set $A$2 as the starting point, the final result of the formula is the range $A$2:$A$5.

To test the newly created dynamic range, you can have COUNTA fetch the items count:

Note. The two formulas discussed above produce the same result, however there is a difference in performance you should be aware of. OFFSET is a volatile function that recalculates with every change to a sheet. On powerful modern machines and reasonably sized data sets, this should not be a problem. On low-capacity machines and large data sets, this may slow down your Excel. In that case, you'd better use the INDEX formula to create a dynamic named range.

How to make two-dimensional dynamic range in Excel

To build a two-dimensional named range, where not only the number of rows but also the number of columns is dynamic, use the following modification of the INDEX COUNTA formula:

In this formula, you have two COUNTA functions to get the last non-empty row and last non-empty column ( row_num and column_num arguments of the INDEX function, respectively). In the array argument, you feed the entire worksheet (1048576 rows in Excel 2016 - 2007; 65535 rows in Excel 2003 and lower).

And now, let's define one more dynamic range for our data set: the range named sales that includes sales figures for 3 months (Jan to Mar) and adjusts automatically as you add new items (rows) or months (columns) to the table.

With the sales data beginning in column B, row 2, the formula takes the following shape:

To make sure your dynamic range works as it is supposed to, enter the following formulas somewhere on the sheet:

=SUM(sales)

=SUM(B2:D5)

How to use dynamic named ranges in Excel formulas

In the previous sections of this tutorial, you have already seen a couple of simple formulas that use dynamic ranges. Now, let's try to come up with something more meaningful that shows the real value of an Excel dynamic named range.

For this example, we are going to take the classic INDEX MATCH formula that performs Vlookup in Excel:

…and see how we can make the formula even more powerful with the use of dynamic named ranges.

As shown in the screenshot above, we are attempting to build a dashboard, where the user enters an item name in H1 and gets the total sales for that item in H2. Our sample table created for demonstration purposes contains only 4 items, but in your real-life sheets there can be hundreds and even thousands of rows. Furthermore, new items can be added on a daily basis, so using references is not an option, because you'd have to update the formula over and over again. I'm too lazy for that! :)

To force the formula to expand automatically, we are going to define 3 names: 2 dynamic ranges, and 1 static named cell :

Lookup_range: =$A$2:INDEX($A:$A, COUNTA($A:$A))

Return_range: =$E$2:INDEX($E:$E, COUNTA($E:$E))

Lookup_value: =$H$1

Note. Excel will add the name of the current sheet to all references, so before creating the names be sure to open the sheet with your source data.

The completed formula looks as follows:

=INDEX(Return_range, MATCH(Lookup_value, Lookup_range, 0))

As soon as you add new records to the table, they will be included in your calculations at once, without you having to make a single change to the formula! And if you ever need to port the formula to another Excel file, simply create the same names in the destination workbook, copy/paste the formula, and get it working immediately.

Tip. Apart from making formulas more durable, dynamic ranges come in handy for creating dynamic dropdown lists .

You may also be interested in

• Excel names and named ranges
• Basic Excel formulas & functions with examples
• Microsoft Excel formulas with examples

Table of contents

48 comments

Hi - Not sure if this is relevant -- I want to increment a Cell index number -- eg Cell referenced in sheet -- =Sheet1!Findx iow how can I modify indx if indx=5 thus F5, how can I modify it with formula to eg F6

use-case - I have on a spreadsheet a integer in a Cell eg C5 = integer 10 What I want to do -- Extract a value from the Sheet1 -- =Sheet1!(Findx) where the indx will now be the value of integer in C5 iow =Sheet1!F10

How Can I modify the formula such that value retrieved will be =Sheet1F10+offsett so that if offset is 5 -- The value in Sheet1 @ F15 (10+5) will be retrieved

Hi! Your explanation is not very clear. However, you can create a cell reference based on the cell value using the INDIRECT function. Here you can find detailed explanations and examples: How to use INDIRECT function in Excel - formula examples .

Hello. Is it possible to store the values of a dynamic array in a named range already calculated? For example, when I enter this formula for a new named range, the name manager shows the values as an ellipsis:

=ABS(INDEX(A$1:A$1000,0,1))

I'm assuming this means Excel will recalculate that formula each time the name is referenced. Is there a way to avoid this without first having to create a column with the absolute values already calculated, and then pointing the new name to that column?

Hi! If I understand the question correctly, such a formula can work:

=ABS(namedrange) or =ABS(INDEX(namedrange,0,1))

I'm trying to add a formula in the cell following a sequence formula that is dynamic. The sequence formula can autofill in 6 cells (I want to add the formula in the 7th cell) of the sequence formula can autofill in 8 cells (I want to add the formula in the 9th cell). Is there a way to do that automatically?

Hi! You cannot automatically add a formula to a cell.

I'm trying to do a graph to display the last 6 days worth of data using OFFSET. The formula works if I have 6 or more days in my table, but I'm receiving reference errors if I have less than 5 days. E9=01Jan2023 F9=02Jan2023 G9=03Jan2023 H9=04Jan2023 .....

this is my formula. =OFFSET($E$9,,IF(COUNTA($E$9:$GA$9)<6,0,COUNTA($E$9:$GA$9)-6),,IF(COUNTA($E$9:$GA$9)<6,COUNTA($E$9:$GA$9),6))

would you be able to identify where my problem lies or how I can display the graph with only 1, 2, 3, 4 or 5 days worth of data without error message?

I tested your formula with three dates in cells E9:G9 and got no error.

I have production report with two columns one column for dates and second column is for case number so I need result how many case number completes for the date. Example. A column B column 03/13/23 AS123 03/13/23 AS234 03/14/23 AS768

I need countifs formula for with match date on second sheet.

Hi! For the answer to your question and examples of formulas, see here: Using Excel COUNTIF function with dates .

Image attached is the best way to understand what the formula supposed to achieve and the problem I am trying to resolve. However, I will try to explain the requirement which the calculation is trying to satisfy.

Background:

Business Entity (Corporation, such as Amazon) - US Tax Code allow a business to carryforward Operating Loss of a business entity to be used to OFFSET income in future years, until the loss is fully used. Depending on the income in future years, previous year(s) loss can be completely utilized in "One Single Year", or spread across multiple years (as shown in Row 33 - Year = 2017)

As shown in the table below, the first three columns capture the details such as NOL Generated Amount, Used, or Expired for the Year of NOL Generated which is mentioned in column "Year". Column H calculate how much of the income from the Year (mentioned in Year column) is used in "Current Year" (which in this example is 2022 - bottom row).

Now, based on the above-mentioned facts so far, the calculation is straightforward, where Total Available column give me the amount of NOL that can be used for Current Year, and the amount utilized needs to be deducted from the Income as calculation progress through subsequent years.

** Here comes the Wrinkle and hence my problem, because of Tax Rule Changes:

Post 2017, there is difference between how the NOL is treated (or for that matter, how much of it can be used). NOLs generated prior to 2018 (Year <2018 in column "Year") vs NOLs generated AFTER 2017.

Tax code now limit the amount of NOL taxpayer can use if it was generated after 2017, and the way it does is taking 80% of the Taxable Income left after deducting total Pre-2018 NOL from the Taxable Income.

Example: Taxable Income = $1,000,000 Total Pre-2018 NOL (Sum of Column H for Years 2001 through 2017) = $400,000

Taxable Income AFTER Pre-2018 NOL = $600,000 NOL Limitation %= 80% Taxable Income Limit for Post-2017 NOL = $480,000

Total Post-2017 NOL (Sum of Column H for Years 2018 through 2021) = $700,000

Taxable Income after NOL = $120,000 --As you can see, Total Post-2017 NOL = $700,000 and prior to change in tax rules, entire income would have been OFFSET and still $100,000 worth of loss was left which can be carried forward into the future, but due to tax rule change, taxpayer can use ONLY $480,000 of NOL from $700,000.

** In the formula I am working on, I need to incorporate above mentioned calculation based on the Year NOL is generated in a "GENERIC" formula, so that I do not have to manually adjust the worksheet every year into the future.

The highlighted cell reference in the expression shown in the image needs to be generated dynamically based on the value in Year column, so that when the formula is dragged down and reaches rows corresponding to Year = 2018, then first part of the SUM range is set as absolute cell reference and the second part is same for 2018 and increases for each subsequent year.

Not able to insert image; hence, manually providing the formulas:

Columns = Year | Total NOL Generated | Previously Utilized | Expired | Total NOL Available | Current Year Utilization | Carryforward | Expiration

Rows: F12 = Income before NOL G17 = First Row [Total Available]

Row 1 (2001) = MAX(0,MIN(F12,G17))

Row 2 through 17 (2002 - 2017) = IF(C18<2018,MAX(0,MIN(G18,$F$12-SUM($H$17:H17)))

Row 18 (2018) = IF(C35=2018,MAX(0,MIN(G35,(($F$12-SUM($H$17:$H$33))*$J$14)))

Row 19 - 21 (2019 - 2021) = MAX(0,MIN(G35,(($F$12-SUM($H$17:$H$33))*$J$14)-SUM($H$34:H34)))))

**Row 2 through 21 have a generic formula which is combination of all the individual expressions mentioned above. Above the expression is broken down to illustrate which part of the expression condition apply to which rows in the NOL schedule.

The problem is with expression "SUM($H$34:H34)" because as the formula is dragged down from first row with Year = 2018 upwards, the formula falls apart. At this point, I have to manually manage the formula based on where the Year = 2018, and will have to do the same next year, unless the cell reference can be generated dynamically based on value in Year column.

Any suggestion?

Hi! Try creating a dynamic reference using the INDIRECT function. You can find the examples and detailed instructions here: Excel INDIRECT function - basic uses and formula examples . I hope it’ll be helpful.

Good day, I need some assistance please...I have an excel sheet with Dynamic columns. I display the total Working hours in columnD and only display the overtime hours if there are any. If there isn't any overtime hours then I display the week in days starting from either column E or columnG. I need to sort the days of the week according to a date range which I have just specified. So, If my Monday falls on the 19th and my Thursday falls on the 13th, I need to sort the columns so that Thursday comes first and Wednesday comes last. I don't know the column letter (could be D or E or F or G or so on) but I can determine the letter. I don't want to use the letter but when I substitute, I get an error. When I run the hardcoded substitution, it works fine "Sheets(sheetName).Range("I2:O132").Sort Key1:=Range("I2:O2"), Order1:=xlAscending, Orientation:=xlLeftToRight". When I use the following, I get an error Sheets(sheetName).Range(("""" & strColumnStart & "" & ":" & "" & strColumnEnd & """")).Sort Key1:=Range("""" & strColumnStart & "" & ":" & "" & strColumn & """"), Order1:=xlAscending, Orientation:=xlLeftToRight. When I use the immediate window, """" & strColumnStart & "" & ":" & "" & strColumnEnd & """" equates to "I2:O132" but it just doesn't work. Does anyone have a solution to this?

Hi! Unfortunately, we can only help with writing Excel formulas. Your problem cannot be solved with formulas.

How to Create a name manager if the is many blank cells in between cells

=SUMPRODUCT(((Airport!$B$5:$B$2000)=$A$6)*((Airport!$C$5:$C$2000)=C2),(Airport!D5:D2000)) works perfect so added your formula to create a dynamic range. =SUMPRODUCT(((Mon_Col2)=$A$6)*((Airport!$C$5:$C$2000)=C2),(Airport!D5:D2000)) when you step in and evaluate the formula it does return the correct dynamic range. However it gives me a #N/A error when finished.

Hello! I can't see your data, but I can assume that your dynamic ranges are different sizes.

HI, I have created a table using dynamic range. I would like to format(meaning color/borders) like a when I create a table with alternating fill colors for row. Is there a simple method? The button Labeled "Format as Table" creates a table them my Dynamic range goes to Spill Error...

Thanks, Jmarc

Hello! You cannot create an Excel table from a named range. You need to convert formulas to values . Or use standard Excel formatting tools. Here is the article that may be helpful to you: Highlight every other row in Excel using conditional formatting .

Here's a weird one for dynamic range defined in Formulas>Name Manager (Excel MAc v16.59)... Trying to define/name a dynamic range $L$1:$M$M from Parameters! Tab using: =OFFSET(Parameters!$L$1,0,0,COUNTA(Parameters!$M:$M),2)

1) Type that into any cell in the workbook (even other Tabs) and seems to return exactly whats desired... 2) Type that into the Name Manager to assign a name to this dynamic range and get nothing returned... no error message, just an empty set of data.

Any ideas or possibly is there a bug in the Excel Mac? Thanks for any response...

Hello! Unfortunately, I was unable to reproduce your situation on Excel for Mac. I pasted your formula into the "Refers to" field and got a working named range. Check if you are doing everything correctly in accordance with the instructions described in the article above.

I'm having trouble entering the formula for a dynamic range. =Sheet1!ADDRESS(MATCH(TODAY():$P:$P,0),16):INDEX($P:$P, COUNTA($P:$P)) or =Sheet1!ADDRESS(MATCH(TODAY(),P10:P374,0),16):$P$374 Keeps giving me a message about am I trying to enter a formula, must start with= or -. When I enter only the cell formula, it takes it, but I need a range. =Sheet1!ADDRESS(MATCH(TODAY():$P:$P,0),16) Works to give the cell address of today's date. This creates dynamic range starting at today's date in column P and I want to extend it to the end of the data in column P. Column P is a list of dates. I need this to then find the next value in a different column after the today's date row. This different than most dynamic ranges that only extend the bottom. I want to do both. One to move the top based on today's date and the bottom to extend it based on data being added. I hope you can help me. I've learned a lot from you. Thanks, Colt

Hello! If your list of dates starts in cell A1, then you can use the formula to create a dynamic range starting from the current date:

=OFFSET(A1,MATCH(TODAY(),A1:A300,0)-1,0, COUNTA(A1:A300)-MATCH(TODAY(),A1:A300,0)+1,1)

This should solve your task.

Hi, How can I get the Defined Name to work for a dynamic range when I have both Blank and Non-Blank cells within my range?

Hello! Try using the COUNTBLANK function in your dynamic range formula:

OFFSET(first_cell, 0, 0, COUNTA(column)+COUNTBLANK(column), 1)

I hope it’ll be helpful.

I don't think this will work. The COUNTBLANK function will also count all of the blanks below the last item in the column giving you a very large value for modern versions of Excel.

Hi! Of course, you can use a range of cells instead of the entire column in the formula.

For example - Instead of COUNTA(column) use COUNTA(A1:A100)

This will seriously speed up the calculations.

Each day I copy a watchlist of share prices from Yahoo Finance to Excel. By placing the latest end-of day closing share prices in the right-most column of a table, all the previous entries move one column to the left. (Excel does that for me). Every day's close-price of each company, from start date to the present, is listed in a row. The column holding the start date, is moving leftwards into another column, day by day.

I make charts of each company. The chart includes the Start date, (when I first started taking the company's data). The chart records up to the present day.

I know where is each start date. By using Index and Match I can find exactly in which column is the Start date. I can list the column reference in a cell:- e.g in cell Q1 is listed, column "CME".

Today, the column holding my start date for the company, RCP.L, is CME. Yesterday, before I took the day's figures, it was CMF.

I now want an automatic facility (formula) to enter CME14:CME770 into an INDEX & MATCH formula (below) which, yesterday was reading CMF14:CMF770:-

So the Index Match formula for yesterday is:- INDEX(Close!CMF$14:CMF$770,MATCH($Q$1,Close!$ATK$14:$ATK$770,0)) It reads yesterday's range, not today's.

($SQ$S1 reads the cell containing "RCP.L" to find the row in the column range CMF14:CMF770.)

Today I want the INDEX Match formula to be INDEX(Close!CME$14:CME$770,MATCH($Q$1,Close!$ATK$14:$ATK$770,0))

Tomorrow I will want CMD14:CMD770 in the formula and so-on.

I've done masses of reading and completely stuck. Can you please help? Regards, Philip.

Hello! How do I create a Dynamic Named Range for unlocked ranges by a password when sheet is protected.

How do I create a Dynamic Named Range for unlocked range by a password?

I would like to add numbers in a column using sum, offset, counta and the column has a name. For practice, I have column E and beginning with row 3 the title: salary. Rows 4, 5, 6,etc are the salaries. Column E3:E10 has been named as "monthly_salaries". I need to use that so I can place the sum(monthly_salaries) function on any worksheet, while the monthly-salaries will be on a seperate worksheet. Each month the length of this vertical column E changes as more or less people draw salaries. Also, I use different worksheets so data, like the salaries may be located on worksheet 2 and the actual sum on worksheet 1. I have offset(e3,0,0,counta(E:E),1. Question: How to combine the column name with the sum function to accommodate the dynamic nature of this problem. Thank you for your assistance. Chris

Hello! Create a dynamic named range “monthly_salaries” using a formula

=OFFSET($E$3, 0, 0, COUNTA($E$3:$E$10), 1)

Use it like this:

=SUM(monthly_salaries)

I hope I answered your question.

Dynamic Named Ranges don't seem to like Dynamic Array Formulas it seems. I have 2 Dynamic Named Ranges, say LIST1 and LIST2. In a separate column, I have entered the formula: FILTER(LIST1, LIST2="an existing value"). This returns an error (#VALUE!). It worked correctly when my named ranges were not dynamic. Is there an evident reason why I am getting this error?

Thanks in advance! Sabrina

Hello! I was unable to repeat your errors. I don't have your data. Please have a look at this article — FILTER function not working . I hope my advice will help you solve your task.

Hi, How do i use Dynamic Named Range in "Data Rane" Excel chart ? =$B$2:INDEX($1:$1048576,COUNTA($B:$B),COUNTA($2:$2)) or =$A$2:INDEX($A:$A, COUNTA($A:$A)) is not suitable for this. Thank You, Ofer

hi there.. hope someone could help me. I want to create a name in my file which is getting values from a single column table. I want to exclude one specific value and don't want it to be part of the name range. Not sure how can I do that? Column data is: Fruits (table header) Apple Banana Orange Grapes Want to have a list that exclude "Orange". As it's a table I will be adding new names after Grapes in near future.

Hello, I am painfully new to working with Excel, macros, and the like. I have recorded a macro, but right now it will only work on the cell range that I originally recorded the macro on. Right now the range reads as =Range("A2:A724"), but I need the bottom part of the range (A724) to be dependent on column B. Meaning, if column B has data going down to cell B1021, then I want my range in column A to automatically look like =Range("A2:A1021"). I would appreciate any help. Thanks!

Hi! I'm a newbie in Excel, so there's a risk my question is kind os obvious. I'm trying to connect a Pivot Table in Excel to a Word File, for this, I have to edit the Name of my range in Excel and Word so the Word table automatically updates the range it is reporting. The problem I have is Excel says he doesn't understand my formula, it looks like this:

='DE40'!$B$4:$B$4:INDEX('DE40'!$1:$1048576,COUNTA('DE40'!$B:$B),+'DE40'!$4:$4))

Where DE40 is the Sheet/Pivot Table I'm trying to refer. Any ideas?

I have a simple table: 7 columns, 4 rows. Can I create a formula just adding Hrs of bananas?

Jan 1 Jan 2 Jan 3 Hrs Cases Hrs Cases Hrs Cases Bananas 1 10 3 30 0 0 Apples 5 20 1 4 3 12

_______|___Hrs_|_Cases_|_Hrs_|_Cases_|_Hrs_|_Cases Bananas|____1__|___10__|__3__|___30___|_0__|___0__ Apples_|____5__|___20__|__1__|___04___|_3__|___12_

No sure if this will help to understand the table...

An easy way to do it if you need a range is to use indirect in the name.. eg example for A2:G10 There is 20 rows in coloumn A

=Indirect(Sheet_name!A2:G"&CountA(A:A))

since indirect "translate" the expression - so it reads the name to be refered to this Sheet_name!A2:G10

Correction - There is 20 rows in coloumn A - should ofcourse be 10 rows ;)

I'm trying to create a dynamic range showing client names only if their status is Active in another column. Can someone help me with the formula for this?

This index formula method for creating dynamic named range is not working.

Sheet3!$H$2:INDEX(Sheet3!$H:$H,COUNTA(Sheet3!$H:$H))

Using evaluate formula, this shows to result to $H$2:$H$6. But post that it acts as an array formula. Its finally returning value either H2/H3/H4/H5/H6 based on the cell. What i mean to say is, it is not giving a range. Hence, this seems wrong. Please let me know your view.

Hi How do you create a Dynamic named range (with INDEX) but without the sheet name automatically added after the Define Name editor is closed? Tnks

THANK YOU FOR ENLIGHTENING ME AM SO DAMN GRATEFUL FOR THIS WRITE UP

How do I create a Dynamic Named Range with worksheets instead of rows or columns?

I have a workbook that I add a new worksheet to every week and I want to track my vacation time over the course of the calendar year.

Thank you for your help.

Post a comment

Rename an Excel table

Each time you create a table, Excel assigns a default name to the table according to this naming convention: Table1, Table2, Table3, and so on. To make it easier to refer to a table, for example, in a workbook that contains many tables, you can name each table.

Note:  The screen shots in this article were taken in Excel 2016. Your view may differ slightly if you have a different version of Excel, but the functionality is the same (unless otherwise noted).

To rename a table:

Click on the table.

Go to Table Tools > Design > Properties > Table Name .

On a Mac, go to the Table tab > Table Name .

Highlight the table name and enter a new name.

All of your tables will be shown in the Address bar, which appears to the left of the Formula bar. When you select any table from the list, Excel will automatically jump to that table—even if it's on another worksheet.

Important notes for names

Use valid characters   —  Always begin a name with a letter, an underscore character (_), or a backslash (\). Use letters, numbers, periods, and underscore characters for the rest of the name.

Exceptions: You can’t use "C", "c", "R", or "r" for the name, because they’re already designated as a shortcut for selecting the column or row for the active cell when you enter them in the Name or Go To box.

Don’t use cell references —  Names can’t be the same as a cell reference, such as Z$100 or R1C1.

Don’t use a space to separate words —  Spaces cannot be used in the name. Consider how you can write the name using no spaces. Or, use an underscore character (_) or a period (.) as word separators. Examples: DeptSales, Sales_Tax or First.Quarter.

Maximum 255 characters — A table name can have up to 255 characters.

Use unique table names —  Duplicate names aren’t allowed. Excel doesn’t distinguish between upper and lowercase characters in names, so if you enter “Sales” but already have another name called “SALES" in the same workbook, you’ll be prompted to choose a unique name.

Rename a table in Excel for the web

Note:  This is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Microsoft 365 subscribers .

On the Table Design tab, double-click the Table Name , and then enter a new name.

Need more help?

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

Overview of Excel tables

Video: Create an Excel table

Total the data in an Excel table

Resize a table by adding or removing rows and columns

Excel table compatibility issues

Export an Excel table to SharePoint

Convert an Excel table to a range

Filter data in a range or table

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

• Search the community and support articles
• Microsoft 365 and Office
• Search Community member

Ask a new question

Change a cell's Defined name in Excel2016 MAC

I find two cells have gotten an incorrect DEFINE NAME - I cannot locate where to "change the defined name of a cell previously named" -

I find where to add new names, where to delete old names - but nothing on changing the name of a cell to something other than what it was previously assigned.  Curious - Should be able to change to the last assignment which it does not.  I highlight the cell, choose DEFINE NAME, and either type in the intended name, or choose it from the list - either way, the cell does not change to the newer choice.

I now will delete the "old name" and rebuild these cells - it is annoying to not be able to simply rename cells.

Report abuse

1 person found this reply helpful

Was this reply helpful? Yes No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

Thanks for your feedback.

Replies (3) 

Question info.

• Norsk Bokmål
• Ελληνικά
• Русский
• עברית
• العربية
• ไทย
• 한국어
• 中文(简体)
• 中文(繁體)
• 日本語

How to Make Excel Change Scope of Named Range

by Ilker | Jan 10, 2020 | Excel Tips & Tricks

Named ranges have a property named "scope". Usually, you do not need to adjust this property to use named ranges in Excel. On the other hand, it can become very useful if you have a number of sheets that are identical. The scope concept allows you to use the same name in different worksheets. In this guide, we're going to show you how to make Excel change scope of named range across the workbooks and worksheets, with examples.

Download-Workbook

Let's assume that you have workbook that stores multiple sheets containing data from different years, and you want to use the same formulas for all. What if the "Amount" cell is on B2 in one worksheet and on C25 in another? You do not need to update your formulas for each worksheet, and use the same name (of a named range) for different cells in every worksheet, if you use worksheet scopes.

When the scope is changed from workbook to a worksheet, the named range becomes an element of the worksheet. Other sheets do not recognize the worksheet-scoped named range. Thus, you can name different cells with the same name in every sheet.

Let's see how to make Excel change scope of named range to something different than "Workbook":

• Select the cell or range you want to name
• Click on Define Name in Formula tab of the toolbar
• Give it a name
• Change the Scope to a worksheet and save

In our example, we have 4 named ranges with 2 duplicate names. As you can see in the screenshot, each "Lookup_Value" named range refer to different cells.

This flexibility allows using the same formula in all sheets, even with named ranges referring to different locations.

=VLOOKUP(Lookup_Value,PokemonTable,2,0)

Worksheet "Generation I":

Worksheet "Generation II":

A small tip

As we mentioned before in the article, you cannot directly use a worksheet-scoped named range from another worksheet. On the other hand, you can use them by placing the worksheet name before the name, just like a regular reference.

These article summarizes scope concept of named ranges in Excel. If you want to learn more about named ranges, please see our complete guide: Excel Named Ranges .

Related posts

Recent Posts

• Translating Templates into Another Language on SpreadsheetWeb Hub
• Unlocking a World of Possibilities: Over 100 Templates Across Diverse Subjects and Industries
• Unlocking the Power of Spreadsheets with SpreadsheetWeb
• Boosting Sales Teams: Custom Web Apps with SpreadsheetWeb
• What's New in SpreadsheetWeb Hub: October 2023

How to Delete Defined Name in Excel? (Name Manager + VBA)

We define and use names in Excel formulas to easily understand, reference, and maintain data.

For example, we can define a name for a table, a cell range, a constant, or a function. 

Too many defined names in your workbook consume a  lot of working memory and may slow down your workbook.

Therefore, you must delete some defined names to improve the workbook’s performance. 

This tutorial shows three techniques for deleting defined names in Excel . 

Note: If a formula in the workbook uses a defined name you want to delete, ensure that you replace it before deleting it; otherwise, the formula will return an error. 

Table of Contents

Method #1: Use the Name Manager Tool

The Name Manager in Excel is a built-in tool that lets us generate, edit, delete and locate all the names used in a workbook. 

We will use the following workbook with three defined names in the Name Box to show how we can use the Name Manager tool to delete a defined name in Excel.

We want to delete the James_Washington_Math_Score defined name because we no longer need it in the worksheet. 

Below are the steps to delete defined names using Name Manager:

• Click the Formulas tab and select the Name Manager option in the Defined Names group. This process opens the Name Manager tool.

Note: You can also open the Name Manager tool using the keyboard shortcut Ctrl + F3 .

• In the Name Manager window, select James_Washington_Math_Score in the list of defined names and click the Delete button. Please note that if you want to delete multiple names at once, you can select them by holding down the Ctrl key and clicking on them one after the other. 

A message box appears asking you whether you are sure you want to delete the name James_Washington_Math_Score .

• Click OK on the warning message box that appears.

The name is removed from the list of names in the Name Manager window.

• Click the Close button to close the Name Manager. 

If you check the Name box, you see that James_Washington_Math_Scores no longer appears.

Filtering for and Deleting Specific Defined Names

Sometimes, your workbook may have too many names to select individually.

That is where the filter option in the Name Manager comes in handy to filter for specific types of names before deleting them. 

For instance, if you want to remove only the defined names scoped to the active worksheet, you do the following:

• Press Ctrl + F3 to open the Name Manager.
• Click the Filter button and select Names Scoped to Worksheet .

Only those names scoped to the worksheet will appear in the Name Manager window, making selecting and deleting them easy.

Deleting Names in a Protected Worksheet

Sometimes when you open the Name Manager tool, you may find that the Delete button is greyed out as follows:

This happens when the worksheet is protected. You cannot delete names in a protected worksheet. 

You will need to unprotect the worksheet before deleting the names. 

Click the Review tab and select Unprotect Sheet option in the Protect group. You may be required to supply the password used to protect the worksheet. 

Method #2: Filter For Delete Defined Names Using Excel VBA

As helpful as the Filter option in the Name Manager tool is in filtering and deleting names, sometimes it can become tedious, especially when dealing with too many names. You can simplify the process by using the VBA code.

We use the following workbook with three defined names to show how we can use Excel VBA to filter for and delete specific names. 

We want to filter for and delete only those defined names that contain the word Student.

We use the following steps:

• Click the Developer tab and click Visual Basic in the Code group. This step opens the Visual Basic Editor .

• Click Module on the Insert Menu to create a new module in the Visual Basic Editor.

• Type the following sub-procedure in the module:
• Save the sub-procedure and save the workbook as a Macro-Enabled Workbook .
• Click inside the sub-procedure and press F5 to execute the code.
• Click the View Microsoft Excel button on the toolbar to switch to the active workbook containing the dataset. You can also use the keyboard shortcut Alt + F11 .
• Open the Name Box and see that all the defined names that contain the word Student have been deleted.

Method #3: Use Excel VBA to Delete All Defined Names

We may want to delete all defined names from a workbook at once. That‘s when Excel VBA comes in handy .

We will use the following worksheet with four defined names to show how we can use the Excel VBA to delete all defined names from a workbook.

We use  the steps below:

• Click the Developer tab and click Visual Basic in the Code group. The Visual Basic Editor is opened.

Note: You can also open the Visual Basic Editor using the keyboard shortcut Alt + F11 .

• Save the sub-procedure and save the workbook as a Macro-Enabled Workbook . 
• Click inside the procedure and press F5 to run the code.
• Click the View Microsoft Excel button on the toolbar to switch to the active worksheet containing the dataset. 

•  Open the Name Box and see that all the defined names have been deleted.

This tutorial has looked at three techniques for deleting defined names in Excel.

The methods include the application of the Name Manager tool, the Excel VBA to filter for and delete specific names, and Excel VBA to delete all the defined names in a workbook.

We hope that you found the tutorial helpful. 

Other articles you may also like:

• How to Rename a Table in Excel?
• Excel Table vs. Excel Range – What’s the Difference?
• How to Remove Table Formatting in Excel?

Steve Scott

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Names object (Excel)

• 8 contributors

A collection of all the Name objects in the application or workbook.

Each Name object represents a defined name for a range of cells. Names can be either built-in names—such as Database, Print_Area, and Auto_Open—or custom names.

The RefersTo argument must be specified in A1-style notation, including dollar signs ($) where appropriate. For example, if cell A10 is selected on Sheet1 and you define a name by using the RefersTo argument "=sheet1!A1:B1", the new name actually refers to cells A10:B10 (because you specified a relative reference). To specify an absolute reference, use "=sheet1!$A$1:$B$1".

Use the Names property of the Workbook object to return the Names collection. The following example creates a list of all the names in the active workbook, plus the addresses that they refer to.

Use the Add method to create a name and add it to the collection. The following example creates a new name that refers to cells A1:C20 on the worksheet named Sheet1.

Use Names ( index ), where index is the name index number or defined name, to return a single Name object. The following example deletes the name mySortRange from the active workbook.

This example uses a named range as the formula for data validation. This example requires the validation data to be on Sheet 2 in the range A2:A100. This validation data is used to validate data entered on Sheet1 in the range D2:D10.

• Application
• Excel Object Model Reference

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Was this page helpful?

Submit and view feedback for

Additional resources