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.
The New Name dialog box appears.
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.
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.
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:
After row added:
You will notice that the range of the formula does not change.
Creating a named range for the cell above solves this problem.
If we use the CellAbove named range in the formula, it always refers to the cell directly above.
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.
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.
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:
- Hide named ranges in Excel: 4 simple ways
- Cell ranges: 99% of users don’t know this
- Named Ranges – VBA properties & actions
Discover how you can automate your work with our Excel courses and tools.
Excel Academy
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.
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
I say give it a go and see what happens.
Do you need to use INDEX? If in C4, why not just use =!C4 for _1Up?
Excellent article, thanks. I liked the running total trick
Hi,
How to create a Named Range with all the Even Rows extracted from an existing Dynamic Named Range ?
Thanks for your help