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.

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.

Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

4 thoughts on “Creating relative named ranges”

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


Leave a Comment