Excel Named Range Shortcut

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 elsewhere. However I want to remind you of the different types of names and then I’ll show you a clever shortcut.

Watch the Video

Download Workbook

Named Range Types

There are 4 types of Named Ranges you can create:

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

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

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

4. 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

1. By default names are created with a Workbook level scope.
 
2. 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.

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

More Named Range Tricks

Create dynamic named ranges that automatically update as your data grows.

Create relative named ranges that reduce the number of names you need to define.

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 leave a comment below if you'd like to give a shout out to Jim and say thanks.