Excel OFFSET Function

OFFSET returns a reference to a range, starting from a given cell and shifted by however many rows and columns you specify. It doesn't move anything on the sheet — it just tells Excel "look here instead." The catch: OFFSET recalculates every time anything changes anywhere in the workbook, not just when its own inputs change. That volatility is the tradeoff for the flexibility, and it's the first thing to understand before you build OFFSET into anything large.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and every earlier version back to Excel 97. OFFSET has been part of Excel's core function set since long before dynamic arrays existed.

Syntax

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

Parameter	Required	Description
reference	Yes	The cell or range OFFSET measures from. Can be a single cell or a multi-cell range.
rows	Yes	How many rows to shift from reference. Positive moves down, negative moves up, 0 stays put.
cols	Yes	How many columns to shift from reference. Positive moves right, negative moves left, 0 stays put.
height	No	How many rows the returned reference spans. Defaults to the row count of reference if omitted.
width	No	How many columns the returned reference spans. Defaults to the column count of reference if omitted.

Basic Example

You're tracking weekly sales starting at C5, where each row is a week and column D holds units sold. You need Week 5's units figure without typing "D9" directly into the formula:

=OFFSET(C5, 4, 1)

// C5  — the anchor cell, Week 1 revenue
// 4   — moves down 4 rows, landing on Week 5
// 1   — moves right 1 column, landing on the Units column

OFFSET starts at C5, moves down four rows and right one column, and returns whatever's in D9. Change the 4 to 9 and the formula follows automatically to Week 10 — no cell reference to rewrite.

How OFFSET Works

OFFSET returns a reference, not a value

This is the detail that separates OFFSET from almost every other function. It hands back a range, which means you can nest it inside SUM, AVERAGE, or COUNT, or use it anywhere Excel expects a range argument. =SUM(OFFSET(C5,0,0,5,1)) sums a 5-row block starting at C5, exactly as if you'd typed =SUM(C5:C9).

Rows and columns can be negative

Negative rows moves up. Negative cols moves left. This is what makes OFFSET useful for trailing calculations and reverse lookups, where the reference point sits below or to the right of the data you actually want.

Height and width resize the return, not just relocate it

Omit them and OFFSET returns a single cell (assuming reference is a single cell). Set height to 3 and width to 1, and OFFSET expands the returned reference into a 3-row block starting at the shifted position — that's the mechanism behind every rolling-sum and dynamic-range formula built on OFFSET.

The reference argument can be a range, not just a cell

reference doesn't have to be a single cell. Point it at an entire block, and rows/cols shift the whole block as a unit while height/width still control the final size. This matters when you're moving a multi-column lookup table rather than a single value.

Common Use Cases

Trailing 3-month total that updates automatically

A revenue column grows every month, and you want the sum of the most recent 3 months without editing the formula each time a row is added.

=SUM(OFFSET(F4, COUNT(F:F)-3, 0, 3, 1))

// F4              — anchor cell placed above the first data row
// COUNT(F:F)-3     — moves down to 3 rows before the last entry
// 3, 1             — expands the reference into a 3-row, 1-column block

Two-way lookup without INDEX/MATCH

A pricing matrix lists products down the rows and regions across the columns. Both the row and column position come from cells the user fills in.

=OFFSET(pricing_table, MATCH(H2, product_list, 0), MATCH(H3, region_list, 0))

This works, but INDEX does the identical job without the volatility cost — worth remembering before you scale this pattern across a workbook.

Dynamic named range for a chart source

The classic OFFSET pairing: a named range that grows as new rows are added, so a chart or pivot source doesn't need manual updating.

=OFFSET(Sheet3!$B$5, 0, 0, COUNTA(Sheet3!$B:$B)-4, 1)

If you're on Excel 365, an Excel Table with structured references handles this without a volatile formula at all — see the note on modern alternatives below.

Scrollable report window controlled by one input cell

