• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Relative Named Ranges

You are here: Home / Excel Formulas / Relative Named Ranges
April 4, 2018 by Mynda Treacy

Excel Named Ranges is a vast topic that includes some simple techniques that we all can and should use to make our spreadsheets easier to build and maintain. Plus, some more advanced techniques like relative named ranges, which are good to know. Especially for that occasion when you inherit a workbook from an Excel Superuser who thinks you’ll have no hope deciphering their file.

Note: If you’re not familiar with Named Ranges you can read up on them here first.

Download the 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.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Watch the Video

Subscribe YouTube

Relative Named Ranges

A Relative Named Range returns a result that is relative to the cell in which you use it. As opposed to regular named ranges which are typically absolute, in that it doesn't matter where you reference the named range from, it will always return the same result.

To understand this let’s take a moment to revisit a concept that every Excel user should know very well, and that is the way a relative cell reference automatically updates as it’s copied from one cell to the next.

For example, cell B7 in the image below contains a SUM formula that uses relative cell references B2:B6:

Relative Named Ranges

When we copy the formula in cell B7 across to cells C7, D7 and E7 it automatically adjusts the column reference relative to its new location. i.e. copying the formula in B7 across one column to cell C7 will result in =SUM(C2:C6), and so on.

Note: If I were to copy the formula down a row the row references would also adjust as they are also relative. More on relative and absolute references here.

Relative Named Ranges work in the same way, and we can use them to replace the individual SUM formulas in row 7 of the example above.

For example, in the image below, row 7 contains the formula =Total_Sales. The Edit Name dialog box shows that the name Total_Sales, which is displayed for the active cell, B7, is referring to the formula:

=SUM(Sheet2!B2:B6)

Total_Sales is in essence a named formula, and I’ll refer to it as such going forward.

Total_Sales example 1

Notice that the named formulas in cells B7, C7, D7 and E7 are all the same; =Total_Sales

However, if I edit the name while the cursor is set in cell C7 you can see in the image below that the named formula, Total_Sales is now referring to cells C2:C6:

Total_Sales example 2

Likewise, if you edit the names while cells D or E are selected. In other words, the named formula Total_Sales will always sum the 5 cells immediately above the cell in which you place it. It does this because the cell references in the Refers to field are relative.

Scope of Relative Named Ranges

The named formula, Total_Sales, has the scope of the workbook, meaning I can use it on any sheet, however the formula in the Refers to field (see image below) specifies that it will always sum cells on Sheet2:

Scope of Relative Named Ranges

For example, if I enter =Total_Sales in cell B7 on sheet 1 it will sum cells B2:B6 on sheet 2.

If I want to use this named formula relative to any sheet, I can change it to:

=SUM(!C2:C6)

Omitting the sheet name and leaving the exclamation mark in front of the cell references results in a dynamic sheet reference. So, while the named formula will have the scope of the workbook, it will refer to the active sheet.

For example, now if I enter =Total_Sales in cell B7 on sheet 1 it will sum cells B2:B6 on sheet 1.

In other words, I have a truly relative named formula, i.e. relative to both the cells and the sheet.

Warning: This use of an exclamation mark in named ranges has been known to cause Excel to crash and can create problems when used with VBA (e.g. creating an action like Application.CalculateFull), so use it with caution. That said, I’ve never experienced any problems, so it may be resolved in more recent versions of Excel.

Relative named ranges and formulas should be used with care, because if you use them in the wrong location they can still return results, but they may be invalid simply because of the cell you use them in.

Creating Relative Named Ranges

Location, location, location. It’s the cliché that should be front of mind when creating relative named ranges.

When you create a relative named range, you should first select the cell that you want the range relative to. For example, to create the Total_Sales named formula I first selected cell B7. Then Formulas tab > Define Name:

Creating Relative Named Ranges

This will open the New Name dialog box where you can give your named range or formula a name (no spaces allowed), select the scope and enter the cell reference or formula in the Refers to field:

new name dialog box

Other Uses for Relative Named Ranges

