Excel OFFSET Function
OFFSET returns a cell or range reference shifted from a starting reference.
Use it when a formula needs a dynamic range, a moving window, or a cell picked by row and column movement from a known anchor.
OFFSET syntax & arguments
Syntax
=OFFSET(reference, rows, cols, [height], [width])
-
1
reference
RequiredThe starting cell or range Excel should move from.
-
2
rows
RequiredHow many rows to move from
reference. Positive values move down, negative values move up, and zero stays on the same row. -
3
cols
RequiredHow many columns to move from
reference. Positive values move right, negative values move left, and zero stays in the same column. -
4
height
OptionalThe number of rows in the returned reference. If omitted, Excel uses the height of
reference. -
5
width
OptionalThe number of columns in the returned reference. If omitted, Excel uses the width of
reference.
Example
=OFFSET(A1, 2, 3)
Return a reference to the cell three columns right and two rows below A1.
OFFSET caveats
-
It returns a reference
OFFSET points to a shifted cell or range. In a normal formula cell, Excel displays the value(s) at that reference; inside functions like SUM or AVERAGE, the receiving function uses the referenced range.
-
The shifted reference must exist
If the movement points outside the worksheet grid, OFFSET returns a
#REF!error. -
Height and width resize the result
heightandwidthcontrol the size of the returned range, not how far the anchor moves. -
It is volatile
OFFSET recalculates whenever Excel recalculates the workbook, which can slow down large models.
Need position-based lookup? INDEX with MATCH is often clearer when labels should determine which row or column is returned.
Intro OFFSET practice problems
No intro OFFSET problems are currently available.
Advanced OFFSET practice problems
Use OFFSET alongside other Excel functions in realistic, less-prescriptive challenges.
