top of page

Three Reasons to use Named Ranges in Excel


Blank name tag with heading "Hello My Name Is"

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.


Screen shot of Excel worksheet showing sales in columns A through E, and the tax rate in cell G1.

The formula in column D looks like this:


Screen shot of formula that references the tax rate.


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:

Screen shot of the New Name dialog box

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:

  1. Simple navigation

  2. Easier-to-read formulas

  3. 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:



Screen shot of the name drop-down box in Excel

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:


Screen shot of a selected named range

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:


Screen shot of named ranges that refer to other worksheets

When I click "Q1Summary", Excel moves me to the Quarter 1 worksheet, and highlights the named range:


Screen shot of the Quarter 1 worksheet, with the range "Q1Summary" selected

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:


Screen shot of formula using the TaxRate named range

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:


Screen shot of VBA code with macro called "MacroWithoutNamedRange"

Ugly, right? It's fixable, if you know how to edit your VBA code. But using a named range, you'd get this instead:

Screen shot of macro called "MacroWithNamedRange", showing the VBA code that refers to the named range.

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

Photo of author Kim Martin







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.




© North Port Solutions, LLC, 2023. All rights reserved.








16 views0 comments

Related Posts

See All

Comments


Join our mailing list

Subscribers receive email notification of new posts.

Thanks for subscribing!

bottom of page