Quickly Create Many Range Names

April 23, 2021 - by Bill Jelen

Challenge: Quickly create many range names in a worksheet. If you decide that complicated formulas would benefit from referring to named ranges instead of cell addresses, you might have a daunting task of individually naming many ranges.

Setup: If your headings are suitable range names, you can use them to quickly create the range names. The commands are slightly different in Excel 2003 and earlier and in Excel 2007.

Solution: In any Excel version, select the data set, including the headings that will be used a range names.

In Excel 2003 and earlier, choose Insert, Name, Create. In the Create Names dialog, choose Top Row and click OK (Figure 74).

Figure 74. Excel 2003 creates five named ranges in this selection.

In Excel 2007, with the data set selected, from the Formulas tab, choose Create from Selection. In the Create Names from Selection dialog, choose Top Row and click OK (Figure 75).

Figure 75. Excel 2007 creates five named ranges in this selection.

In this example, Excel creates five named ranges. The Sales range includes B3:B14. You can now use =SUM(Sales) as a valid formula (Figure 76).

Figure 76. You can create five named ranges using a single command.

Gotcha: If your headings contain a space or other punctuation that is not valid in a named range, Excel substitutes an underscore for each invalid character. If your heading happens to contain a name that is also a valid cell address, Excel appends an underscore to the end of the name. Figure 77 shows several examples in which dashes, spaces, at symbols, and colons are all replaced with underscores when the names are created. Note that the heading I42 in cell G1 generates the range name I42_ to differentiate it from the cell address I42.

Figure 77. Created range names differ from the headings if the headings contained invalid characters.

Additional Details: The following characters are valid in a range name:

Letters A through Z and a through z
Digits 0 through 9
Period, question mark, backslash, underscore
Euro symbol (character 128)
Script f (character 131)
Letters in other alphabets (such as characters 192 – 214, 216 – 246, and 248 – 255)

Additional Details: Formulas that existed before the named ranges were created do not automatically update to use the new named ranges. To retroactively apply a name to formulas, you can use Insert, Name Apply in Excel 2003. In Excel 2007, the Apply command is hidden behind a dropdown at the end of the Define Name command.

Summary: Rather than define range names individually, you can use existing headings to create many names at once.

Title Photo: Toa Heftiba on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.