So far, the example we’ve looked at is a relative named formula, but you can also create a relative named range. For example, with cell B7 selected we can name the cells B2:B6; Product_Sales:

Product_Sales

And then use the relative named range in a SUM formula (or any other formula):

relative named range in a SUM formula

Relative Dynamic Named Ranges

Dynamic named ranges are a staple for the intermediate/advanced Excel user. They allow us to return a range that adapts to ever changing data. For example, we might use a dynamic named range as the source for a PivotTable.

Change PivotTable Data Source

As new rows are added to our source Data_Rng, the dynamic named range also increases to include the new data, thus eliminating the need for us to update the PivotTable data source cell references.

Or maybe you’re using a PivotTable as the source data for a regular chart. You can use a dynamic named range for the chart source allowing it to automatically pick up changes to the PivotTable size.

PivotTable as the source data

However, typically these dynamic named ranges aren’t relative.

An area where relative dynamic named ranges will come in handy though, is for Sparklines. In the image below, I’ve inserted a group of Sparklines in column A and you can see the Data Range is hard coded C2:I9:

sparklines in Column A

This means that when new data is added for future months in column J onward, we’ll have to edit the Sparkline Data range and update it… MANUALLY! That ‘M’ word is enough to make an advanced Excel user queasy.

Now ideally, we’d use a dynamic named range for the Sparkline Data Range, but you can’t enter a dynamic named range for a Group of Sparklines, only for individual Sparklines. And I don’t fancy creating 8 separate dynamic named ranges. That’s way too much work.

Luckily, we can create one dynamic named range that is relative to the cell it’s in and use that for each individual Sparkline (it’s a lot quicker to copy and paste 8 Sparklines).

With the Sparklines removed, I’ll start with cell A2 selected > Formulas tab > Define Name.

I’ll call my relative dynamic named range ‘sparkline_rng’, and use an INDEX formula like so:

=C2:INDEX(C2:O2,,COUNTA($C$1:$O$1))

In English it reads;

Start the range in cell C2 and find the last cell in the range C2:O2 using INDEX, skip the row argument for INDEX because there's only one row being INDEXed, then COUNTA returns the column number argument by counting the columns that contain text in the range C1:O1 to find the last column containing a month name.

Create a named range for the Sparklines; I called mine sparkline_rng as you can see below:

sparkline_rng

I can check the formula is evaluating correctly by inspecting it in the Name Manager (CTRL+F3).

For example, in the image below, you can see I’ve selected cell A3 (1) and in the Name Manager (2) I’ve selected the sparkline_rng in the list of names (3). To see the marching ants around the cells returned by the formula, I simply click anywhere in the ‘Refers to’ box (4):

Refers to

Now that I know my named range is working correctly I can insert the Sparklines. Just enter the first one and in the Create Sparklines dialog box enter the relative dynamic named range in the Data Range field:

insert the Sparklines

Then copy and paste the Sparklines one at a time, so they remain ungrouped:

copy and paste the Sparklines

A big thanks to Christopher Mangels for the Sparkline example.

Limitations

Relative named ranges cannot be used in hyperlinks because cell A1 is always the hyperlink anchor for a defined named:

relative named ranges limitations

Using absolute named ranges with Hyperlinks isn’t an issue, but for relative named ranges it essentially renders them absolute, or always relative to A1 when used with hyperlinks.  If someone knows how to circumvent this, please let me know as it has been driving me crazy!

Name Range Manager

If you are in need of a utility to manage defined names in your Excel models, this one is a must-have.

  • List all names in your active workbook.
  • Filter them using 13 filters, e.g. "With external references", "With errors", Hidden, Visible.
  • Show just names that contain a substring.
  • Show just names unused in worksheet cells.
  • Edit them in a simple dialog or make a list, edit the list and update all names in one go.
  • Delete, hide, unhide selected names with a single mouse click.

More Named Ranges Posts

Excel Named Ranges

Excel Named Range Shortcut

This Excel Named Range shortcut using the exclamation mark will enable you to create a global name with local scope.
Writing Excel Formulas Efficiently

Writing Excel Formulas Efficiently

