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.
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.
| Formula | Excel shows | Case |
|---|---|---|
=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 .