OFFSET starts from one cell, moves a number of rows and columns, and returns a range of the size you choose. Because the range can move, it is useful for rolling totals and ranges that grow with the data.
Syntax
=OFFSET(reference, rows, cols, [height], [width])
Simple example
=OFFSET(A1, 2, 1) starts at A1, moves 2 rows down and 1 column right, and returns the value in B3.
Rolling last 3 months total
Monthly sales in B2:B13, and the latest month number in D1. This adds the last three months:
=SUM(OFFSET(B1, D1-2, 0, 3, 1))
When D1 changes from 6 to 7, the total moves one month forward.
Range that grows with the data
=SUM(OFFSET(B2, 0, 0, COUNTA(B:B)-1, 1))
New rows are included automatically.
Important warning
OFFSET is a volatile function. It recalculates every time anything changes in the workbook. In a big file with thousands of OFFSET formulas, Excel becomes slow. For the same job, INDEX is usually faster:
=SUM(B2:INDEX(B:B, COUNTA(B:B)))
Better still, convert your data to a Table, which grows by itself.