Excel Named Range Shortcut

Mynda Treacy

June 23, 2015

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

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

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

    named range for a cell

    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:

    named range for a cell used in a formula

  1. For ranges - for example I’ve given cells G4:G17 the name US_Sales:
    2.  

    name for a range of cells

    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:

    name for a range of cells used in a formula

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

    2. name for a formula

    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:

    named formula

    I can even use that named formula in another formula! That’s a lesson for another day.

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

    named constants

    Then I could use it in a formula like I’ve done in cell C1 below:

    named constant used in a formula

Editing Named Ranges

If I needed to change the FX rate I’d edit the name in the Name Manager:

edit a 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’:
    •  

    setting the scope of a name

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

names with a 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:

names with a local scope

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

name shortcut

The above named range exists locally on every worksheet in the file but only once in the name manager:

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:

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

Apps for Office In Excel

Excel Forms

34 thoughts on “Excel Named Range Shortcut”

  1. Hi Mynda,

    Thanks a lot for all your videos and web pages: they are a source of continuous improvement for me.

    Is there a way to use “Local Named Range Shortcuts” with Excel tables ?

    Imagine an Excel file with one tab called “tab1” containing one table “tableT”. If you duplicate the tab1 (let’s be creative and call the new tab “tab2”), in this new tab, the table will be called “tableT12” (or any other random name).

    It would be cool that a table could have a Local Named Range in order to keep its name when duplicating the table.

    Reply

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

    Cheers,

    Reply

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

    Reply

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

    Reply

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

    Reply

  7. Hi Mynda,

    Using a named range like !A1 can be handy. However, there is a bug described by Jan Pieterse 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

    Reply

    • 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

      Reply

  8. 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!

    Reply

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

    Reply

  14. 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!

    Reply

    • 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

      Reply

  15. 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,

    Reply

Leave a Comment

Current ye@r *