Formulas should and can be quick to write, interpret and update. That's why writing Excel formulas efficiently should be one of the first skills you master.
Excel Dynamic Named Ranges

Excel Dynamic Named Ranges

Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas, PivotTables and other Excel tools.
excel named ranges explained

Excel Named Ranges Explained

Excel named ranges can be a huge time saver. They're easy to use and even easier to read in your formulas.

More Excel Formulas Posts

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.


Category: Excel FormulasTag: named ranges
Previous Post:ALT codes for symbols and charactersALT Codes for Symbols and Characters
Next Post:Volatile User Defined Functionsvolatile user defined functions

Reader Interactions

Comments

  1. Jeff S

    January 5, 2021 at 12:51 am

    I was looking specifically for how to make a cell reference relative across sheets and this answered my question. Also, the feature is not intuitive and it was very valuable to find this page.

    Reply
    • Catalin Bombea

      January 5, 2021 at 2:13 am

      Thanks Jeff, glad to hear you found it useful 🙂

      Reply
  2. Rosa

    May 7, 2020 at 11:19 pm

    Good morning Mynda,

    Great video, as always I learn something new every time I watch your videos. I have a question how do you display the formulas as text right below without using the ‘ or the =Formulatext(Cell)?

    Thank you,
    Rosa

    Reply
    • Mynda Treacy

      May 8, 2020 at 8:35 am

      Glad it was helpful, Rosa! To display the formula without FORMULATEXT, copy the formula from the cell, then type an apostrophe before pasting it into the cell. The apostrophe in front of the equals sign converts it to text.

      Reply
  3. michael

    September 20, 2019 at 12:11 am

    i’m trying to have a pivot on one sheet and the sparklines on another sheet and have the pivot controlled by a slicer there. But i’m getting invalid reference for location or data range invalid when i use the sparkline_rng name.

    it works on the sheet with the pivot table though..

    Reply
    • Mynda Treacy

      September 20, 2019 at 8:08 pm

      Hi Michael,

      My guess is you didn’t select the first cell to contain the sparkline before creating the relative named range. If you want to share your file and post your question on our forum we can take a look.

      Mynda

      Reply
  4. Peter

    June 27, 2019 at 7:39 am

    Mynda, brilliant as always!

    Reply
    • Mynda Treacy

      June 28, 2019 at 4:22 am

      Thanks, Peter!

      Reply
  5. Sandeep Kothari

    June 17, 2018 at 8:20 pm

    Extra-ordinary!

    Reply
  6. Jon Wittwer

    April 21, 2018 at 12:17 am

    Mynda, Thanks for the =!A1 trick (leaving off the sheet name). That helped me figure out how to make a relative “cell_above” named range work throughout the workbook. – Jon @ Vertex42

    Reply
    • Mynda Treacy

      April 21, 2018 at 8:04 am

      Glad you found it useful, Jon 🙂

      Reply
  7. Bill Turner

    April 9, 2018 at 5:33 am

    Hello Mynda,
    A good detailed post , thanks for that.
    Like most of us old hands I’ve been using named ranges for ever. I often make use of the dynamic named range in that the range auto adjusts to suit the amount of elements you have in it.
    For example a Named range called OFFSET referring to a range on a sheet called SetUp

    =OFFSET(SetUp!$G$12,0,0,COUNTA(SetUp!$G$12:$G$1000),1)

    This range may have 1 element in it or more and the named range auto adjusts to include more as they get added or less as removed.
    With your skill of explaining things maybe you could include a small section on this type of dynamic named range.

    back to your post…
    How would you reference a dynamic range (as detailed above) in VBA if they all have the same name ?
    Ordinarily for a static named range
    Worksheetname.range(“namedrange”) gets you access to the data in the named range but if you have multiple of the same name ??

    Thanks

    Reply
    • Catalin Bombea

      April 10, 2018 at 3:08 pm

      Hi Bill,
      Not sure what you mean, you cannot have multiple defined names with the same name, only if their scope is set for different sheets. You can have a defined name named “Test” scoped to Sheet1, another name “Test” scoped to Sheet2, or to This Workbook, but you should always use full references to get to the correct value for that name, even if the scope is set to a specific sheet or to entire Workbook:
      thisworkbook.worksheets(“sheet3”).range(“Test”)
      Regards,
      Catalin

      Reply
    • Mynda Treacy

      April 10, 2018 at 3:37 pm

      Hi Bill,

      Thanks for you kind words!

      There is a link to a tutorial on dynamic named ranges in the post above under the heading “Relative Dynamic Named Ranges”.

      Mynda

      Reply
  8. jim

    April 4, 2018 at 6:57 pm

    great post Mynda

    I regularly used dynamic ranges for charts but when I discovered Tables I never needed to again…

    …until I started plotting charts from pivot tables (BEWARE when you edit such a chart – I find I need to edit each series individually or else it reverts to a pivot chart and cannot be undone)

    I remember discovering the ! trick by accident – took me ages to work out what was happening

    jim

    Reply
    • Mynda Treacy

      April 4, 2018 at 7:03 pm

      Thanks, Jim 🙂

      Reply
      • jim

        April 4, 2018 at 8:14 pm

        I also liked the way you avoided using a volatile formula with the cellref:cellref trick

        Reply
        • Mynda Treacy

          April 4, 2018 at 8:38 pm

          I like INDEX for dynamic ranges too 🙂

          Reply
  9. Steven Rider

    April 4, 2018 at 2:13 pm

    This is a pretty sharp post. I don’t typically find new Excel tricks that I haven’t seen. This has some nice applications for some obscure work cases I’m having to address. Basically, I have a fluctiating range of rows, that is not dynamically updating due to an Excel Add-On.

    This trick allows me to create a dynamic range I can use in all of my columns. I have to create the range using some VBA and there is a “gotcha” that isn’t obvious at first: when using the RefersTO reference, one must include a “=” at the start of the string.

    Example below:

    Set RowRng = Range(Range(“A10”,Range(“A20″))

    ThisWorkbook.ActiveSheet.Names.Add Name:=”singleDayNamedRange”, RefersTo:=”=” & ActiveSheet.Name & “!” & RowRng.Address(True, False)

    I use the “address” function to generate the named range because I deal with users with different Excel languages and I try to avoid stringing together formulas in VBA.

    My vba code is linked below if for some reason it doesn’t show up well in this comment:
    https://pastebin.com/raw/FxHDJBX8

    Anyway, strong post. Well done.

    Reply
    • Mynda Treacy

      April 4, 2018 at 2:50 pm

      Thanks, Steven! Glad you’ll have a use for it and thanks for sharing your code.

      Reply
      • Steven Rider

        April 4, 2018 at 4:56 pm

        Another gotcha is if some renegade user wants to put spaces in the name of their sheet. (the old “Space In The Worksheet Name” vba problem)

        As mentioned, I deal with international users, so I try to avoid direct formula adjustments with vba (i.e. directly putting in two ‘ around ‘Sheet Name’!). I let Excel update the range.

        Updated code dynamically defends against potential spaces in sheet names by briefly renaming the sheet:

        'Needed in case sheet has space in it. Ending ""x^x"" to avoid duplicate sheets.
        Dim oldSheetName As String, RowRng as Range
        If InStr(1, ActiveSheet.Name, " ") > 0 Then
            oldSheetName = ActiveSheet.Name
            ActiveSheet.Name = Replace(oldSheetName, " ", "") & "x^x"
        End If
        
        Set RowRng = Range(Range("A10",Range("A20"))
        
        ThisWorkbook.ActiveSheet.Names.Add Name:="singleDayNamedRange", RefersTo:="=" & ActiveSheet.Name & "!" & RowRng.Address(True, False)
        If oldSheetName  "" Then ActiveSheet.Name = oldSheetName
        
        'Pastebin Updated too.
        
        Reply
        • Ignacio De Bustamante

          September 19, 2018 at 2:11 am

          La solucion es Usar Guiones bajos en lugar de espacios. e.e.: Hoja_1.

          The solution is to use underscores instead of spaces. e.g. Sheet_1

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.