OFFSET Function
Returns a reference a set number of rows and columns away from a starting point.
Category
Lookup & Reference
Available in
All versions, Microsoft 365
Returns
A reference
Overview
OFFSET starts at a cell, moves down and across by the amounts you give it, and returns a reference there — optionally resized to a block of cells.
It's often used for rolling ranges (e.g. 'the last 3 months'). Like INDIRECT, OFFSET is volatile and recalculates on every change.
Syntax
=OFFSET(reference, rows, cols, [height], [width])
Arguments
| Argument | Required? | What it does |
|---|---|---|
| reference | Yes | The starting cell or range. |
| rows | Yes | Rows to move. Positive = down, negative = up. |
| cols | Yes | Columns to move. Positive = right, negative = left. |
| height | Optional | Height of the returned range. |
| width | Optional | Width of the returned range. |
Examples
Example 1 — Moving from a starting cell
Start at A1, go down 2 and right 1.
B5fx=OFFSET(A1, 2, 1)
| A | B | |
|---|---|---|
| 1 | Rep | Sales |
| 2 | Ana | 400 |
| 3 | Ben | 350 |
| 4 | ||
| 5 | Result | 350 |
=OFFSET(A1, 2, 1) Explain
Example 2 — Sum of the last 3 entries
Column B has monthly sales from B2 downward with no gaps.
=SUM(OFFSET(B2, COUNT(B2:B100) - 3, 0, 3, 1)) Explain
- COUNT finds how many months are filled, e.g. 10.
- OFFSET moves down 7 rows from B2 and returns a 3-row block: B9:B11.
- SUM adds those three months.
For non-volatile alternatives, try TAKE (=SUM(TAKE(B2:B100, -3)) on a trimmed range) or INDEX.
Common mistakes
- #REF! errors. The offset lands outside the worksheet (e.g. above row 1).
- Slow recalculation. Volatile functions recalculate constantly in large models.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.