Returns a range shifted from a starting cell by rows and columns, optionally resized. Dynamic ranges, rolling windows, and "last N rows" all use it. It is volatile, so it recalculates constantly.

Syntax

=OFFSET(reference, rows, cols, [height], [width])

• Shift a reference

  =OFFSET(A1,2,1) → 25

  Two rows down and one column right of A1 is B3.

• Resize a range

  =SUM(OFFSET(B2,0,0,3,1)) → 50

  A 3-row, 1-column block starting at B2: 10 + 25 + 15.

• Last three rows

  =SUM(OFFSET(B1,COUNTA(B:B)-3,0,3,1)) → 110

  The last three prices: 15 + 40 + 55.

• Volatile and hard to audit. INDEX can usually do the same job.
• Rolling 12-month total: =SUM(OFFSET(B1,COUNTA(B:B)-12,0,12,1)).
• INDEX-based ranges (=B2:INDEX(B:B,COUNTA(B:B))) do the same without volatility.
• Height and width can be negative to extend upward or leftward.

• #REF! when the offset points outside the sheet.
• Volatile: large models with many OFFSETs recalculate slowly.
• The result is a reference, so it shows #VALUE! when a single cell expects a multi-cell range.