Categories
Excel

Working With Named Ranges in Excel

Microsoft Excel has a feature that enables you to refer to cells by name instead of using cell addresses. Using named ranges will make your spreadsheets easier to understand and it’ll simplify the process of creating formulas.

Here’s what I’m talking about. Take a look at this formula.

=SUM(B3:B6)

Can you tell what the formula does? Do you know what type of data is represented in B3:B6?

You can probably tell that the formula adds a column of numbers… but there’s no way to tell what the numbers represent.

How about this formula?

=SUM(Q1_Sales)

Once again, you can probably tell that we’re adding a column of numbers but, this time, you likely inferred that the numbers represent sales revenue for Q1.

This article is a detailed introduction to using named ranges in your spreadsheets.

I’ve broken the blog post down into 3 parts :

Part 1: Creating Named Ranges
Part 2: Using Named Ranges in Formulas
Part 3: Managing Named Ranges

Let’s get started!

Part 1: Creating Named Ranges

In order to use named ranges in your formulas, first you need to create them.

Keep these rules in mind as you create range names:

  • Names can be up to 255 characters long
  • Names should start with a letter but may contain almost any character except spaces
  • Names should not resemble cell addresses (Q1, R2D2, etc.)
  • Names should not be the same as a function (SUM, COUNT, etc.)
  • Names are not case sensitive

Creating Named Ranges Using the Name Manager

The Name Manager isn’t the fastest way to create a named range but it gets the job done. You can also edit or delete range names in this dialog box, if needed. More on this later!

Using the following spreadsheet as an example, here is how you create a named range, using the Name Manager.

Step #1 – Select cell B7.

Excel worksheet showing a sales of food items by fiscal quarter

Step #2 – Click the Name Manager button in the Formulas > Defined Names group.
The Name Manager dialog box appears.

Name Manager button
Name Manager Button

Step #3 – Click the New button in the Name Manager dialog box.
The New Name dialog box appears.

The New button highlighted in the Name Manager dialog box
Creating a New Named Range

Step #4 – In the New Name dialog box, type Q1_Total beside Name: and verify that =Sheet1!$B$7 appears beside Refers to: then click OK.
The Name Manager dialog box reappears.

The New Name dialog box
New Name Dialog Box

Step #5Q1_Total appears in the Name Manager dialog box, indicating that you successfully created the named range. Click Close.

Name Manager dialog box showing a named range called Q1_Total

Creating Named Ranges Using the Name Box

Using the following spreadsheet as an example, here is how you create a named range using the Name Box.

Step #1 – Select the range B3:E6.

Step #2 – Click in the Name Box, type All_Revenue, then press Enter.

The Name Box

Just a reminder: range names cannot contain spaces. Use an underscore instead  – and don’t forget to press Enter!

Create Multiple Range Names with Create from Selection

If you need to create multiple range names using titles from your worksheet, you can use Create from Selection. Here is the process.

Step #1 – Select the range A3:E6.

Cells A3:E6 are highlighted
Creating Multiple Named Ranges

Step #2 – Click the Create from Selection button in the Formulas > Defined Names group.
The Create Names from Selection dialog box appears.

Create from Selection Button
Create from Selection Button

Step #3 – In the Create Names from Selection dialog box, verify that the Left column checkbox is selected then click OK.

The Create Names from Selection dialog box
Create Names from Selection

Four new named ranges are created: Baked_Goods, Hot_Drinks, Cold_Drinks, and Deli. We’ll talk about validating the range names you’ve created in the next section.

Validating Named Ranges

How do you validate the named ranges you’ve created? Click the down-arrow in the Name Box and select one!

In the screenshot, I selected Deli from the Name Box resulting in the range B6:E6 being highlighted. This tells me that the range was successfully named and the name is associated with the correct cells.

Do this for each range name you want to validate.

The named range Deli is selected in the Name Box resulting in cells B6:E6 being highlight in the worksheet

