Have Excel Automatically Create Range Names

A quick way to create range names is to base them on heading cell text (worksheet labels). In the example shown below, the cells representing quarterly sales for all regions will be named based on the labels in columns B through E.

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.

Name Cells or Ranges Based on Worksheet Labels:

• Select the cells that you want to name, including the labels. These can be above, below, left or right of the cells to be named. Here, we are having Excel name the four quarter labels all at once by selecting B4:E8

• Click the Formulas tab on the Ribbon, then Create from Selection in the Defined
  Names group.

• Excel will automatically place a check mark for the location of the labels; assumes you want to use the first cell as the name (in this case Top Row of the selection).
• Click OK to add the range names to the Name box and the Name Manager.

• Click the dropdown arrow on the Name box to see your new range names. Just click one to highlight the included cells (Quarter1 would highlight B5:B8 as B4 is the label and not included in the range).
  
  NOTE: If there are spaces in the labels, they are replaced with an underscore. Quarter 1 would become Quarter_1.
  

Auto apply range names for the row headers by selecting the text in Column A through the numbers in Row 8 – A5:E8 (don’t include totals):

Repeat Steps 2, 3 and 4. Note that the check box is now Left row. Click OK.

Display range names from the Name Box:

Notice that Excel alphabetizes the list no matter what the order of creation.

Before we put those names into action, we probably will want to know totals for all the eastern regions and all the western ranges at some point, so we’ll create range names for them. Also, we’ll abbreviate the individual regional names so they are much easier to use in our formulas:

Since we are naming the range, we only select the numerical cells:

• Select B5:E6 (all quarterly sales for the two eastern regions)
• Click in the Name box and type East
• Press ENTER

Do the same for both the western regions:

• Select B7:E8
• Click in Name box and type West
• Press ENTER

East and West names are added to the Name box list and the Name Manager.

Now, add a section below your existing data to capture total and average sales for the two regions:

We could call it done and begin using the created names in formulas but the regional names are too long, so for efficiency, let’s change them to just two letters.

Change a Named Range

After you create a named range, you might need to adjust the referred to cell references or, in our case, abbreviate the name to make it easier to use in a formula. Here’s the steps:

• Click the Formulas tab, and in the Defined Names group, Click Name Manager icon
• Click on the name that you want to change In the list (in this case Northeast)

• Click the Edit… button or double click the name to display Edit dialog box
• The name field shows the name highlighted
• Type NE in the box to replace it
• Click OK

1. You can now choose the other three long regional names and replace with NW, SE and SW using Steps 3 through 6
2. When completed, click the Close button in the Name Manager

Now we’re ready to rock and roll using them in our formulas!

Use Excel Names in Formulas

The real fun begins! We saw in earlier posts how to use range names for navigation or selection – very handy but here’s the real power. We want to find the totals for eastern and western regions for the four quarters. Because we made separate range names for the east and west regions, we can create formulas for the northern or southern regions and/or east and west like this:

• In B13, type: =sum(ne,se) ENTER   (Range names are not case sensitive so can be typed lower case and Excel will convert)

Note: Another advantage of range names is that they are added to the auto display list along with function formula names and you don’t have to remember any cell references!

Next, we’ll simplify even more using the east/west names:

• In B14, type: =sum(east) ENTER

Presto! There’s the total for the Northeast and Southeast regions.

For the average section, formulas are the same, changing the function:

• In B17, type: =average(east) ENTER (or you could type: =average(ne,se) ENTER
• In B18, rinse and repeat with (west) for final results!

TIP: If you need to change the cell references for a range name, open the Name Manager; select the name, and edit the contents of the Refers To box, or highlight the new range on the worksheet with the mouse, and Excel will edit for you. Click the check mark to save the change and close the Name Manager. No need to retype anything.

Your names can be created from references on one worksheet and the formulas used on another because their scope is available across the workbook. I’m pretty smitten with named ranges myself. What do you think? Do you see lots of uses for using this feature? Let me know in the Comments. Thanks!

For more information on Range Names, see:  https://gaylelarson.com/quick-navigation-excel-worksheet/ and https://gaylelarson.com/excel-name-box-for-navigation/