Practice free

OFFSET function

OFFSET returns a reference a set distance from a starting cell, optionally resized. It is used for dynamic ranges, though INDEX is usually preferred because OFFSET is volatile and slows large workbooks.

Example

Read it as a sentence: start at the Item heading in B1, move down 2 rows and right 1 column, then return that cell's value, which gives Smoking.

Practice this in the app

Syntax

OFFSET(reference, rows, cols, [height], [width])

Arguments in [square brackets] are optional.

reference (required)
Starting cell or range.
rows (required)
Rows to move; positive down, negative up.
cols (required)
Columns to move; positive right, negative left.
height (optional)
Height in rows of the result.
width (optional)
Width in columns of the result.

Returns: A reference moved and sized from the start.

OFFSET is volatile: Excel works it out again every time anything in the workbook changes.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=OFFSET(Items!B1,2,1) Smoking Cell offset
=SUM(OFFSET(Items!D1,1,0,6,1)) 68 Sized range
=OFFSET(Items!B1,1,0,2,2) Boxer cartridge Range spills
=OFFSET(Items!D3,-1,0,-2,1) Qty Negative height from C2
=OFFSET(Items!B1,"2","1") Smoking Text numbers
=OFFSET(Data!F1,0,0) 0 Blank target
=OFFSET(Items!B1,-1,0) #REF! Off the sheet
=OFFSET(Items!B1,0,0,0,1) #REF! Height 0

The Items sheet these formulas read

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Volatile; slows large workbooks.
  • Precedent tracing does not follow it well.
  • Out-of-sheet results give #REF!.

Where it works

Excel
OFFSET is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .