An Excel Named Range is a powerful tool and something we should all be using to make our formulas quick and easy to write and read.
I’m not going to cover the basics of creating a named range as I’ve done that here, however I want to remind you of the different types of names and then I’ll show you a clever shortcut.
Named Range Types
There are 4 types of Named Ranges you can create:
- For cells - for example cell C3 has the name fx_rate which you can see in the name box (to the left of the formula bar):
I can then use that name in a formula instead of the cell reference as you can see in the formula bar below for cell C6:
- For ranges - for example I’ve given cells G4:G17 the name US_Sales:
I can then use that name in a formula like I’ve done below in cell G1, which sums cells G4:G17 by referring to the named range US_Sales:
- For formulas - I can define a name for a formula, for example I could combine my fx_rate name and my US_Sales name in a new name called US_Sales_AUD like so:
Which will give me the sum of G4:G17 * the FX rate in cell C3, which is $1723.61. I can then reference that name in a cell instead of inserting the formula, as you can see below in cell C9:
I can even use that named formula in another formula! That’s a lesson for another day.
- For constants - instead of referencing a cell for my FX rate, I could simply assign the rate to the name like so (see ‘refers to’ field below):
Then I could use it in a formula like I’ve done in cell C1 below:
Editing Named Ranges
If I needed to change the FX rate I’d edit the name in the Name Manager:
Changing the rate in the name manager like this will mean it's picked up by all formulas using that name. It's a great way to quickly make global changes.
Named Range Scope
When you define a name you can specify the scope of that name to the Workbook or a specific Worksheet.
- Workbook scope – names with workbook level scope, also known as global workbook level, can be referenced in a formula on any worksheet in the workbook.
- Worksheet scope – names with worksheet level scope, also known as local worksheet level, can only be referenced from the sheet to which the name is assigned, unless you qualify the name by preceding it with the worksheet name. For example in sheet ‘Types’ I have named cell H18: ‘total_au_sales’ with the scope limited to the sheet called ‘Types’:
I can reference this name in the sheet called ‘Types’ like so:
=total_au_sales
But if I wanted to reference that name from another sheet I would have to qualify it with the sheet name like this:
=Types!total_au_sales
Which is equivalent to:
=Types!$H$18
Note: if your sheet names have spaces in them then you must surround them in apostrophes like this:
='Types of Names'!total_au_sales
Handling Duplicate Name Conflicts
- By default names are created with a Workbook level scope.
- If you attempt to create a name that already exists in the file, Excel will handle the conflict by limiting the scope for the new name to that of the worksheet.
Note: you can automatically create duplicate names when you copy a sheet already containing named ranges.
- If you reference a name that is set up for both global workbook scope and local worksheet scope, Excel will use the local name defined for the worksheet as it takes precedence over the global name. You can override this by prefixing (qualifying) the name with the workbook name e.g.
=YourWorkbookName!NamedRangeName
This will force Excel to use the name with the Workbook scope.
Note: you cannot override the scope for the first worksheet, this will always use the local name if there is a name conflict.
Working with Named Ranges in Templates
Often when you create a template in Excel you’ll use named ranges, and if you don't you should because it's best practice.
For example in my former life as an accountant one of our jobs was to collate the global budget for the IT department. This meant gathering figures in each country’s local currency and then converting it to GBP for reporting the consolidated budget.
Each sheet would have a named range for the FX rate. We could either make each name unique and retain global scope:
Tip: Don't forget another alternative is to create a named constant for each FX rate.
Or we could limit the scope of each name to that of the worksheets (called AUS, EUR, USA) and use the same name:
The downside of these options is that you could end up with a massive list of names and if you only require local scope then there’s a more efficient way.
Drum roll….
Local Named Range Shortcut
Adding an exclamation mark to the beginning of your ‘refers to’ cell reference creates a name that has local scope but need only be created once. E.g.:
The above named range exists locally on every worksheet in the file but only once in the name manager:
With this approach I could set up a the first country's sheet, then copy the whole sheet for the next country and all I would need to do is change the FX rate in cell C3 and any mentions of the country name. All formulas would remain the same.
Features of Names Defined with an Exclamation Mark
- When you reference the name ‘fx’ in a formula it has a local scope. i.e. the exclamation mark has the same effect as qualifying the sheet name to that of the (local) sheet containing the formula.
- However, it also means the name cannot be referenced globally, even if fully qualified.
- And you cannot select the name from the name box, it simply doesn’t appear in the list:
- When you copy a worksheet containing this type of name you don’t get a ‘duplicate name’ error. Happy days.
Thanks
I’d like to thank Jim Benton for teaching reminding about me the exclamation mark shortcut. He discovered it by accident when editing a reference to a Name. It’s not a technique documented anywhere by Microsoft (that I could find).
Nice find, Jim 🙂
UPDATE: This technique was also used in Roberto's Excel Factor entry - Dynamic Data Validation List technique, back in 2012!
Please Share
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
And please leave a comment below if you'd like to give a shout out to Jim and say thanks.
Jeff Weir says
Awesome post, Mynda. This page is going to get ‘referenced’ in my book!
Mynda Treacy says
Cheers, Jeff.
That exclamation mark trick is so clever.
MF says
Hi Mynda,
Maybe it’s a bit irrelevant to the topic… but the trick of using ! in Name inspired me to do an experiment and discovered that we may use wildcards to reference to other sheets.
Here’s my sharing:
http://wmfexcel.com/2015/07/11/sumc3-is-it-a-valid-formula-no-it-is-magical-indeed/
Hope you like it.
Cheers,
Mynda Treacy says
Awesome, MF! Love it 🙂
René says
I came across the ! trick when I tried to avoid the #REF error when you delete a row in a range with cells that point to the cell above. Create a named range in cell A2, like CellAbv, and it points to !A1. Now you can delete a row, and the reference still is intact.
Randy Madden says
Hey Mynda,
Excellent post today. I can’t even imagine creating an Excel model of any significant size that doesn’t use named ranges. I’ve been using them for decades. I’m probably not the first, but just in case, I thought I’d mention one of the techniques we use most frequently that you didn’t mention. It’s a variation of #2, named ranges. We often have files that use one sheet that contains all of our base data, and then use it as a database to populate reports written on top of it. As new data arrives we need the references in our reports to grow as the data grows. So we use a function to define that range – usually an Offset combined with a CountA. That way the ranges are dynamic and stay accurate without ever needing any editing. Love your blog, keep the great tips coming! Rm
Mynda Treacy says
Hi Randy,
Glad you liked my post 🙂
I too like OFFSET for dynamic ranges, or INDEX, but by far my favourite is Excel Tables.
Cheers,
Mynda
Jim says
I used to do this all the time
Then I discovered Tables and have never done it since
Jim
Jim says
OK, perhaps sometimes (interactive charts where Pivot Charts fall short spring to mind) but far less than I used to
Kevin Lehrbass says
That was a great article Mynda! Must know for data analysts. A lot of people aren’t even aware of named ranges or don’t realize the difference between local and global.
Mynda Treacy says
Thanks, Kevin! I’m honoured you liked it 🙂
Martin Nolan says
Hi Mynda,
Using a named range like !A1 can be handy. However, there is a bug described by Jan Pieterse at http://www.jkp-ads.com/Articles/ExcelNames10.asp that you should watch out for that may give incorrect results when the calculation is called from VBA. In these cases he recommends using =INDIRECT(“A1”) instead.
Martin
Mynda Treacy says
Thanks for sharing Jan Karel’s post, Martin. I was only focussing on the use of named ranges in formulas but I’m sure some will want to know about the VBA implications too.
Cheers,
Mynda
Viki Christensen says
Hi Mynda! Thank you sooo much for posting this fabulous tip, and thanks to Jim for discovering it and sharing it with you and all the rest of us. What a cool feature! We really appreciate your sharing your Excel expertise, especially when you give “best practices” and provide all the real-world examples to show its relevance. Cheers!
Mynda Treacy says
Thanks for your kind words, Viki. Glad you liked Jim’s tip 🙂
Lior Sternberg says
There is also a possibility of creating a dynamic name range using the offset counta functions.
You can also reference a table to make it dynamic.
Mynda Treacy says
Sure can, I wrote about dynamic named ranges here.
And using Table Structured References here.
Mynda
Lior Sternberg says
Great TIP! Thanks Mynda!
Mynda Treacy says
Glad you liked it, Lior.
Bob Umlas says
Oh — and thanks so much for mentioning my book early in this article!
Mynda Treacy says
My pleasure, Bob. It’s a great book 🙂
Bob Umlas says
See page 90/tip 41 of my Excel magic book! I “discovered” this in the late ’80’s!
Jan says
Great tip! Thanks for sharing.
Cheers,
Jan
Mynda Treacy says
Cheers, Jan. Glad you enjoyed it.
ruve1k says
Mynda, I’m curious as to where you discovered the tip that you noted in Handling Duplicate Name Conflicts.
“Note: you cannot override the scope for the first worksheet, this will always use the local name if there is a name conflict.”
Thanks!
Mynda Treacy says
Hi,
I found it on this page. Scroll down to the sub heading: ‘Defining and entering names’ and you’ll find it in the paragraph above.
Kind regards,
Mynda
MF says
Hi Mydna,
Very interesting tip of using the !.
btw, should the second screenshot under the section “Local Named Range Shortcut” be referring to C3?
Cheers,
Mynda Treacy says
Cheers, MF.
Thanks for spotting the image error. I’ve fixed it now. I should pay you to proof read 🙂
Mynda
MF says
Hi Mynda,
I’ve got the paycheck already… I’ve learnt so much from you free of charge.
Cheers, 🙂
Mynda Treacy says
Aw, thanks MF. That’s kind of you 🙂