Excel has a lot of great features that many people don't know about, or don't use because they don't fully understand them. Here's one of them: Named Ranges.
If you're a frequent Excel user and you're not using Named Ranges regularly, it's probably something that can make your Excel life a little easier. Before I tell you why you should use them, let's talk about what they are.
In Excel, we often refer to a cell in our formula. Our cell references might looks something like this:
=A1
=$A$1:$D$197
=Sheet1!E8
=LastMonth!$E$8:$F$227
'[SalesReport.xlsx]Sheet2'!$A$1
A named range is a name we assign to a specific range of cells. Let's look at an example.
Example
Here I've got a simple spreadsheet that lists sales of my products with the quantity sold, unit price, tax amount, and total. The tax column is calculated using the formula in cell H1.
The formula in column D looks like this:
Since the tax rate is used repeatedly and is something I might refer to often, I'll give it a name. I do this by clicking on the cell, then clicking the "Define Name" button on the Formulas tab. I then specify the name, the scope, and the cell reference:
Once I click OK, I then have a named range I can use.
How might you use the name?
There are many good reasons for using a named range. I'll cover three of them in this article:
Simple navigation
Easier-to-read formulas
Macro friendly
Simple Navigation
Now that you've created a name, you can jump to the cells referenced by using the Name drop-down list. In the example above, let's say that you've got lots of rows - 1,000 or more. When you're looking at row 999 and wondering what the tax rate is, you'll do this:
Click on the address of the cell in the top-left corner of the screen, under the ribbon, as I've highlighted in yellow in the screen shot. In my example, my address box said "A999". When I click there, Excel shows me a drop-down list of all named ranges that are valid in this worksheet. I see the TaxRate name on the list (as highlighted above, once again), and click it. Excel moves me to the top of the sheet, selects cell H1, and shows the name (TaxRate) in the address box:
Note that this works across multiple worksheets as well. Here I have a "2023 Total" worksheet, which has formulas that refer to other worksheets on the Quarter 1, Quarter 2, Quarter 3 and Quarter 4 worksheets. Using this drop-down, I can click on any one of those, and Excel will automatically take me to the appropriate worksheet with the range selected. Here's the drop-down on my "2023 Total" sheet:
When I click "Q1Summary", Excel moves me to the Quarter 1 worksheet, and highlights the named range:
When you're working with large workbooks with many rows, columns and worksheets, this feature can be a real time saver.
Easier to Read Formulas
With named ranges, your formulas become easier to read. Going back to my Tax Rate example, my formula now references the "TaxRate" cell by name:
That's a simple example, of course, but consider this one:
BEFORE: =IFERROR(VLOOKUP(B2,$J$1:$K$5, 2, FALSE), "NOT FOUND")
AFTER: =IFERROR(VLOOKUP(B2,TaxRates, 2, FALSE), "NOT FOUND")
Better right? Right in the middle of that formula, I had "$J$1:$K$5" before the named range. After the named range, it's simply called "TaxRates", allowing me to quickly understand what its referring to.
Going back to the list of references I used above, here's what they might look like with named ranges:
Before | After |
=A1 | =TaxRate |
=$A$1:$D$197 | =CommissionTable |
=Sheet1!E8 | ​=PeriodEndDate |
​=LastMonth!$E$8:$F$227 | ​=LastMonthSalesSummary |
​='[SalesReport.xlsx]Sheet2'!$A$1 | ​='[SalesReport.xlsx]'!LeadTime |
Excel formulas can be very complex, and the more complex they are, the harder they are to read. Named formulas can be a big help.
Macro Friendly
Macros are a great productivity tool, allowing you to automate repetitive tasks. But when you record a macro using cell references, those cell rows and columns get hard-coded into your macro. Here's a quick macro that I recorded without a named range:
Ugly, right? It's fixable, if you know how to edit your VBA code. But using a named range, you'd get this instead:
Much better, and also more durable. The first macro is only going to work in one specific cell, while the second macro will work in any cell.
Lots More Reasons
There are many other good reasons for creating named ranges, but hopefully this list primes the pump, getting you thinking about them.
I haven't thoroughly covered the ins-and-outs of creating named ranges here, but the Internet is full of resources. Of course, if you come up with a specific question about them, reach out to me via the comments below or via email. I'm always happy to answer questions!
In my next Excel post, I'll show you how you can create a dynamic drop-down list in Excel, making good use of named ranges. To be notified when that post goes live, subscribe using the form below.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2023. All rights reserved.