Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
Email Address * First Name *
By entering your email address you agree to receive emails from Excel Off The Grid. We'll respect your privacy and you can unsubscribe at any time.

Creating relative named ranges

Creating relative named ranges

I’ve posted many times about named ranges in various guises.  However, one thing I have not covered is relative named ranges.  Relative named ranges don’t sound too exciting, but once you’ve read this post I’m sure there will be plenty of examples where you could have used them and now wish you had.

Creating named ranges

To start, let’s remind ourselves how to create a standard named range.  There are many ways to achieve this, we’ll go the long way, because creating a relative named range follows the same process.

From the Formulas ribbon click Name Manager

Relative Named Range - Ribbon Name Manager

The Name Manager window will open.  Click New.

Relative Named Ranges - Named Range Window

The New Name window appears.

Relative Named Ranges - Name Manager New

The important options are:

  • Name – the name you wish to give the named range
  • Scope – this has two options, Workbook or Worksheet.
    • A Workbook level named range can be used in a cell or calculation by using only its name.
    • A Worksheet level named range must use the name of the sheet and the name of the name range if it is used outside the sheet in which the named range is held.
  • Refers to – the range of cells (or can be a calculation) which you want to name.

Set these options then click OK.

Using the example in the screenshot above, any time =namedRange is used, it is equivalent to entering =Sheet1$A$1:$H$10.

Creating relative named ranges

Excel will try to enter $ signs (to freeze the cell references) automatically, but there is no requirement to use $ symbols.  A named range can be created where the Refers to value is =Sheet1!A10:H10.  Without the $ signs it has now become a relative named range.  The range is set relative to the cell selected at the time of creating the named range.

To prove how this works, I selected Cell E4, then I created a named range with Cells A1-B5 (without any $ signs)

Relative Named Range - Example

By using this named range in a formula one cell below the cell selected at the time of creation, the named range will also move one cell down.

Relative Named Range - Example Result

The minimum value within the named range at creation would have been 1 (Cell A1), but the result of the calculation in the screenshot above is 2 (not 1).  The relative named range no longer contains the 1, as the cells have shifted down by one cell.

It is possible to mix and match the $ symbols to freeze specific columns or rows.

Formula Magic with Dynamic Arrays

Of all the features available in Excel dynamic arrays provide the most power for the smallest time investment.  Yet most Excel users do not even know what they are.

Have you ever faced these spreadsheet scenarios?

  • How can I use VLOOKUP to return all the matching items, not just the first?
  • How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
  • How can I quickly create unique lists of items to use with my SUMIFS calculation?
  • How can I stop copying down formulas every time my source data changes.
  • How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.

Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂

Uses for relative named ranges

That all sounds interesting, but is it actually useful?  Here are a few ideas to implement relative named ranges.

Always referring to the cell above

If you have a formula which should always refer to the cells above, what happens when a new row is added?

Relative Named Range - Cell Above 1

The range of the formula does not change.  The formatting has copied down, but the formula has not.

Relative Named Range - Cell Above 2

Creating a relative named range for the cell above would solve this problem.

Relative Named Range - Cell Above 3

It does not matter how many rows are added the formula will always refer to the cell directly above the formula.

Simplifying calculations

One of the key advantages of named ranges is simplifying calculations.  This, rather basic example shows that a Total Calculation can be created and copied down.

Relative Named Ranges - Simplify Formula

The formulas in Cells D2 – D5 are all =totalCalc.  Excel has calculated a different result for each use of the named range.

Running total

Creating a running total is also possible with a relative named range.  This example shows it is possible to mix relative and absolute cell references within a named range.

Relative Named Range - Running Total

The formulas in Cells E2 – E5 are =runningTotal.  Excel has calculated a different result for each use of the named range.

A warning

It is not possible to create a ‘global’ relative named range.  In each of the examples above, the name of the sheet is used to define the named range, so there needs to be new relative named ranges for each worksheet.  I have seen suggestions of just using the “!” without the sheet name to make it ‘global’, however this can result in some strange behaviors and can even result in Excel crashing.  In such circumstances, it may be better to investigate using the INDIRECT or OFFSET functions.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

2 thoughts on “Creating relative named ranges

  1. Kie says:

    I have created the following Workbook relative named range and assuming I am in Cell C4:
    _1Up RefersTo =Index(!C3:C4,1,1) This picks up C3
    _1Dw RefersTo =Index(C4:C5,2,1) This picks up C5
    _1Lf RefersTo =Index(B4:C4,1,1) This picks up B4
    _1Rg RefersTo =Index(C4:D4,1,2) This picks up D4
    This would allow me to use on any sheet.
    Question would this create havoc or crash Excel.
    Many thanks and also for your excellent blogs.
    Kie

Leave a Reply

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