Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
INDEX vs OFFSET: stable vs volatile references in Excel and Sheets
Excel & Google Sheets
=INDEX(A2:A6,3)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Sales |
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
=INDEX(A2:A6,3)→30
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1INDEX(A2:A6, 3) returns the 3rd value in the range — here 30.
- 2OFFSET(A2, 2, 0) returns the same 30 by stepping 2 rows down from A2.
- 3Prefer INDEX: it gives the identical result without OFFSET's volatile recalculation overhead.
Tips
- Use OFFSET only when you genuinely need a resizable block (height/width arguments).
- On large models, replacing OFFSET with INDEX noticeably speeds up recalculation.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
What does "volatile" mean?
A volatile function recalculates every time anything in the workbook changes, even unrelated cells. OFFSET, INDIRECT and TODAY are volatile; INDEX is not.
Can INDEX return a whole row or column?
Yes — INDEX(range, 0, col) returns an entire column and INDEX(range, row, 0) a whole row, which you can sum or feed to another function.