MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Use A2:INDEX() as a Non-Volatile OFFSET


October 07, 2019 - by Bill Jelen

Excel Use A2:INDEX() as a Non-Volatile OFFSET. Photo Credit: Markus Spiske at Unsplash.com

There is a flexible function called OFFSET. 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.

There are names in A2:A7. Four quarters of sales stretch across B:E for each name. Over in H1, you enter the number of quarters you want included. The Average formula in F2 uses =AVERAGE(OFFSET(B2,0,0,1,$H$1)).

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 spend 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.


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. It is wild that this works. 

Replace the OFFSET function in F2 with =AVERAGE(B2:INDEX(B2:E2,$H$1)).

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.

Title Photo: Markus Spiske at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.