OFFSET Function in Excel
OFFSET returns a range that's shifted from a starting reference by a specified number of rows and columns, with optional custom height and width. Powerful for building dynamic ranges — but volatile, meaning it recalculates on every workbook change. Modern Excel has better alternatives for most cases.
What it does: Takes a starting reference and returns a new reference shifted by rows and cols, optionally resized to height rows and width columns. The output is a live reference, not a value — usable inside SUM, AVERAGE, chart data, and named ranges.
What OFFSET does
OFFSET starts from a reference and shifts. =OFFSET(A1, 2, 3) starts at A1, moves 2 rows down and 3 columns right, and returns D3. That's the basic mode: a shifted single-cell reference.
The two optional arguments make it more powerful. =OFFSET(A1, 2, 3, 5, 2) starts at D3 and returns a 5-row × 2-column range (D3:E7). This lets you build a range whose starting position, size, or both change dynamically.
Syntax breakdown
reference Required
The starting cell or range. Must be a valid reference — cannot be a value or an array. Named ranges work.
rows Required
How many rows to shift down (positive) or up (negative) from reference. Zero means stay in the same row. Can be a cell reference or formula that produces a number.
cols Required
How many columns to shift right (positive) or left (negative). Zero means stay in the same column.
height Optional
The number of rows the returned range should span. Defaults to matching reference's height. Must be positive.
width Optional
The number of columns the returned range should span. Defaults to matching reference's width. Must be positive.
5 real-world examples
Example 1: Shift a single reference
From A1, get the value 3 rows down and 2 columns right (which is C4):
=OFFSET(A1, 3, 2)Result: Value at C4. Simple demonstration — in practice you'd just write =C4. OFFSET only earns its complexity when the shifts are dynamic.
Example 2: Dynamic sum of the last N rows
Sum the last 30 rows of column A regardless of where the data ends:
=SUM(OFFSET(A1, COUNTA(A:A)-30, 0, 30, 1))Result: Sum of the last 30 values. As new data is added, the range shifts. But use TAKE in Excel 365: =SUM(TAKE(A:A, -30)) — cleaner and non-volatile.
Example 3: Named range that auto-expands
Define a named range "SalesData" via Name Manager with:
=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1, 3)Result: A 3-column range starting at A2, with height equal to the number of data rows. Reference SalesData anywhere and it stays current. Modern replacement: convert the data to a Table and reference Table columns instead — auto-expanding without volatility.
Example 4: Rolling 12-month average
Get the average of the last 12 monthly values in column B (assumes A has months):
=AVERAGE(OFFSET(B1, COUNTA(A:A)-12, 0, 12, 1))Result: Rolling 12-month average. Recomputes as new months are added. Modern alternative: =AVERAGE(TAKE(B:B, -12)).
Example 5: Two-way dynamic pivot with OFFSET
Get a value from a grid based on row/column offsets from a corner cell (rare, but historically common):
=OFFSET($A$1, MATCH(E2, A:A, 0)-1, MATCH(F2, 1:1, 0)-1)Result: Value at the intersection. INDEX with two MATCHes does this without volatility: =INDEX(A:Z, MATCH(E2, A:A, 0), MATCH(F2, 1:1, 0)).
Common errors and how to fix them
The shifted reference falls outside the worksheet (row less than 1, column less than A, or beyond the sheet's edge). Check your row and column arguments — a common cause is rows being negative when the reference is already near row 1.
Non-numeric row or column argument, or zero/negative height/width. Verify inputs produce numbers. When height and width come from COUNTA, subtract 1 or add 1 carefully to avoid zero.
Too many OFFSET formulas — every change to any cell triggers recalculation of all of them. Solution: replace OFFSET with INDEX for dynamic ranges (non-volatile), convert data to Tables (auto-expand), or in Excel 365, use TAKE/DROP for last-N-rows patterns.
Named ranges built with OFFSET should update charts automatically — but Excel occasionally fails to detect the change until you re-select the chart. Better solution: convert your data to a Table and reference Table columns in the chart.
OFFSET vs modern alternatives
| Use case | Old (OFFSET) | Modern alternative |
|---|---|---|
| Dynamic sum end | =SUM(OFFSET(A1, 0, 0, D1, 1)) | =SUM(A1:INDEX(A:A, D1)) |
| Last N rows | =SUM(OFFSET(A1, COUNTA(A:A)-N, 0, N, 1)) | =SUM(TAKE(A:A, -N)) (365) |
| Growing named range | =OFFSET(A1, 0, 0, COUNTA(A:A), 1) | Convert to Excel Table |
| Row/column offset lookup | =OFFSET(A1, row, col) | =INDEX(A:Z, row+1, col+1) |
| Chart data range | OFFSET-based named range | Table column reference |
Version compatibility
Download the practice workbook
Every example above, plus before/after performance comparisons showing OFFSET vs INDEX for the same tasks.
Related functions
Frequently asked questions
Should I still use OFFSET in modern Excel?
Rarely. Almost every OFFSET use case has a non-volatile replacement. Use INDEX for dynamic-range endpoints, Tables for auto-expanding data, TAKE/DROP for last-N patterns. Reserve OFFSET for maintaining older workbooks or for the very rare case where nothing else fits.
How volatile is OFFSET compared to other functions?
Fully volatile — same as NOW, TODAY, RAND, RANDBETWEEN, INDIRECT, INFO, and CELL (with certain arguments). All these recalculate on every workbook change. In a workbook with many OFFSETs, the recalculation cost compounds and can turn instant changes into multi-second delays.
Can I use OFFSET inside a Table's structured reference?
You can OFFSET from a Table column: =OFFSET(Sales[Amount], 0, 0, 10). But this defeats the purpose — Tables already provide auto-expanding ranges without volatility. Reference the Table column directly instead.
Why does OFFSET break when I insert or delete rows?
Because OFFSET's shifts are relative to the reference and don't adjust for structural changes the way normal references do. If you delete a row above the OFFSET's starting cell, the reference shifts but the shift amounts don't. Result: OFFSET points to a different place than intended.
Can OFFSET return a negative-sized range?
No. Both height and width must be positive. To reference "the previous 10 rows," compute the starting position with a negative rows shift and use positive height.
Is there a way to make OFFSET non-volatile?
No — volatility is baked into how Excel treats OFFSET. The only path to non-volatile shifted-reference behavior is to use INDEX (which is non-volatile) or restructure the problem to avoid needing shifts at all.
What are the alternatives specifically for chart data ranges?
Convert your data to a Table (Home → Format as Table, or Ctrl+T). Then create the chart from the Table. When you add rows, the Table expands and the chart updates automatically — no OFFSET, no named range, no volatility. This is Microsoft's recommended pattern for expanding chart data.
Modernize volatile OFFSET formulas — with the Sheets & Cells AI Add-in
The Add-in scans your workbook for OFFSET usage, categorizes each one, and suggests non-volatile replacements — INDEX, Tables, or dynamic-array functions. Speed up sluggish workbooks in minutes. All inside Excel.
Learn about the Add-in →