Create Excel names that refer to cells, a constant value, or a formula. Use names in formulas, or quickly select a named range.
In Excel, you can create names that refer to:
After you define Excel names, you can:
The instructions below show how to create names and use names in your Excel files. Get the sample Excel workbook, to follow along with the instructions.
NOTE: To create a quick list of all the names in a workbook, see the Quick List of Names - No Macro instructions.
Watch this short video to see how to name a group of cells. Then, go to that named group of cells, or use the name in a formula. The written instructions are below the video. TOP
You quickly name the selected cells by typing in the Name Box. NOTE: There are a few rules for Excel names
There are rules for Excel names on the Microsoft site, and those are summarized below.
For an in-depth look at what characters are allowed, see Martin Trummer's GitHub project excel-names -- it has written examples, and an Excel file to download.
Follow these rules from Microsoft, when you're creating a name in Excel.
NOTE: To create a quick list of all the names in a workbook, see the Quick List of Names - No Macro instructions.
To see some of the named ranges on a worksheet, use this trick:
The names of some ranges will appear on the worksheet, in blue text, like the MonthList in this screen shot.
After you create a named range, you might need to change the cells that it refers to. This short video shows the steps, and there are written steps below the video.
Follow these steps to change the range reference:
To quickly name individual cells, or individual ranges, you can use heading cell text as the names. Watch this video to see the steps. Written instructions are below the video.
A quick way to create names is to base them on heading cell text (worksheet labels). In the example shown below, the cells in column E will be named, based on the labels in column D.
NOTE: If the labels contains spaces, those are replaced with an underscore. Other invalid characters, such as & and # will be removed, or replaced by an underscore character.
To name cells, or ranges, based on worksheet labels:
Most Excel names refer to ranges on the worksheet, but names can also be used to store a value.
For example, create a name to store a percentage amount that you use frequently, such as a retail tax rate:
Then, use that name in formulas, instead of typing in the value
You can also create names to store values that are difficult to enter. For example, some formulas use this strange-looking number. According to Excel specifications on the Microsoft site, that is the largest positive number that you can type into an Excel cell.
Instead of typing that number into your formulas, you could define a name, using that value (copy the number from this page before you create the name):
Then, use the XL_Max name in formulas, like this LOOKUP formula that finds the last number in a column.
=LOOKUP(9.99999999999999E+307, WeightData[Wt])
After creating names, you can use them:
If a name refers to a range, you can select that name in the Name Box dropdown list, to select the named range on the worksheet.
NOTE: If a name does not appear in the drop down list, you can type the name instead
You can also use names in formulas. For example, you could have a group of cells with quantities sold. Name those cells Quantity, then use this formula to calculate the total amount:
=SUM(Quantity)
In addition to using the Name Box to create a named range, or to select a named range, here are a few other Name Box tricks.
In old versions of Excel, the Name Box was a set width, and you couldn't change that. Here's how you can adjust the Name Box width in newer versions:
Another handy trick is that you can use the Name Box to select unnamed cells too. Here are a couple of ways that trick can be useful -- unhide columns, or fill a long range of cells.
Unhide Columns
Here's a quick way to unhide specific columns, and leave others hidden.
Fill Cells
With Excel's AutoFill feature, you can create a list of dates, or numbers, or other sequences, very quickly. Just type one or two values as the starting sequence, select those cells, and double-click the Fill Handle to fill down to the last row of data.
Sometimes though, there's no data in the adjacent column, so AutoFill won't work with a double-click. You could drag the Fill Handle down, but that's not very efficient if you need to create a long series.
Here's how to create a list of 1000 numbers in column A:
If the list that you want to name will change frequently, having items added and removed, you should create a dynamic named range. A dynamic named range will automatically adjust in size, when the list changes. Here are two ways to create a dynamic named range:
The easiest way to create a dynamic named range is to start by creating a named Excel table. Then, define a range based on one or more columns in that table.
In this example there is a list of parts on the worksheet, and a named table, and dynamic named ranges will be created. Later, if you add new items to the table, the named range will automatically expand.
To see the name's definition, follow these steps:
Because the PartIDList named range is based on a named table, the list will automatically adjust in size if you add or remove part IDs in the list.
When you create a named range in Excel, it doesn't automatically include new items. If you plan to add new items to a list, you can use a dynamic formula to define an Excel named range. Then, as new items are added to the list, the named range will automatically expand to include them.
The written instructions are below the video.
If you don't want to use a named table, you can use a dynamic formula to define a named range. As new items are added, the range will automatically expand.
Note: Dynamic named ranges will not appear in the Name Box dropdown list. However, you can type the names in the Name Box, to select that range on the worksheet.
To follow along with the instructions on this page, download the Excel Names Sample File. The zipped file is in xlsx format, and does not contain any macros. TOP
Create Dynamic Ranges With a Macro
Last updated: September 28, 2020 4:28 PM