OFFSET Generator

How OFFSET works

Availability: Every version of Excel, Excel for the web, Google Sheets and LibreOffice Calc. The spilling behaviour described below applies to Excel 2021 and Microsoft 365; in earlier versions a multi-cell OFFSET result has to be consumed by another function or array-entered.

OFFSET builds a reference by walking away from a starting point. Give it an anchor cell, a number of rows to move down, a number of columns to move right, and optionally a height and width, and it returns the reference it lands on. It does not return a value as such - it returns a range, which Excel then evaluates. That distinction is the whole function: OFFSET is at its most useful wrapped in SUM, AVERAGE or COUNT, where the range it produces can be many cells tall.

The classic job is a range that resizes itself. =SUM(OFFSET($B$2, COUNT($B$2:$B$1000)-12, 0, 12, 1)) totals the last twelve numbers in column B and keeps doing so as rows are added, and the same construction defines the dynamic named ranges that older charts use to grow with their data. Negative values move the other way: negative rows move up, negative columns move left, and 0 in either slot means stay put.

Two things make OFFSET expensive. It is volatile, so it recalculates on every change anywhere in the workbook and forces everything that depends on it to recalculate too - a model with hundreds of them is the usual reason a file lags a keystroke behind. And because the reference is computed rather than written, Excel's auditing tools cannot follow it, so nothing warns you when the anchor is moved or the shape stops making sense.

Most of what OFFSET was used for now has a better answer. An Excel Table expands automatically, so a chart or a formula pointing at a Table column needs no dynamic range at all. INDEX also returns a reference, can be used at either end of a range with the colon operator - $B$2:INDEX($B$2:$B$1000, COUNT($B$2:$B$1000)) - and is not volatile. In Microsoft 365, TAKE and DROP express "the last twelve rows" directly. OFFSET is still the right tool when the shift genuinely has to be calculated in both directions at once, and it remains in enough existing workbooks to be worth reading fluently.

Syntax

More worked examples

Common mistakes

#REF! from moving off the sheet

Any offset that lands outside the worksheet fails: negative rows from row 1, negative cols from column A, or a height that would run past the last row. The commonest version is a rolling-window formula run before enough data exists, where COUNT minus the window size goes negative.

Height or width below 1

Both must be at least 1. A height driven by COUNTA returns #REF! on an empty column, since the count is 0. Wrap the argument in MAX(1, ...) so the range collapses to one row instead of erroring.

Volatility multiplied by dependants

OFFSET recalculates on every edit, and so does everything downstream of it. Thirty dynamic named ranges feeding thirty chart series will recalculate on every keystroke in the workbook. Replace them with Table columns, which expand on their own and cost nothing.

COUNTA-based ranges cut short by gaps

COUNTA counts non-empty cells, so one blank row halfway down makes the dynamic range end one row early and quietly drops the last value. Use a Table, or count to the last used row with =MATCH(1E+306, $A:$A) for numbers or =MATCH("zzzz", $A:$A) for text.

Using a multi-cell result on its own

=OFFSET($A$1, 0, 0, 12, 1) typed into a single cell spills down twelve cells in Excel 2021 and Microsoft 365, and returns #VALUE! or a single value by implicit intersection in older versions. If you want one number, wrap it in SUM or AVERAGE and be explicit about which.