MrExcel.com - Your One Stop for Excel Tips & Solutions

Learn Excel Replace OFFSET with INDEX

October 13, 2017 - by

Learn Excel Replace OFFSET with INDEX

Excel's OFFSET function will slow your workbook's calculation. There is a better alternative: an unusual syntax of INDEX.

This is a niche tip. There is an amazingly flexible function called OFFSET. It is flexible because it can point to a different-sized range that is calculated on-the-fly. In the image below, if someone changes the # Qtrs dropdown in H1 from 3 to 4, the fourth argument of OFFSET will make sure that the range expands to include four columns.

Using OFFSET Function
Using OFFSET Function

Spreadsheet gurus hate OFFSET because it is a volatile function. If you go to a completely unrelated cell and enter a number, all of the OFFSET functions will calculate. Even if that cell has nothing to do with H1 or B2. Most of the time, Excel is very careful to only waste time calculating the cells that need to calculate. But once you introduce OFFSET, all of the OFFSET cells, plus everything downline from the OFFSET, starts calculating after every change in the worksheet.

Illustration Credit: Chad Thomas
Illustration Credit: Chad Thomas

I was judging the 2013 ModelOff finals in New York City when a few of my friends from Australia pointed out a bizarre workaround. In the formula below, there is a colon before the INDEX function. Normally, the INDEX function shown below would return the 1403 from cell D2. But when you put a colon on either side of the INDEX function, it starts returning the cell address D2 instead of the contents of D2. This is wild that it works.

Using INDEX Function
Using INDEX Function

Why does this matter? INDEX is not volatile. You get all of the flexible goodness of OFFSET without the time-sucking recalculations over and over.

I first learned this tip from Dan Mayoh at Fintega. Thanks to Access Analytic for suggesting this feature.

Watch Video

Video Transcript

Learn Excel from MrExcel podcast, episode 2048 - :INDEX Will Replace a Volatile OFFSET!

Hey, I'm podcasting all of my tips from this book, click that “i” in the top-right hand corner to get to the playlist!

Alright, OFFSET is an amazing function! OFFSET allows us to specify a top left corner cell, and then use variables to define from there how many rows down, how many rows over, and then define a shape, alright. So here, if I want to add up, or do an average of, let's say 3/4, this formula right here will take a look at the formula. The OFFSET says we're starting from B2, starting from Q1, we're going down 0, over 0, the shape will be 1 row tall, and it will be H1, in other words 3 cells wide, alright. So in this case all we're really doing is changing how many cells we're adding up, we're always starting back in B2, or B3 or B4 as I copy down, and then it decides how many cells wide. Alright, OFFSET is this cool thing, it does all kinds of amazing functions, but here's the hassle, it is volatile! Which means that even if something's not in the calculation chain, Excel, is going to take the time to recalculate it, which is going to slow things down, alright so.

This amazing version of INDEX, right, normally if I would ask for the INDEX of these 4 cells ,3 , it's going to return the number 1403. However, when I put a colon either before or after the INDEX, something very different happens, we'll take a look at this formula here. So index of the 4 cells, which one do I want, I want the 3rd one, but you see there's a : right there. So we're always going to go from B2:E2, and I'll show you if we go to Formulas, Evaluate Formula, alright, so right here it's going to calculate the number 3. And here, the INDEX with the : next to it, instead of telling us 1403, which is how INDEX normally would calculate, it's going to return $D2, and then the AVERAGE will do the average of those 3. Absolutely amazing, the way that this works, and the added benefit, it is not volatile, alright, and this can even be used to replace an incredibly complex OFFSET.

So here with this OFFSET we're based on these values. If I choose Q2 and Central, alright, these formulas are using MATCH and COUNTIF to figure out how many rows down, how many columns over, how tall, how wide. And then the OFFSET here is using all of those values to figure out the median. We'll just do a test to make sure that it is working, so =MEDIAN of that blue range over there, better be 71, alright, and we'll choose something else here, Q3 and West, so. Press F2, I'm just going to drag this over and resize it to rewrite that formula, press Enter, and it's working with the OFFSET.

Well here, using an INDEX, I actually have a colon in the middle with an INDEX on the left side and an INDEX on the right side, watch this thing get calculated in Evaluate Formula. So it starts out, will Evaluate, Evaluate, and right here that INDEX is about to turn it into a cell address. So H16 is the 1st, West, Q3, and over on the right side will evaluate, couple more, and then right her it changes to I20. So the cool, cool non-volatile to replace an OFFSET using the INDEX function. Alright, this tip and many more in this book, click that “i” in the top-right hand corner to buy the book.

Alright recap: OFFSET, awesome, flexible function, once you master, you can do all kinds of things. Point to a variable top-left cell, point to a range that has a variable shape, but it's volatile, and so they calculate at every calculation. Excel usually does a smart calculation, or it recalculates the cells that need to be calculated, but with OFFSET it will always get calculated. Instead of OFFSET you can use INDEX: or :INDEX or even INDEX:INDEX, anytime INDEX has a colon next to it, it will return a cell address instead of the value of that cell. And the benefit is INDEX is not volatile!

OK, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!

Download File

Download the sample file here: Podcast2048.xlsm

Title Photo: Pixabay