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.
Forget VBA, this is hands down one of the best ways to save time when working with Excel.
Enter your email address below to download the sample workbook.
Dynamic References
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:
=SUMIF($B:$B,G2,$E:$E)
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:
=SUMIF(salesperson,J2,OrderAmt)
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:
=Sheet1!$B$2:OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B$2:$B$1000),,1)
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:
OFFSET(reference,rows,cols,[height],[width])
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:
=Sheet1$B$2:$B$19
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).
Notes:
- 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.
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:
=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$1000,COUNTA(Sheet1!$B$2:$B$1000))
And for the Order Amount column like this:
=Sheet1!$E$2:INDEX(Sheet1!$E$2:$E$1000,COUNT(Sheet1!$E$2:$E$1000))
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:
=Sheet1$E$2:$E$19
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, ex 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”.
Click here for a comprehensive tutorial on the INDEX function.
Excel CONVERT Function
Peter Sumner
I have used the OFFSET function before but it is particularly useful if you have dropdown validation lists that may require updating. You simply refer to the Named Range when setting up your validation. The dropdowns dynamically update without having to ammend your validation settings.
Ryan Merullo
Neither the offset nor index approach works with the SUMIFS function if the ranges contain blank cells in different rows. You can use either approach to perform a single operation on a single expanding range. SUMIFS is an extremely handy function. Thus far, I am using the primitive method of guesstimating liberally beyond my data. I have data, including many blank cells, in range A4:L1350. I am able to use SUMIFS, not with any of the OFFSET or INDEX formulas offered, but simply with the following:
=SUMIFS($G$4:$G$10000,$D$4:$D$10000,”*Item”,$C$4:$C$10000,”Person*”)
Sophie
Thanks Mynda, I really like your step by steps !
I’m trying to set up a dynamic reference for a table with data re male and female. My goal is to build a chart with 1 serie with data re male and the other re female. Currently, each time I refresh the data, I sort the table by male/female then edit manually the graph series. Is there a way I could avoid that manual sort and let excel plot the “male” and female” series ?
Mynda Treacy
Hi Sohpie,
I’d use a Pivot Table to summarise the data and automatically sort it. That way when you add new data it will automatically re-sort it. Make sure the PivotTable source data is formatted in an Excel Table so that when you add new data the PivotTable range automatically picks it up upon refresh.
If you get stuck please post your question and Excel sample file on our forum where we can help you further.
Mynda
Ram
Excellent article. A video could have been much better unless it is separately available
Terence
Great post, love the multiple options all explained thoroughly.
Just out of curiosity, on the offset example is there a reason for doing cell:offset cell rather than using the height functionality of the offset?
Mynda Treacy
Hi Terence,
Thanks for your kind words.
There’s no particular reason for creating the range using cell:offset vs using the height parameter inside of OFFSET. I think I was just being consistent with the approach used to write the INDEX function dynamic named range, and so I wrote the OFFSET formula that way to make it easier to compare the two functions.
Mynda
George Prattos
Hi Mynda,
After studying your website and trying things out in the process of getting to terms with dynamic ranges, I seem to have created dynamic range names without using any functions at all, i.e. without Offset, Index or Indirect. This came as a surprise…
Starting with a copy of your raw data (columns A:E) I did the following:
1. Made a table of the raw data, including headings (Table1).
2. In column B: selected all Salesperson data excluding the heading and created a range name SalesPax with it.
3. In column E: selected all Order Amount data excluding the heading and created range name OrderAmt with it.
Both these are dynamic range names by virtue of being table data.
4. In Total Sales cell H2 used the formula =SUMIF(Salespax,G2,OrderAmt) which the system accepted and converted to =SUMIF(Table1[Salesperson],G2,Table1[Order Amount]).
5. Copied H2 to cells H3 through H8.
I’ve tested this by adding and deleting both Salespersons and Order Amounts to/from the original data table and all Total Sales in column H update correctly.
I welcome feedback on this approach. Thank you.
Regards,
George
Mynda Treacy
Hi George,
Yep, that’s one of the features of Excel Tables. In fact you don’t even need to create named ranges. You can just refer to the table and columns using their Structured References. All explained here.
Note: sometimes formatting your data in an Excel table isn’t ideal. For example, they aren’t suitable for data over 500k rows.
Mynda
Bill Tastle
There is (surprisingly!) another way to create this range using INDIRECT.
=INDIRECT(“B2:B”&COUNTA(B:B)-1)
While this bypasses the OFFSET function, it does require one to grasp the use of INDIRECT. In short, INDIRECT converts any text address as an actual address. Thus, it cannot be used by itself for it generates a #VALUE! error, the same one as the OFFSET function when used by itself.
Mynda Treacy
Hi Bill,
That’s one of the great things about Excel. I would avoid using INDIRECT where ever possible though because it’s a volatile function and can cause performance issues in medium/large workbooks.
Mynda
Rajesh Sinha
Useful information,, cud u Plzzz post some samples, to Optimize data in Excel, for example PIPS data.
Mynda Treacy
Hi Rajesh,
Sorry, I’m not familiar with PIPS data.
Mynda
Phil
Thank you, this was very helpful
Tim Hale
I really like your explanation of the dynamic range name. Thank you for sharing.
Tim Hale
Mynda Treacy
Cheers, Tim. Glad I could help.
Luc Van Uffelen
I successfully applied the INDEX method in my files, but the named ranges disappear in the Name Box. What can be done to avoid this consequence of the INDEX method?
Mynda Treacy
Hi Luc,
Dynamic named ranges never appear in the name box. It’s just how it is I’m afraid.
Mynda
Ed
I’m missing something here. I don’t see the advantage of using a dynamic named range rather than an Excel table. It seems to me the whole point of tables is that they are dynamic and “automatically” generate named ranges without the user having to identify ranges or use complex formula.
Mynda Treacy
Hi Ed,
I too like using Excel Table’s dynamic structured references but Tables aren’t always an option so it’s good to know how to create a dynamic named range with formulas too.
For example, Tables can become very slow when they are a lot of formulas and are not always suitable for big data. They’re also not compatible with Excel 2003, although these days no one should be using Excel 2003 anymore 🙂
Kind regards,
Mynda
Mynda
Arthur Arkin
I am trying to replicate what you have shown in excel_manual_chart_table, but there is no way I can do it. You seem to assume that one would intuitively know how to do all the things you show but the instructions are not detailed enough. For example, when I create the tow pivots tables, where do I put them? I notice that the chart for one table is sorted differently, but there was no instruction to sort anything. Sorry, I’m a beginner with pivot tables and charts, and I need step-by-step instructions, with nothing left to the imagination. Yet, this particular operation is very important to me. Hope you can help.
Arthur
Mynda Treacy
Hi Arthur,
Great to see you’re having a go at replicating this technique.
If you’ve downloaded the file you’ll see where the PivotTables are in my file, however the location of them can be anywhere in the file. It makes no difference.
You don’t have to sort the PivotTable data, I just did because it’s useful to the chart reader. Sorting is easy, select a value (number) cell in the column you want to sort > right-click > sort > choose your sort order. You only need to sort the first PivotTable since the values from the second one are brought in using VLOOKUP and will therefore be sorted based on the first PivotTable.
Kind regards,
Mynda
david Ostreicher
Hi Mynda.,
These are great tools for dynamically naming ranges, and I love the way Tables are automatically dynamic.
What I’m trying to do though, is to use the offset or index function (based on a range in a separate sheet, and couple that with the table’s built in function, to make it automatically resize itself with the same amount of rows as my other range. That way, I imagine, the same way when you add data to the cell directly beneath a table, the table automatically resizes and copies down all the formulas of the other columns in the table, in this case, as I add a row to my “other” range on the other sheet, my Table will automatically add a line and copy down ALL formulas… even that for the first data point.
Sorry… I hope I was clear enough.
Thank you very much for all your help.
Mynda Treacy
Hi David,
Great to hear you’ll be making use of dynamic ranges.
I’m struggling to picture the dependencies in your workbook. Are you able to send me a sample file with your question so I can better understand, and also give you a tailored answer? You can send it via the Help Desk.
Kind regards,
Mynda
Joyce
Now when I think of all the old models where I used OFFSET in my dynamic named ranges I will dream about replacing it with INDEX based formulas as you outlined. Thank you for the tip!
Mynda Treacy
🙂 great to hear, Joyce.
Leah
Hi Mynda ~
For anyone who routinely updates named ranges this is amazing! Of course, now that I’m trying to apply it, I’m stumbling. My first test file uses a named range in a vlookup. The current named range encompasses 2 columns (the first one is the lookup value [employee id], the second is the return value [employee name]) with data starting in cell B4 (header included). I attempted to use this formula for the named range but it errors out –=Employee!$B$4:INDEX(=Employee!$B$4:$C$4500,COUNT(=Employee!$b$4:$c$4500)). Either this won’t work across multiple columns or I’ve messed up the syntax somewhere. Would you mind pointing me back in the correct direction? Thanks!
Mynda Treacy
Hi Leah,
It’s tricky to say for sure but, your formula contains = signs where they’re not required so let’s start by eliminating those so your formula looks like this:
Also, your INDEX formula is referencing B4:C4500 which is 2 columns, but you haven’t got a column_num argument in your INDEX formula. You either need to give it a column_num argument or make your range only 1 column wide.
Your COUNT function is also couting 2 columns: B4:C4500 you need to choose just the column you want to count, and if that column contains text as opposed to numbers then you need to use COUNTA instead of COUNT. COUNT only counts numbers.
If that doesn’t work then I’ll need you to send me the file via our Help Desk so I can see all of the contributing factors.
Kind regards,
Mynda
Leah
Thanks Mynda!
I modified the formula to this >> =Employee!$B$4:INDEX(Employee!$B$4:$C$4500,COUNT(Employee!$B$4:$B$4500),2) << and it worked! Absolutely fantastic. I can't wait to share it with my team.
Mynda Treacy
That’s great, Leah. You should give yourself a pat on the back for mastering dynamic ranges using INDEX. Well done.
Mynda
Kenneth J. Nessing
Mynda: Another home run, mate! The Dynamic Range info is priceless (actually, I’m sure we could agree on a price!). 😉
Ken
Mynda Treacy
Cheers, Ken. Just reaching for my calculator now 😉
Rebekah
I hv a question in setting range using table.
I hv a report which has 5 sections. Each section starts with a function, eg cell A10 = Admin. Each of the function has the same set chart of accounts.
Everymonth, I generate a new report, copy and paste the contents to a file which has formulae populating the data to a summary sheet in the same file.
The problem I hv is that when a new account is added to the chart of accounts, the range changes. I try to range each function using the table, unfortunately, when I copy and paste the new data, the table range seems hv removed.
Is there any other way to over come this problem?
thanks for your help.
rgds
Catalin Bombea
Hi Rebekah,
Looks like you are pasting data over the entire table, including headers, this operation will delete the table. You should not do that, for example, if the first row contain headers, paste data under the headers row, starting from A2. This way, if you have extra rows, the table will autoexpand to include new rows, and even new columns (the new columns headers will be named automatically to Column1, Column2)
Of course, this assumes that the data structure is the same for the new data, and new columns are added to the right side of the table, not between initial data structure.
If the headers row structure is not the same, to preserve the table you can paste new headers to headers row, after you paste the data.
Catalin
Maxime Manuel
Whoever from Microsoft Corp that invemted INDEX deserves a place in paradise. This function is gift from heaven.
Mynda Treacy
🙂 Indeed, Maxime.
Maxime Manuel
Minda, I never learne Excel but my level is very advanced. I am the Excel Guru at work, no joke. I can do most of the dashboards that I see online, I create my own, I helped all the departments in my organization using Excel, etc. I finally want to have a certification in advanced Excel with you guys. Can you send me the curriculum of your courses?
Another thing, what is the career of an Excel Guru? What should be his position in an organisation? What path should he follow? Please let me know. Thanks! 🙂
Mynda Treacy
Hi Maxime,
It’s great to hear you are helping people get more out of Excel.
You can see our course syllabuses from the Pricing menu, then select the course you’re interested in. Each page with have a link to the syllabus.
Excel Guru’s come in all shapes and sizes and there is no definitive path. For example; you are already a Guru in your workplace. Helping out in Excel forums is a great way to increase your Excel knowledge. Typically the questions you find in forums are unique and challenging.
All the best with your journey to Excel heights.
Kind regards,
Mynda.
Maxime Manuel
Thank you!
Vinay J
Hi Mynda, Really love your site. It has taught me so much already – especially the beautiful elegant way you have for explaining any topic. Such a contrast from the inbuilt Help with Excel.
Anyhow, I was wondering if you can help me out with a couple of queries on Dynamic Ranges and their application. Briefly, I have three worksheets (M,B,C) Each of them has an identical columnwise (15 columns) layout though the number of entries (rows) in each is different and is constantly growing. Hence I have made Dynamic Named Ranges (M,B,C) all with Workbook scope. Currently the number of rows of data (excluding Headers) for M is 25, for B is 60 and for C is 10.
Now I want to consolidate the three sheets such that all the data from M (25 rows across 15 columns) and B (60 rows across 15 columns) and C (10 rows across 15 columns) should be stacked one of top of the other i.e. excluding headers, row 1-25 should be M, row 26-86 should be B and row 87-97 should be C in a Master Sheet. Note-it should not sum the data but each individual row should be reflected in Master sheet such that Master sheet should have 97 rows and 15 columns of data. And thereafter as more and more rows of data are added to the 3 sheets, the Master sheet will incorporate all the entries (both old and new)
Is it even possible for Named Ranges to be displayed this way or can they only be displayed if some function is applied to them? Of course if it is not possible I would love to understand whether the issue is with the ranges being non-contiguous, being dynamic or their positioning on multiple worksheets or anything else. Intuitively I think it should be doable but I am unable to implement it.
VBA solution I have found is to create a temporary range which is a union of the three ranges but I would prefer to do it without VBA as multiple users will handle the document and are not as proficient/ comfortable.
Alternatively, a solution is to use multiple range/sheet inputs in Pivot tables – however, I am not able to get it to work as the page fields do not
reflect all the column headings the way it reflects when using a single input/sheet range. So solutions from this angle would also be welcome.
Thanks so much!
Keep up the AWESOMENESS!!
Mynda Treacy
Hi Vinay,
Thanks for your kind words.
In regards to your question; I don’t know of a way to create a 3D dynamic named range. I’ve never seen it done and I can’t think of a way you could do it without VBA.
I would make all of your worksheets uniform and use PivotTables.
You haven’t said what you need this dynamic range for. I wonder if this tutorial on 3D SUMIF might be relevant.
Kind regards,
Mynda.
Vinay J
No Mynda, 3D Sumif isn’t relevant for this instance, although it seems like an interesting thing to learn.
I am not sure I explained myself correctly. What I am looking for is a way to consolidate multiple ranges together without summing them or any function. Just display the raw consolidated data one below another.
All worksheets are already uniform in layout – unfortunately I am not able to use the Pivot tables with multiple ranges as inputs as the page fields do not reflect all the column headings the way it reflects when using a single input/sheet range. Perhaps you have some suggestions/ tips for using inputs from multiple ranges/ worksheets?
Mynda Treacy
Hi Vinay,
Yes, I know what you mean with consolidating PivotTables. Unfortnately the only other options are Copy and Paste all the data into one master worksheet, or find some VBA to do the copying and pasting for you.
Kind regards,
Mynda.
Theron P. Yates
dear all. this code copy cell step by step. do you know any other way to write the code easier?
Carlo Estopia
Hi Theron,
I really don’t think there can be any other, easier way of doing this code.
However, you may go to our HELP DESK and I’ll give you a file that could automate
some. Just specify what part of this topic explained do you want it to be
automated.
Cheers.
CarloE
Kevin
a quick tip I picked up from another site for data validation
Lets say we are working with Range A1:A10 and A1 has the header to our list
Select A1:A10 and insert table (check the box “mt table has headers”)
(A1:A10 is now a table)
Select A2:A10 and name the range
Insert Data Validation in whatever cell you need it and input the range you named in previous step
thats it 🙂 you now have a dynamic range
If you add data to cell A11 it should appear in your drop down list
Carlo Estopia
Hi Kevin,
Thanks for the tips.
Sincerely,
CarloE
Keith Thompson
Hi Mynda,
I have found the Dynamic Named Ranges using the INDEX function extremely useful, but have come across one small issue that you might be able to help me with. I have an excel based time-sheet for staff where they choose the site worked from a drop down list using Data Validation. As the number of sites is always changing I have named the site list using a dynamic named range using the INDEX function, but now find when you click on the drop down list you see the blank cells and have to scroll up each time to see the site list, is there a way around this?
Regards,
Keith
Carlo Estopia
Hi Keith,
It’s good to hear that you have found it useful. I tried to simulate and walked through the file and I had a hard time trying to get
the same problem you have.
Anyway, It would be better for you to send your file through HELP DESK so we can have a better look at it.
Sincerely,
CarloE
Mynda Treacy
Hi Keith,
First you need to create a list that doesn’t have blanks. Let’s say your list containing blanks is in cells A2:A9. In cell C2 enter this formula:
Enter with CTRL+SHIFT+ENTER as this is an array formula, and copy down as far as you need.
Then set up your dynamic named range referring to cells C2:??, let’s say C2:C1000 using this OFFSET formula:
Or using INDEX:
Kind regards,
Mynda.