4.5Advanced functions: OFFSET, INDIRECT, AGGREGATE
The essentials in 30 seconds
- OFFSET = a range that moves: a shifted, resizable reference (e.g. last 12 rolling months) — but volatile, to limit on large workbooks.
- INDIRECT = text that becomes a reference: =INDIRECT("Sales!B"&L1) points to a computed address — powerful but volatile and fragile on renaming.
- Wassim's reflex: to total despite errors and filters, AGGREGATE (non-volatile, ignores errors/hidden) — and first look for a robust alternative (INDEX, structured table).
Running mini-case — Wassim totals a filtered table full of errors
Wassim, an accountant in Algiers, must total a column of amounts that contains #N/A, with a table filtered on Algiers: =SUM returns #N/A. He uses =AGGREGATE(9,6,B2:B100) to ignore the errors, then the visible-rows option for the filtered view. For his rolling 12-month summary, he builds an OFFSET range — then replaces it with a structured table, less costly. Last milestone of the running thread: robust where SUM crashed, without slowing the workbook.
Last case of the running thread 4.1-4.5 — Wassim wraps up his level: import, model, dynamize, automate, make reliable.
1. OFFSET: a range that moves
OFFSET builds a reference shifted from a starting point (n rows, n columns), of a settable size. Useful for dynamic ranges (Wassim's last 12 rolling months). But it's a volatile function: it recalculates constantly and can slow a large workbook — often replaceable by a structured table or INDEX.
Typical form: =OFFSET(start;rows;columns;height;width). You confine it to cases where the range size really changes, and measure the impact on large files.
2. INDIRECT: reference by text
INDIRECT turns text into a reference: =INDIRECT("Sales!B"&L1) points to a cell whose address is computed — handy for built references (by sheet, by name). But it too is volatile and fragile (a sheet rename breaks the text, with no auto update).
Reserve it for cases where nothing else fits: when a reference must be built dynamically and INDEX or structured references aren't enough.
3. AGGREGATE: aggregate while ignoring
AGGREGATE performs aggregations (sum, average, large…) able to ignore errors and/or rows hidden by a filter. E.g. sum a column containing #N/A without crashing, or total only the visible after filtering. A robust, non-volatile alternative to workarounds.
Reading: AGGREGATE(function_num;option;range) — 9 = SUM, 6 = ignore errors, 5 = ignore hidden, 7 = both. Wassim memorizes 9;6 and 9;5 by heart.
🔎 Concrete example — 🔎 Concrete example — Total despite errors and a filter
Wassim's column B: numbers, #N/A, and the table is filtered:
=SUM(B2:B100) → returns #N/A (blocks) ❌
=AGGREGATE(9,6, B2:B100) → sum ignoring errors ✅
# 9 = SUM ; 6 = ignore errors
=AGGREGATE(9,5, B2:B100) → sum of visible rows only (5)
// Range of the last N months (N in D1):
=OFFSET($A$1, COUNTA($A:$A)-$D$1, 0, $D$1, 1)Reading: AGGREGATE combines an operation (9=sum) and an option (ignore errors/hidden).
Lesson: a robust function avoids fragile workarounds around errors and filters.
Section vocabulary
- OFFSET
- Builds a shifted, resizable reference (dynamic ranges). Volatile.
Simple example: Wassim frames his last 12 rolling months with OFFSET.
What it is not: a frozen range that follows insertions by itself.
Reflex: variable-size range: OFFSET, then consider a structured table.
- INDIRECT
- Turns text into a cell or range reference. Volatile.
Simple example: Wassim points =INDIRECT("Sales!B"&L1) to a computed row.
What it is not: a direct reference updated on renaming.
Reflex: computed address: INDIRECT as a last resort.
- AGGREGATE
- Aggregates (sum, average…) while ignoring errors and/or hidden rows.
Simple example: Wassim sums his #N/A column with AGGREGATE(9;6;...).
What it is not: a SUM that blocks at the first error.
Reflex: dirty or filtered data: think AGGREGATE.
- Volatile function
- Recalculates on every workbook change, even unrelated — can slow things down.
Simple example: Too many OFFSET/INDIRECT slow Wassim's closing workbook.
What it is not: a function that only recalculates if its inputs change.
Reflex: large workbook: count the volatiles, prefer INDEX.
In practice — In practice — Robust rather than fragile
- Sum a range containing a #N/A with AGGREGATE (ignore-errors option).
- Filter the table and total only the visible with AGGREGATE (hidden option).
- Build a range of the last N months (OFFSET) then discuss an INDEX alternative.
- Note why to limit INDIRECT/OFFSET on a large workbook.
Ready-to-say script — justify AGGREGATE at closing (30 s)
« This is Wassim: the column contains #N/A and the table is filtered, so SUM crashes. I used AGGREGATE with the ignore-errors option, and a visible-rows version for the filtered view. For the rolling range, I limited OFFSET to the strict minimum — these are volatile functions. »
Say it in the closing review: show the before/after of SUM vs AGGREGATE.
Copy-paste template — the robust trio
=AGGREGATE(9,6, B2:B100)
=AGGREGATE(9,5, B2:B100)
=AGGREGATE(9,7, B2:B100)
=OFFSET($A$1, COUNTA($A:$A)-$D$1, 0, $D$1, 1)
=INDIRECT("Sales!B" & L1)Column with errors
Like Wassim: =AGGREGATE(9,6,range) sums while ignoring the #N/A, without cleaning the source.
Filtered table
=AGGREGATE(9,5,range) totals only the visible rows after filtering.
Rolling summary
OFFSET sized by COUNTA for the last N months — then a structured table if there are slowdowns.
Built reference
INDIRECT to a sheet or a computed row, as a last resort and documented.
✓ Best practices
- Prefer AGGREGATE to aggregate despite errors/filters.
- Limit OFFSET/INDIRECT (volatile) on large workbooks.
- Look for an alternative (INDEX, structured table) first.
✗ Common pitfalls
- Filling a large workbook with volatile functions → slowdowns.
- INDIRECT to a renamed sheet name → #REF!.
- Using OFFSET where a structured table would do.
Key takeaways
- OFFSET: moving/resizable range (volatile).
- INDIRECT: text → reference (volatile, fragile).
- AGGREGATE: aggregates while ignoring errors and/or hidden rows.
- Volatile = constant recalculation; prefer robust alternatives.
48h challenge — apply tomorrow
On a real column with errors: replace SUM with AGGREGATE(9,6,...), test in filtered mode (option 5), then audit your OFFSET/INDIRECT and propose an alternative.
- I know how to aggregate despite errors and hidden rows (AGGREGATE).
- I know how to build a moving range (OFFSET) and a reference by text (INDIRECT).
- I know how to limit the volatiles and propose robust alternatives.
Frequently asked questions
Why avoid volatile functions?
They recalculate on every workbook change (even unrelated), which weighs on large files. OFFSET and INDIRECT are volatile; when a non-volatile alternative exists (INDEX, structured table, SUBTOTAL/AGGREGATE), prefer it.
AGGREGATE or SUBTOTAL?
SUBTOTAL ignores rows hidden by a filter; AGGREGATE goes further, also ignoring errors and offering more options. For « dirty » (errors) and filtered data, AGGREGATE is more robust.
Which function sums a column containing #N/A without crashing?