How to create a relative named range in Excel

Using named ranges in Excel is a core skill. Normally, we refer to a specific range. However, named ranges have a secret feature… the ability to use a relative named range.

They don’t sound too exciting, but they open up a new world of opportunity, which you may have never considered before.

Let’s start with a bit of terminology:

  • Absolute named range: Refers to a specific range which does not change
  • Dynamic named range: Refers to a range which expands/contracts to a specific size
  • Relative named range: Refers to a range which is determined based on where it is used

If that is unclear, don’t worry, it will become clear as we work through the post.

So, let’s fire up Excel and take a look.

Creating named ranges

Let’s start by reminding ourselves how to create a standard named range.

From the Ribbon, click Formulas > Define Name.

Formulas - define name

The New Name dialog box appears.

Standard named range

The important options are:

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

Set these options, then click OK.

Using the screenshot above as an example, any time =namedRange is used, it is equivalent to entering =Sheet1!$C$4:$C$7.

So, that’s caught us up on standard named ranges. Now, let’s move on to relative named ranges.

Creating relative named ranges

When entering a range into the Refers to box, Excel enters the $ signs (to freeze the cell references) automatically.

But (here is the trick) there is no requirement to use $ symbols. 

For example, we can create a named range that refers to =Sheet1!C4:C7. Without the $ signs. It is now a relative named range. The range is set relative to the cell selected at the time of creation.

To prove how this works, select cell F5, then create a named range called relativeNamedRange with =Sheet1!C4:C7 – don’t include any $ signs.

Click OK to create the name.

Create relative named range

Next, move down one cell to F6. Enter a formula that uses the relativeNamedRange.

As you enter the formula, notice how the range has also moved down one cell.

Using a relative named range

In the screenshot above, =MIN(relativeNamedRange) calculates the minimum value.

The minimum value within the range at the time of creating the name was 10 (the value in cell C4). But, the named range no longer contains 10; the cells have shifted down by one cell. Therefore, the result will be 15.

In case you’re wondering… Yes, we can use mixed references with the $ on the row or column references.

Examples of relative named ranges

Here are a few ideas where you can implement this technique.

Always refer to the cell above

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

Before row added:

Formula using the range above

After row added:

Range above insert row

You will notice that the range of the formula does not change.

Creating a named range for the cell above solves this problem.

CellAbove named range

If we use the CellAbove named range in the formula, it always refers to the cell directly above.

Function using the CellAbove calculation

The formula in cell C9 is:

=SUM($C$4:CellAbove)

No matter how many rows we add, the range always expands from C4 to the cell above the formula.

Running total

Another use case for relative named ranges is creating a running total.

The calculation method for the running total is: cell above + cell to the left.

Running total named range

In the screenshot above, the formula is:

=N(!D3)+!C4

The N() function is a conversion function:

  • Non-number values are converted to numbers
  • Dates are converted to serial numbers
  • True is converted to 1
  • Anything else is converted to zero

In a running total calculation, the cell above the first cell is often a heading. By using the N function, it converts a heading to 0. Therefore, it does not impact the result of the running total calculation.

Running total named range result


In the screenshot above, the formulas in cells D4:D7 are =runningTotal. Excel has calculated a running total by returning a different result for each named range.

Global vs local relative named ranges

In the two examples above, we did not use a sheet name within the named range. Therefore, these were “global” relative named ranges. They can be used on any sheet in the workbook.

If we include the name of a sheet, it creates a “local” relative named range. The sheet remains fixed.

For example, if cell C9 of Sheet1 were selected, and we created a relative named range with the following formula, it would create a cell above reference, but only for that sheet.

='Sheet1'!C8

So, if the named range were used in cell D2 of Sheet2, using, it would return cell D1 of Sheet1 (i.e. all always refers to Sheet1 no matter where in the workbook).

Therefore, we need to be careful when creating relative named ranges so they have the correct global or local range.

Related Posts:


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 “How to create a relative named range in Excel”

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

    Reply

Leave a Comment