The Excel OFFSET function returns a reference that is a given number of rows and columns away from a starting cell — and can optionally resize that reference into a whole range. It powers dynamic ranges and rolling calculations, but it is volatile: it recalculates on every worksheet change, so heavy use can slow workbooks where INDEX would not.
height and width to return a range instead of a single cell — e.g. =SUM(OFFSET(A1, 1, 1, 3, 1)) sums the 3-cell column starting at B2.
Syntax
| Argument | Description | |
|---|---|---|
reference | Required | The starting cell or range that the offset is measured from. |
rows | Required | Rows to move down (positive) or up (negative) from the top-left of reference. 0 = stay in the same row. |
cols | Required | Columns to move right (positive) or left (negative). 0 = stay in the same column. |
height | Optional | Number of rows in the returned reference. Defaults to the height of reference. Must be positive. |
width | Optional | Number of columns in the returned reference. Defaults to the width of reference. Must be positive. |
OFFSET is volatile. It recalculates every time anything in the workbook changes — not just its inputs. A handful of OFFSETs is harmless; thousands can make a workbook crawl. INDEX can build the same dynamic references without volatility.
Worked examples: single cells and rolling ranges
OFFSET never moves anything — it just points somewhere. From the header cell B1 below, moving down 3 rows and 0 columns lands on March’s sales:
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | $8,200 |
| 3 | Feb | $7,900 |
| 4 | Mar | $8,600 |
| 5 | Apr | $9,100 |
| 6 | May | $9,800 |
| 7 | Jun | $10,400 |
The real power is the height/width pair, which turns the result into a range other functions can consume. A rolling “last 3 months” total anchored to the bottom of the list:
And a version that finds the bottom automatically, so it keeps working as rows are added:
Try it: interactive OFFSET demo
Drag the sliders and watch which cells =OFFSET(A1, rows, cols, height, width) grabs. The starting cell A1 is outlined in green. Push the range off the grid to see #REF!.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 10 | 20 | 30 | 40 | 50 |
| 2 | 11 | 21 | 31 | 41 | 51 |
| 3 | 12 | 22 | 32 | 42 | 52 |
| 4 | 13 | 23 | 33 | 43 | 53 |
| 5 | 14 | 24 | 34 | 44 | 54 |
| 6 | 15 | 25 | 35 | 45 | 55 |
OFFSET vs INDEX: the non-volatile alternative
Almost everything OFFSET does, INDEX can do without volatility. INDEX with a row number returns a reference too, so it can anchor dynamic ranges:
Prefer OFFSET when the shape of the range must move and resize from a single anchor (rolling windows, moving averages) and the workbook is small. Prefer INDEX — or, in Excel 365, dynamic arrays like FILTER and TAKE/DROP — everywhere else.
Dynamic named ranges: the classic pattern =OFFSET($A$2,0,0,COUNTA($A:$A)-1,1) inside Name Manager makes charts and validation lists grow with the data. In Excel 365, a spilled range reference like A2# does the same job non-volatilely.
Errors & common pitfalls
#REF! — the offset falls off the sheet. Moving up past row 1 or left past column A (e.g. =OFFSET(A1, -1, 0)) returns #REF! because the resulting reference doesn’t exist.
#VALUE! — height or width of 0 or a non-number. Both must be positive whole numbers when supplied. height/width of zero is invalid.
Pitfall: a bare OFFSET range shows #VALUE! in one cell. =OFFSET(A1,1,0,3,1) returns a 3-cell range — pre-365 Excel can’t display that in a single cell. Wrap it in SUM, AVERAGE, COUNT, etc., or let Excel 365 spill it.
Pitfall: volatility cascades. Every cell that depends on an OFFSET also recalculates on every change. Trace long chains before blaming Excel for slowness — and consider INDEX.
Pitfall: OFFSET hides its target. Formula auditing (Trace Precedents) can’t follow OFFSET’s computed reference, and inserting rows won’t adjust the rows/cols numbers. Document what the offsets mean.
Practice workbook
Frequently asked questions
Why is OFFSET considered slow?
Can OFFSET use negative numbers?
rows moves up and negative cols moves left. =OFFSET(B7, -2, 0, 3, 1) starts 2 rows above B7 and returns B5:B7. You get #REF! only if the result would leave the sheet.Does OFFSET return a value or a reference?
How do I build a dynamic range with OFFSET?
=OFFSET($A$2, 0, 0, COUNTA($A:$A)-1, 1) — anchored at A2, as tall as there are entries. In Excel 365 prefer a spilled reference like A2# or an Excel Table, which grow without volatility.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class