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.
Create Names from Cell Text
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:
Select the labels and the cells that are to be named. The labels
can be above, below, left or right of the cells to be named. In
this example, the labels are in column B, to the left of the cells
that will be named.
On the Ribbon, click the Formulas tab, then click Create from
In the Create Names From Selection window, add a check mark for the
location of the labels, then click OK. In this example, the labels
are in the left column of the selected cells.
Click on a cell to see its name. In the screen shot below, cell
C4 is selected, and you can see its name in the Name Box -- Full_Name.
The space character was replaced with an underscore.
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
Use Names in Formulas
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:
Create a Dynamic Named Range
If the list that you want to name will change frequently, having
items added and removed, you should create a dynamic named range.
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.
First, create the table:
Select a cell in the parts list
On the Ribbon's Insert tab, click Table
Check that the correct range has been selected, and add a check
mark to My Table Has Headers
Click OK, to create the table.
(optional) Change the table's default name (e.g. Table1) to a meaningful name, such as tblParts
Next, create a dynamic list of part IDs:
Select cells A2:A9, which contain the Part IDs (not the heading)
Click in the Formula Bar, and type a one-word name for the range:
Press the Enter key, to complete the name.
To see the name's definition, follow these steps:
Click the Ribbon's Formulas tab, and
click Name Manager.
There are two named items in the list:
table, with the default name, Table1 (or the name that you gave to the table)
the PartIDList, which is
based on the PartID field in Table1.
Text the Dynamic Range
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.
Add a new item in the list of Part IDs
In the Name Box, select the PartIDList name
The named range is selected, and it includes the new Part ID. TOP
Dynamic Named Range - Formula
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.
Dynamic Named Range Based on Formula
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.
On the Ribbon, click the Formulas tab
Click Define Name
Type a name for the range, e.g. NameList
Leave the Scope set to Workbook.
In the Refers To box, enter an Offset formula that defines the
range size, based on the number of items in the column, e.g.: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
In this example, the list is on Sheet1, starting in cell A1
The arguments used in this Offset function are:
Reference cell: Sheet1!$A$1
Rows to offset: 0
Columns to offset: 0
Number of Rows: COUNTA(Sheet1!$A:$A)
Number of Columns: 1
Note: for a dynamic number of columns, replace the 1