Part 2: Using Named Ranges in Formulas

The reason we created named ranges in the first place was to be able to use them in formulas. Instead of typing =sum(B3:E3), we want to be able to use =sum(Baked_Goods).

The Baked_Goods named range is used to add cells B3:E3
Using Named Ranges in Formulas

Named ranges will work with any function that accepts a range of cells as an argument. You could calculate an average by typing =AVERAGE(Baked_Goods). Are you ready for some more good news? You don’t even have to type the entire range name. You can use Formula AutoComplete.

Formula AutoComplete

Formula AutoComplete is a feature in Microsoft Excel that simplifies the process of using range names. To calculate an average for baked goods, I would type =AVERAGE( then start typing Baked_Goods. In this example, I only got as far as typing Bak before Excel displayed the Baked_Goods range name as an option.

To utilize AutoComplete, you can either double-click Baked_Goods or press Tab to select it.

An example of Formula AutoComplete
Formula AutoComplete

What if you don’t remember all of the named ranges you created?

There are two options you’re going to find really helpful:

  • Use in Formula
  • Paste List.

Use in Formula

The Use in Formula command lets you select range names from a list. Here’s how calculate the total for Hot Drinks with Use in Formula.

Step #1 – Select cell F4 and type =SUM(.

The SUM function entered in cell F4

Step #2 – Click the Use in Formula button in the Formulas > Defined Names group and select Hot_Drinks.

The Use in Formula button

Step #3 – Press Enter.

The Hot_Drinks named range is used to add cells B4:E4

Paste List

The Paste List command creates an alphabetized list of all of the named ranges in your workbook. This can be useful if you need to examine the range names in use and verify the cells they refer to.

To create a list of range names:

Step #1 – Select an empty cell below your data.

Cell A9 is selected

Step #2 – Click the Use in Formula button in the Formulas > Defined Names group and select Paste Names… from the list (or press F3).
The Paste Name dialog box appears.

The Paste Names command
List of Range Names

Step #3 – Click the Paste List button at the bottom of the Paste Name dialog box.

Paste Name dialog box
Paste Name Dialog Box

Step #4 – An alphabetized listing of all range names is displayed.

A list of Named Ranges has been added to the worksheet
List of Named Ranges

Part 3: Managing Named Ranges

You may need to edit named ranges as you update your spreadsheet or, if you find you’re not using certain range names any longer, you can delete them.

Editing a Named Range

In this example, I’m going to update the All_Revenue range name so that it refers to numbers in the total row instead of the detailed revenue figures in B3:E6.

Step #1 – Click the Name Manager button in the Formulas > Defined Names group.
The Name Manager dialog box appears.

Name Manager Button
Name Manager Button

Step #2 – Verify that All_Revenue is selected in the Name Manager dialog box then drag to select =Sheet1!$B$3:$E$6 in the Refers to: box.

The Name Manager dialog box populated by several named ranges

Step #3 – Drag to select cells B7:E7 in the worksheet.

Worksheet with cells B3:E6 highlighted

Step #4 – Click the green checkmark in the Name Manager dialog box then click Close.

The Name Manager dialog box populated by several named ranges
List of Named Ranges

Deleting a Named Range

Now I’m going to delete several range names.

Step #1 – Click the Name Manager button in the Formulas > Defined Names group.
The Name Manager dialog box appears.

Name Manager button

Step #2 – In the Name Manager dialog box, select Baked_Goods then hold the Shift button and select Hot_Drinks.
Four named ranges are selected.

Multiple named ranges selected in the Name Manager dialog box
Deleting Multiple Named Ranges

The selected range names are deleted.

Clicking the first name and holding Shift as you click an additional name in a series will select multiple contiguous range names. Holding Ctrl as you click allows you to select individual names.

Microsoft Excel is a registered trademark of Microsoft Corporation. XL Basics contains articles and educational content related to Microsoft Excel but is not affiliated with Microsoft in any way.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.