Formula Guide

The Excel OFFSET Function
explained simply

OFFSET returns a reference to a range that is a given number of rows and columns away from a starting cell — the basis of many dynamic ranges.

ExcelPro · 5 min read · Updated June 2026
Contents
  1. What does OFFSET do?
  2. Syntax
  3. 4 examples
  4. Performance warning
  5. Common mistakes
  6. FAQ

What does OFFSET do?

OFFSET starts at a reference cell, then moves a specified number of rows and columns away, and can also resize the result into a range rather than a single cell.

It is most often used to build ranges that shift automatically — like a rolling 12-month total that always points at the most recent 12 months as new data is added.

Syntax

=OFFSET(reference, rows, cols, [height], [width])
ArgumentDescription
reference requiredThe starting cell or range.
rows requiredHow many rows to move down (positive) or up (negative).
cols requiredHow many columns to move right (positive) or left (negative).
height optionalHow many rows tall the result should be. Defaults to the height of reference.
width optionalHow many columns wide the result should be. Defaults to the width of reference.
⚠️ OFFSET is volatile

OFFSET recalculates every time anything on the sheet changes, even cells unrelated to it. Large numbers of OFFSET formulas can noticeably slow down big spreadsheets.

Examples

Example 1
Move down 2 rows
=OFFSET(A1,2,0)

Starting at A1, move 2 rows down and 0 columns across — returns the value in A3.

Example 2
Rolling 3-month sum
=SUM(OFFSET(B4,-2,0,3,1))

Starting at B4, move up 2 rows, then build a 3-row-tall, 1-column-wide range — sums the current cell and the two above it.

Example 3
Dynamic named range
=OFFSET(A1,0,0,COUNTA(A:A),1)

A common trick: build a range that automatically grows as more rows of data are added to column A.

Example 4
Shift a column over
=OFFSET(A1,0,2)

Moves 0 rows down, 2 columns right — returns the value in C1.

Performance warning

OFFSET is a volatile function, meaning Excel recalculates it on every single change to the workbook, not just when its own inputs change. In a small sheet this is invisible. In a large model with hundreds of OFFSET formulas, it can cause real slowdowns.

Where possible, modern alternatives like dynamic arrays, structured Tables, or INDEX (which is not volatile) achieve the same dynamic-range result without the performance cost.

Common mistakes

⚠️ Overusing OFFSET in large models

Each volatile OFFSET formula adds to recalculation time. Prefer INDEX-based dynamic ranges in performance-sensitive sheets.

⚠️ Forgetting height/width default to the reference size

If reference is a single cell and you omit height/width, the result stays a single cell — easy to assume it auto-expands when it does not.

⚠️ Negative rows/cols confusion

Positive numbers move down/right, negative numbers move up/left — easy to get backwards under pressure.

FAQ

Is INDEX a faster alternative to OFFSET?
Yes — INDEX can build similarly dynamic ranges without being volatile, so it recalculates only when its actual inputs change.
Can OFFSET return an error?
Yes, #REF! if the resulting reference would fall outside the worksheet boundaries.

Practise OFFSET with real data

ExcelPro has hands-on OFFSET exercises built into real job scenarios — free to start.

Try OFFSET exercises →

Related formulas

INDEXMATCH INDIRECT SUM