4.3Dynamic formulas: FILTER, SORT, UNIQUE, LAMBDA
The essentials in 30 seconds
- One formula, several results: FILTER extracts, SORT orders, UNIQUE dedupes — the result spills onto neighboring cells and updates by itself.
- The # designates the spilled area: if D2 holds =UNIQUE(...), D2# references the whole list — and #SPILL! means « free the space ».
- Wassim's reflex: nest (SORT(UNIQUE(...))) for living lists, and name a small LAMBDA (e.g. 19% VAT) instead of repeating the calculation.
Running mini-case — Wassim produces living lists for the closing
Wassim, an accountant in Algiers, is preparing the closing: he needs the list of distinct wilayas (sorted, up to date) and the extraction of Algiers sales, without redoing manual filters every time. He writes =SORT(UNIQUE(Sales[Region])) and =FILTER(Sales,Sales[Region]="Algiers"): both arrays spill and follow the data. He references the list via D2# in his summaries and creates a VAT19 LAMBDA for the tax-inclusive amounts. Third milestone of the running thread: lists that live with the data.
Continuation of cases 4.1-4.2 — Wassim's cleaned and modeled tables are put to work in living formulas.
1. A formula that returns an array
Dynamic formulas (Microsoft 365) return several results that « spill » onto neighboring cells, without copying them. FILTER extracts the rows meeting a condition; SORT orders; UNIQUE removes duplicates. The result updates by itself when the source changes — Wassim's list of wilayas stays right without intervention.
You nest them: =SORT(UNIQUE(Sales[Region])) gives, in one formula, a deduped and sorted list. A single formula fills several cells and follows the data: it's the end of repetitive manual manipulations.
2. The spill (#)
The spill area is referenced with the #: if D2 holds =UNIQUE(...), then D2# designates the whole resulting list — including when it grows. You therefore build the summaries on D2#, not on a frozen range.
If a cell blocks the spill, Excel returns the #SPILL! error — just free the space below and to the right of the result. And you never copy a dynamic formula: it already fills its area by itself.
3. LAMBDA: create your own functions
LAMBDA lets you define your own reusable function, named in the Name Manager, without VBA. E.g. a VAT19(x) = x*1.19 function for the Algerian 19% VAT. Combined with MAP/REDUCE/BYROW, it opens elegant calculations without a macro. It's the summit of modern formulas.
Watch the compatibility: FILTER/SORT/UNIQUE/LAMBDA require Microsoft 365 or Excel 2021+. On earlier versions, they don't exist: you fall back on filters, pivots or old array formulas.
🔎 Concrete example — 🔎 Concrete example — Extract, sort, dedupe in one formula
Wassim's Sales table; list of regions and Algiers sales:
=SORT(UNIQUE(Sales[Region])) → deduped and sorted list (spills)
=FILTER(Sales, Sales[Region]="Algiers") → all Algiers rows (spills)
Reference the result elsewhere: D2# (the whole spilled area)
// Named LAMBDA VAT19 (Name Manager):
=LAMBDA(amount, amount*1.19)
= VAT19([@Amount]) → tax-inclusive with 19% VATReading: a single formula fills several cells and follows the data.
Lesson: FILTER/SORT/UNIQUE replace repetitive manual manipulations.
Section vocabulary
- Dynamic formula
- A formula that returns a « spilling » array, on Microsoft 365.
Simple example: Wassim's FILTER fills dozens of rows on its own.
What it is not: a classic formula to copy down.
Reflex: multi-row result: write a formula, let it spill.
- FILTER
- Extracts the rows meeting a given condition.
Simple example: Wassim extracts all Algiers sales in one formula.
What it is not: a manual filter to redo at each closing.
Reflex: living subset: think FILTER.
- SORT / UNIQUE
- Orders a range (SORT) or removes its duplicates (UNIQUE).
Simple example: Wassim produces his list of wilayas with SORT(UNIQUE(...)).
What it is not: a manual sort and a duplicate removal in clicks.
Reflex: reference list: nest SORT(UNIQUE(...)).
- LAMBDA
- Defines your own reusable, named function, without VBA.
Simple example: Wassim names VAT19 to apply the 19% VAT everywhere.
What it is not: a macro or a rate repeated hard-coded in each formula.
Reflex: repeated calculation: name it in a LAMBDA.
In practice — In practice — Formulas that spill
- Write =UNIQUE(column) to get the distinct values.
- Wrap with =SORT(UNIQUE(...)) to sort the list.
- Extract a subset with =FILTER(table, condition).
- Create a small named LAMBDA function (e.g. VAT19) and use it.
Ready-to-say script — show the spill to a colleague (30 s)
« This is Wassim: look, a single formula fills the whole list of wilayas — and when I add a sale, it updates by itself. I extract Algiers with FILTER, I reference it via D2#, and the 19% VAT is a named function. No more filters redone by hand. »
Say it at closing time: type the formula in front of them and add a row to show the update.
Copy-paste template — the dynamic trio + VAT
=SORT(UNIQUE(Sales[Region]))
=FILTER(Sales, Sales[Region]="Algiers")
=SORT(UNIQUE(FILTER(Sales[City], Sales[Region]="Algiers")))
// In the Name Manager, name it VAT19:
// =LAMBDA(amount, amount*1.19)List of sales reps
Like Wassim: =SORT(UNIQUE(Sales[Rep])) for a living list of sellers.
Top of unpaid invoices
=FILTER(Sales,Sales[Status]="Unpaid"): the extraction follows the payments without redoing a filter.
Reference elsewhere
Build the summaries on D2#: when the list grows, everything follows automatically.
Version without 365
FILTER/SORT/UNIQUE absent: fall back on AutoFilter, pivots or old array formulas.
✓ Best practices
- Leave space for the spill.
- Reference a spilled area with # (D2#).
- Combine FILTER/SORT/UNIQUE by nesting.
✗ Common pitfalls
- Cell occupied under the result → #SPILL!.
- Copying a dynamic formula unnecessarily.
- Using these functions on a version that lacks them (before 365/2021).
Key takeaways
- Dynamic formulas return an array that spills.
- FILTER (extract), SORT (order), UNIQUE (dedupe).
- The spilled area is referenced with # (e.g. D2#).
- LAMBDA creates reusable functions without VBA.
48h challenge — apply tomorrow
On a real table: write =SORT(UNIQUE(column)), extract a subset with FILTER, reference it via # and name a LAMBDA for a repeated calculation.
- I know how to extract, sort and dedupe with dynamic formulas.
- I know how to reference a spilled area with # and handle #SPILL!.
- I know how to name and use a simple LAMBDA function.
Frequently asked questions
Why #SPILL! ?
A dynamic formula wants to « spill » but a cell in the target area is occupied. Free the space below/right of the result, and the formula will fill the whole area.
Do these functions work on all versions?
FILTER/SORT/UNIQUE/LAMBDA are in Microsoft 365 and Excel 2021+. On earlier versions, they don't exist: you fall back on filters, pivots or old array formulas (Ctrl+Shift+Enter).
What does the UNIQUE function do?