Create Excel Named Ranges for Easy Formulas
To make your Excel formulas easier to read and manage, you can create Excel named ranges. In the video and steps below, I show how to name a range of cells in Excel, and the simple steps to use that name in a formula.
Video: Create an Excel Named Range
In this short video, I show how to name a range of cells in Excel, and then use that name in a formula.
Table of Contents
- Step 1: Select Cells to Name
- Step 2: Name Selected Cells
- Step 3: Confirm the Name
- Step 4: Use Named Range in Formula
- Why Use Named Ranges?
- FAQ
Step 1: Select Cells You Want to Name
First, open your Excel worksheet.
Then, choose the cells that you want to name.
In this example, I have a list of quantities, so I selected those numbers, in cells B2:B7.
Step 2: Name Selected Cells
After selecting the cells, click in the Excel name box, just to the left of the formula bar.
- Next, type a one-word name for your selected range of cells.
- For this example, I named the range Quantity.
- Then, to complete the name, press the Enter key.
Important: If you forget to press Enter, the name will not be stored!
Step 3: Confirm the Name
Now, if you click on the name box again, you’ll see your new name in the dropdown list!
This means it’s ready to use.
Step 4: Use Named Range in Formula
Now, let’s use our named range in a formula. To get the sum of the named range, type the following in a cell:
=SUM(quantity)
Start with an equal sign, type SUM, and then type the name quantity inside the brackets.
- Note: You can use upper or lower case
Press Enter, and you’ll see the total of the numbers in your named range!
Why Use Named Ranges?
- Clarity: Named ranges make your formulas easier to read.
- Convenience: You can use the name instead of cell references.
- Consistency: Helps avoid errors in large spreadsheets.
FAQ
How can I delete a named range?
- You can manage named ranges by going to the Formulas tab and clicking the Name Manager command. From there, you can delete or edit named ranges.
Can I change the name of a named range?
- Yes, you can go to the Name Manager, click on the name, type a new name, and click the OK button.
Get the Excel file
For more tips on using Excel named ranges, and to get the Excel file, go to the Excel named ranges page on my Contextures site.