OFFSET returns a range a set number of rows and columns away, optionally with a new size. Classic for rolling totals and dynamic chart ranges.
Syntax
=OFFSET(reference, rows, cols, [height], [width])
Argument
What it means
rows, cols
How far to move.
height, width
Size of the returned range.
Examples
Example 1
=SUM(OFFSET(B1, COUNT(B:B)-2, 0, 3, 1))
Sum of the last 3 values in column B.
💡 OFFSET is volatile — it recalculates on every change and can slow big files. INDEX-based ranges are faster: =SUM(INDEX(B:B,COUNT(B:B)-1):INDEX(B:B,COUNT(B:B)+1)).
