OFFSET vs INDEX: same tricks, very different cost
Both can build references that move and resize — the difference is that OFFSET is VOLATILE: it recalculates on every change anywhere in the workbook, and everything downstream of it recalculates too. INDEX does most of the same jobs without that tax, which is why 'replace OFFSET with INDEX' is a standard performance fix for slow sheets.
The differences at a glance
| OFFSET | INDEX | |
|---|---|---|
| Returns | A shifted/resized RANGE reference | A cell value — or a range when used as INDEX(rng,0,col) / range-end syntax |
| Volatile (recalcs on every edit) | Yes — plus everything that depends on it | No — recalcs only when its inputs change |
| Dependency tracking | Opaque — audit tools can't see what it points at | Normal, traceable references |
| Dynamic window (last N rows) | Classic idiom: OFFSET(A2,COUNT(...)-n,0,n,1) | INDEX(A:A,COUNT(...)-n+1):INDEX(A:A,COUNT(...)) — non-volatile equivalent |
| Resizable in-place (height/width args) | Yes — its unique convenience | Via the range-of-two-INDEXes pattern |
| Compatibility | Universal | Universal (both verified in every version of all three apps we test) |
Which should you use?
- OFFSET — Small sheets where its compact height/width arguments read clearly (rolling windows, resizable named ranges) and the volatility cost is negligible.
- INDEX — Anything large or shared: the INDEX:INDEX range trick covers dynamic windows without volatile recalcs, keeps dependency tracing intact, and — combined with MATCH or with modern TAKE/DROP — replaces nearly every OFFSET construction.
Compatibility (from executed tests)
Both execute identically in every version of Excel, Google Sheets, and LibreOffice we test, including OFFSET's height/width resizing and the INDEX:INDEX range syntax. The volatility difference is also cross-app: all three engines treat OFFSET (and INDIRECT) as volatile.
Example formulas
| Rolling last-3 average with OFFSET (volatile) | =AVERAGE(OFFSET(A2,COUNT(A2:A1000)-3,0,3,1)) |
| Same window, non-volatile INDEX range | =AVERAGE(INDEX(A:A,COUNT(A2:A1000)-1):INDEX(A:A,COUNT(A2:A1000)+1)) |
| Modern replacement (365 / LO 25.8+) | =AVERAGE(TAKE(FILTER(A2:A1000,A2:A1000<>""),-3)) |
Full per-version details on each function page: OFFSET · INDEX.