Lookup & reference
How to Use OFFSET in Excel (Dynamic Ranges)
OFFSET returns a reference that is shifted a set number of rows and columns from a starting cell, and can be a single cell or a whole block. It is the classic tool for building ranges that resize themselves as data is added.
It is powerful but volatile, meaning it recalculates on every change, so use it deliberately. This guide covers the exact syntax, real worked examples, and the pitfalls to watch for.
OFFSET syntax
=OFFSET(reference, rows, cols, [height], [width]) | Argument | Required | What it does |
|---|---|---|
reference | Required | The starting cell or range to move from. |
rows | Required | How many rows to move down (positive) or up (negative) from the reference. |
cols | Required | How many columns to move right (positive) or left (negative) from the reference. |
height | Optional | How many rows tall the returned range should be. Defaults to the height of reference. |
width | Optional | How many columns wide the returned range should be. Defaults to the width of reference. |
Note: OFFSET is a volatile function: it recalculates on every edit, which can slow large workbooks. Where possible, prefer INDEX for static lookups. Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Move to a single cell
=OFFSET(A1, 2, 1) Starts at A1, moves down 2 rows and right 1 column, and returns the value in B3. With no height or width, it returns a single cell.
2. Sum a dynamic range that grows with your data
=SUM(OFFSET(A2, 0, 0, COUNT(A:A), 1)) OFFSET starts at A2 and builds a block 1 column wide and COUNT(A:A) rows tall. As you add numbers the range expands automatically, so the SUM stays correct.
3. Grab the last value in a column
=OFFSET(A1, COUNT(A:A), 0) Counting the numbers in column A gives the offset to the last filled row, so this always returns the newest entry, perfect for a running latest reading.
4. Build a rolling last-3 total
=SUM(OFFSET(A1, COUNT(A:A) - 3, 0, 3, 1)) OFFSET jumps to 3 rows before the end of the data, then returns a 3-row block for SUM to total, so the window slides as new rows arrive.
How to write OFFSET step by step
- 1
Click the result cell and type =OFFSET(
- 2
Click the starting cell (the anchor you measure from), then a comma.
- 3
Type how many rows to move down, then a comma, then how many columns to move right.
- 4
Optionally add a comma and a height (rows), then a comma and a width (columns) to return a block instead of one cell.
- 5
Close the bracket ) and press Enter. Wrap it in SUM, AVERAGE, or COUNT to work on the whole block.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#REF! | The offset pushes the reference off the edge of the worksheet, for example moving up past row 1. | Reduce the rows/cols values, or change the starting reference so the result stays inside the sheet. |
#VALUE! | height or width is 0, negative, or non-numeric. | Use positive whole numbers for height and width, or omit them to return a single cell. |
Slow / constantly recalculating workbook | OFFSET is volatile, so many OFFSET formulas recalculate on every edit. | Replace static lookups with INDEX, or limit how many OFFSET formulas you use in one sheet. |
#N/A or wrong total | COUNT misses text headers or blank cells, so the dynamic height is off by a row. | Use COUNTA for text lists, and start the count below the header row to keep the range aligned. |
Frequently asked questions
Is OFFSET a volatile function?
Yes. OFFSET recalculates every time any cell in the workbook changes, even if its result has not changed. A few are fine, but hundreds of OFFSET formulas can slow a large workbook noticeably.
Should I use OFFSET or INDEX?
For a fixed lookup, INDEX is usually better because it is not volatile and is just as flexible. Reach for OFFSET when you specifically need a range that resizes, such as a dynamic chart source or a rolling window.
Can OFFSET return a range instead of one cell?
Yes. Supply the height and width arguments to return a block, then wrap it in SUM, AVERAGE, or COUNT. On its own, a multi-cell OFFSET only spills in modern Excel.
Does OFFSET work in Google Sheets?
Yes. OFFSET works identically in Google Sheets, including the optional height and width arguments for returning a block.