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!
Excel Courses: We offer Microsoft Excel training delivered online by a live instructor. Participants can connect from anywhere in Canada.
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.
Step #2 – Click the Name Manager button in the Formulas > Defined Names group.
The Name Manager dialog box appears.
Step #3 – Click the New button in the Name Manager dialog box.
The New Name dialog box appears.
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.
Step #5 – Q1_Total appears in the Name Manager dialog box, indicating that you successfully created the named range. Click Close.
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.
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.
Step #2 – Click the Create from Selection button in the Formulas > Defined Names group.
The Create Names from Selection dialog box appears.
Step #3 – In the Create Names from Selection dialog box, verify that the Left column checkbox is selected then click OK.
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.
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).
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.
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(.
Step #2 – Click the Use in Formula button in the Formulas > Defined Names group and select Hot_Drinks.
Step #3 – Press Enter.
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.
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.
Step #3 – Click the Paste List button at the bottom of the Paste Name dialog box.
Step #4 – An alphabetized listing of all range names is displayed.
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.
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.
Step #3 – Drag to select cells B7:E7 in the worksheet.
Step #4 – Click the green checkmark in the Name Manager dialog box then click Close.
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.
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.
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.
