Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All functions

OFFSET

Lookups & references

Rolling windows — last 3 months, trailing twelve — and it's in every model you'll inherit.

Difficulty

Advanced

Excel file

1What is it?

OFFSET returns a range defined relative to a starting point: move down so many rows, across so many columns, and give me a block of this height and width. That last part is what makes rolling windows possible — a trailing-twelve-month sum whose window moves as the data grows. You'll meet it in every inherited model, which is reason enough to read it. But it's volatile like INDIRECT, and in modern Excel a combination of INDEX or TAKE usually does the same job without the recalculation cost.

2What it looks like

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

reference: The anchor to move from.
rows / cols: How far to move. Negative goes up or left.
[height] / [width]: The SIZE of the range to return. Omit them and you get the same shape as the reference — supplying them is what makes a window.

3When you use it

A trailing-12-month total that moves as months are added.
The last N rows of a growing table, for a rolling average.
A chart range that extends itself when new data arrives.

4See it in action

Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.

Set the window length and how many months exist. The window follows the end of the data.

=SUM(OFFSET(A1, COUNT(A:A)-12, 0, 12, 1))Months 7-18 — the last 12, wherever the data now ends

	A
1	Result
2	Months 7-18 — the last 12, wherever the data now ends
3	Average: $49,850
4	height and width are what make this a WINDOW. Omit them and you get one cell.
5	Volatile — INDEX(range, n):INDEX(range, m) does the same without recalculating constantly.

The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.

Months of data

Window length

5Common errors

#REF!The offset lands outside the worksheet — usually a negative row count on a range near the top.

Fix: Guard the arithmetic: MAX(0, count − 12) keeps the window inside the sheet when there's less data than the window.

The file recalculates constantlyOFFSET is volatile: every one recalculates on every change anywhere in the workbook.

Fix: A few are fine. Hundreds will make a model crawl — that's usually what's wrong with an inherited model that's slow.

Returns one cell, you wanted a rangeheight and width were omitted, so it returned the reference's own shape.

Fix: Supply both. They're optional in the signature and essential in practice.

#VALUE!Used somewhere expecting a single value while returning a multi-cell range.

Fix: Wrap it in SUM, AVERAGE or whatever aggregates the window.

6Better functions & alternatives

INDEX — INDEX(range, n):INDEX(range, m) builds the same window and is NOT volatile. The better modern answer.
TAKE — TAKE(range, -12) is the clearest way to say "the last twelve" if you have Microsoft 365.

Want OFFSET already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.