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 executed in every LibreOffice version we test, and in our dated Google Sheets run; Excel per docs) |
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 (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
Ordinary use agrees everywhere — OFFSET's height/width resizing and the INDEX:INDEX range syntax behave the same in every engine we execute. The out-of-range cases do not: OFFSET off the top of the sheet returns #VALUE! in LibreOffice where Microsoft documents #REF! (our executed Google Sheets run returns #REF!), and an out-of-range INDEX returns #NUM! in our executed Google Sheets run where Excel documents, and LibreOffice returns, #REF!. Desktop Excel behavior is per Microsoft's docs — we do not run desktop Excel. The volatility difference is cross-app: every engine we execute treats OFFSET (and INDIRECT) as volatile. Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 9 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
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.