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

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · Reference Functions module

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

ArgumentRequired?What it does
referenceYesThe starting cell or range.
rowsYesRows to move. Positive = down, negative = up.
colsYesColumns to move. Positive = right, negative = left.
heightOptionalHeight of the returned range.
widthOptionalWidth 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)
AB
1RepSales
2Ana400
3Ben350
4
5Result350
=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
  1. COUNT finds how many months are filled, e.g. 10.
  2. OFFSET moves down 7 rows from B2 and returns a 3-row block: B9:B11.
  3. 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

Kyle Stott

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.