How much time do you spend editing the ranges referenced in your formulas because you’ve added new data to your source?
This is what I’m talking about; let’s say you have some data in columns A:E:
And in columns G and H you have a summary of the data, in this example, sales by Salesperson:
See the formula in the formula bar is fixed on cells B2:B19 and E2:E19. When I add new data for September to my data in columns A:E I have to manually edit my formulas in column H to include the new rows.
Let me just say, if you do this type of thing more than once in any workbook then you are wasting valuable time, and you should be setting up your formulas with dynamic ranges.
Dynamic ranges automatically detect new data and include it in your formulas.
Forget VBA, this is hands down one of the best ways to save time when working with Excel.
Note: this is a .xlsx file. Please ensure your browser doesn't rename it when downloading.
There are a few different ways to set up ranges so that they dynamically update.
1. Whole Column/Row References
For example taking the data above you could replace the formula in cell H2 with:
While this will work, it is the lazy way to create dynamic references and it’s not recommended.
Why? Because you’re asking Excel to do a load of work checking cells that are empty, and in Excel 2007 onward there are 1M+ cells in every column. The bottom line is whole column/row references = a slow workbook (usually).
Tip: If your workbooks are slow to calculate and save, and you have whole column or row references then this is likely to be the culprit.
2. Excel Tables
Dynamic references are just one of the benefits to housing your data in Excel Tables. Find out how to set them up and the other benefits of Excel Tables here.
Tip: If formulas are still a little daunting for you then Excel Tables will give you loads of benefits without the need for complex formulas.
3. Dynamic Named Range Using OFFSET
Now we’re getting a bit more sophisticated.
We can replace our formula in the example above with this:
Where ‘salesperson’ and ‘OrderAmt’ are dynamic named ranges, as you can see in the Name Manager below:
How to set up a dynamic named range
To set up any Named range go to the Formulas tab of the ribbon and click ‘Define Name’:
We can make it dynamic by entering a formula in the ‘Refers to:’ field like this:
Hold up, let’s take a closer look at that formula in the ‘Refers to:’ field:
The first part in orange is nothing special, but the second part in blue and pink uses the OFFSET and COUNTA functions to return a reference to the last cell in the range B2:B1000 that contains data.
Here’s a quick reminder of the syntax for the OFFSET function:
There’s an in-depth tutorial on the OFFSET function here, but I’ll quickly translate what this formula is doing in English.
=Reference the range that starts in cell B2:, and use the OFFSET and COUNTA functions to find the last cell in the range that contains data in cells B2:B1000. In the OFFSET function start in cell B1, and count all cells in the range B2:B1000 that contain data, (which = 18 i.e. rows 2 to 19), and return a reference to a cell that is 1 cell high.
That is, if I start in cell B1 and count down 18 cells I end up in cell, B19 therefore my formula evaluates like this:
When new data is added after cell B19 the formula will dynamically update to return the correct range, up to a maximum of row 1000 (because I’m only counting data in the range B2:B1000).
- You can’t have any blanks in B2:B1000 because the COUNTA function will not count those cells and this would result in an incorrect range.
- If you’re counting numbers you can replace the COUNTA function with the COUNT function like I’ve done for the OrderAmt named range (see the Name Manager image above).
- If you think your data will exceed 1000 rows simply increase the row number to adequately accommodate it.
The Problem with OFFSET
Now, while a dynamic named range using OFFSET is good, there is one main problem with it:
OFFSET is a volatile function, which means every time you edit any cell it recalculates. The impact of this can render large workbooks slow, very slow.
So slow that you avoid saving your work because it’ll mean you’re left twiddling your thumbs. You end up not saving your work because you don’t want to develop unsightly callouses, then your PC crashes…..and, well, you know the rest.
This is the reason why the next option is the best.
4. Dynamic Named Range using INDEX
The INDEX function is not only non-volatile, it’s faster than the OFFSET function.
We can set up a dynamic named range for the Salesperson column using INDEX like this:
And for the Order Amount column like this:
The syntax for the INDEX function in its reference form is:
INDEX(reference, row_num, [column_num], [area_num])
Note: the last two arguments, [column_num] and [area_num] are optional as we can tell from the square brackets. We don’t need them in this formula.
I’ll translate the Order Amount formula into English:
=Reference the range that starts in cell E2:, then INDEX the range E2:E1000, and return the row_num of the last cell containing data by counting all cells in the range B2:B1000 that contain numbers, (which = 18 i.e. rows 2 to 19), Index therefore returns a reference to E19.
My formula evaluates like this:
This is just one of the tricks in the INDEX hat. When teamed up with MATCH it can replace VLOOKUP, in fact INDEX & MATCH are faster than VLOOKUP too.
Daniel Ferry, MVP, owner of the Excel Hero blog and highly acclaimed Excel Hero Academy says:
“INDEX is the single most important function in the roster of Microsoft Excel functions”.
For Daniel’s comprehensive tutorial on the INDEX function check out his post titled ‘The Imposing INDEX’.
Did you like this post? What's your favourite way of setting up dynamic references?