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
The Name Manager window will open. Click New.
The New Name window appears.
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)
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.
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?
The range of the formula does not change. The formatting has copied down, but the formula has not.
Creating a relative named range for the cell above would solve this problem.
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.
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.
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.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match 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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- 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.
- 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:
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