A dashboard needs to show 10 rows of transaction data starting wherever the user types a row number into cell J2.

=OFFSET(transactions_start, $J$2-1, 0, 10, 5)

Type 21 into J2 and the report window jumps to rows 21–30 without a filter, a slicer, or a helper table.

Handling Errors

OFFSET throws #REF! when the calculated reference falls outside the worksheet — off the top edge, past column XFD, or below row 1,048,576.

Common causes of #REF!:

• A negative rows or cols value pushes the reference above row 1 or left of column A
• height or width is 0 or negative
• The reference cell itself was deleted after the formula was written

=OFFSET(C5, -10, 0) fails when C5 sits in row 5. Shifting up 10 rows would land on row -5, which doesn't exist, so Excel returns #REF! instead of a cell reference. Wrapping the formula in IFERROR hides the error but doesn't fix the underlying problem — the formula still can't reach the row it wants:

=IFERROR(OFFSET(C5, -10, 0), "Out of range")

The real fix is to stop the row shift from going negative in the first place. Clamp it with MAX so it never asks for a row above row 1:

=OFFSET(C5, MAX(-10, 1-ROW(C5)), 0)

// MAX(-10, 1-ROW(C5))  — uses -10 normally, but stops short of row 1 when C5 is near the top of the sheet

Notes & Gotchas

What is the OFFSET function in Excel?

OFFSET is an Excel function that returns a reference to a range of cells located a specified number of rows and columns away from a starting cell. It doesn't move or copy data — it builds a new reference that other formulas can read from. That returned reference can be a single cell or an entire block, depending on the height and width arguments.

What are the arguments (syntax) of the OFFSET function in Excel?

OFFSET takes five arguments: reference, rows, cols, height, and width. The first three are required — they define the starting point and how far to shift. height and width are optional and default to the size of reference if left out.

How do you use the OFFSET function in Excel?

Start with a fixed anchor cell, then tell OFFSET how many rows and columns to move away from it. =OFFSET(A1, 2, 3) starts at A1, moves down 2 rows and right 3 columns, and returns whatever's in D3. Add height and width when you need OFFSET to return a multi-cell range instead of a single value.

What is the difference between OFFSET and INDEX in Excel?

OFFSET calculates a reference dynamically and recalculates every time the workbook changes, making it volatile. INDEX returns a reference from a fixed range using a row and column number, and it only recalculates when its own inputs change, making it non-volatile. =INDEX(A1:A100, 5) and =OFFSET(A1, 4, 0) can return the same cell, but INDEX does it without the performance cost.

Why is the OFFSET function volatile in Excel?

OFFSET is volatile because Excel can't determine which cells it depends on without recalculating it first — the reference itself is built dynamically rather than fixed at the moment the formula is written. That forces Excel to reevaluate every OFFSET formula on every recalculation of the entire workbook, not just when a directly related cell changes.

Does OFFSET recalculate when rows or columns are inserted?

Yes, but the anchor cell reference itself can shift in ways that change the formula's result unexpectedly. Because OFFSET's reference argument behaves like a normal cell reference, inserting rows above it shifts the anchor down along with everything else — the offset math then runs from a different starting point than intended. Test any OFFSET formula after inserting or deleting rows near its anchor cell.

Should you still use OFFSET in Excel 365?

For most of the classic OFFSET use cases, no. Dynamic named ranges are better handled with an Excel Table, and rolling calculations often work fine with INDEX combined with dynamic array functions like SEQUENCE. OFFSET still earns its place for genuinely dynamic reference-shifting — the scrollable report window pattern, for instance — where INDEX can't easily substitute.

Related Functions

Function	Use this when...
INDEX	You need a non-volatile way to return a value from a fixed position in a range.
MATCH	You need to find a row or column position dynamically, paired with INDEX or OFFSET.
INDIRECT	You need to build a reference from text, like a sheet name stored in a cell — also volatile, so weigh it the same way as OFFSET.