The OFFSET function
A range defined by a starting point, an offset and a size: the last 12 months, a range that grows with the data. At the price of a slower workbook.
Syntax
=OFFSET(reference, rows, cols, [height], [width])
In a French Excel: =DECALER(réf; lignes; colonnes; [hauteur]; [largeur]).
Arguments
- reference: the starting cell.
- rows, cols: the offset.
- height, width: the size of the returned range.
An example
=SUM(OFFSET(B1,COUNTA(B:B)-12,0,12,1))
The sum of the last 12 values of column B.
Pitfalls
- Volatile: recalculated on every change to the workbook; in numbers, it slows it down.
- Unreadable: a calculated range cannot be seen, and is hard to check.
Going further
- INDEX, which does the same without being volatile:
=SUM(INDEX(B:B,COUNTA(B:B)-11):INDEX(B:B,COUNTA(B:B))). - The formula audit: spots volatile functions.
Frequently asked questions
- Why does my workbook crawl with OFFSET?
- Every change, even elsewhere, reruns every OFFSET and everything that depends on it. A structured table or INDEX are usually enough.