Back in March I did a post on three marketing uses for the OFFSET function. But, truth be told, I avoid the OFFSET function like the plague. The reason is that it’s very unstable and can bring large Excel files to their knees.
Why? Every time you modify the file the OFFSET function takes that as a cue to go ahead and run again.
It’s not alone. There are a few more functions that are volatile in this way, namely:
- NOW()
- TODAY()
- CELL()
- INDIRECT()
- ROWS()
- COLUMNS()
REMIX!
So what I decided to do was take all of the marketing uses I demonstrated in that video and show the INDEX alternative.
Demo File
If you’d like to see how these look out in the wild, you can download the Excel file I used.
Learn More
To learn about Excel functions — and more — check out out my Annielytics Dashboard Course offerings.
Thanks for the wonderful understanding information. Will require some more guidelines from you on excel and analytic.
You’re very welcome! The [data] doctor is in. https://www.annielytics.com/services/ 🙂
Wonderful understanding. Thanks a lot
My pleasure! Glad it helped.
Fantastic – very clear and super useful.
Thanks!
Great post — I’ve been using the offset function a lot but haven’t changed it for fear of destroying all of my hard work. While I use pivot tables a lot, sometimes I just like using a plain table for certain reports, which makes using the offsetfunction crucial when dealing with a lot of data. I will be trying out the index function this week! Thanks 🙂
Your spreadsheets will thank you!. 🙂
This really helped me resolve a very slow trading dashboard. Much appreciated
Excellent! Happy to help!
Hi Annie. Great tutorial.
I have a large (and very sluggish) workbook that uses many dynamic ranges with OFFSET. Recoding it with INDEX will be a lot of work. I am inclided to do this but you warn against not going overboard with extended range. In my case, the number of data rows could be 30,000. So I would have to use a very large range to cover this. Would the performance still be improverd with such large ranges?
Thanks! There’s no way for me to know without testing it, unfortunately. There are so many factors involved.
Well I tried it Annie. Changed all the OFFSETs to INDEX equivalents and it made a major improvement in performance. Thanks for the tip.
BTW, I used the most extreme extension flexibility. My dynamic range extends potentially to the full size of the worksheet. Here is a sample:
Data refrers the the range Data!$A$3:INDEX(Data!$3:$1048576,COUNTA(Data!$A:$A),COUNTA(Data!$3:$3))
This selects names the range “Data” to be all rows from 3 and below until the first blank, and similarly all the columns from A until the first blank. Since my header row and first column have no missing data cells, this selects my entire table of data. I name some columns as follows:
Account_Name refers to the range
Data!$D$4:INDEX(Data!$D$4:$D$1048576,COUNTA(Data!$A:$A)-1,1)
(starting at row 4 to remove the header)
Works great! Thanks again.
If your data is in an excel Table, then you can reference its entire extent by selecting the Table, and it will automatically extend the range as you add more rows, or columns. Problems I have found with the dynamic range using count, is if you forget the count is there and accidentally add some data in those rows that don’t belong in the data table.
I have used this for Pivot ranges and your data set suddenly includes blanks column titles and crashes. Tables are just a bit more resilient.
Great! Glad it helped.
Also, I note that in your tutorial at 7:28, you show that the range reference when entered in the Excel worksheet returns a #Value! error. I don’t get that on my Mac (Office 365). I get the first cell of the array referenced. I can see the whole array of course by using the forumla editor, selecting the formula, and hitting Cmd + “=”. How come you get an error and I get the array?
I have no idea. Maybe it’s a difference with how 2016 processes an array function.