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.
UNIQUE
Dynamic arrays (Microsoft 365)Spill a distinct list — perfect for dropdowns and clean lists.
Difficulty
1What is it?
UNIQUE returns the distinct values from a range, spilling them out. Pair it with SORT for a clean, ordered list — ideal as the source for a data-validation dropdown or a list of accounts/customers. Microsoft 365 / Excel 2021+.
2What it looks like
UNIQUE(array)- array
- The range to de-duplicate.
3When you use it
- Build a dropdown source from a messy column.
- List every account or customer that appears in the data.
- Count distinct values: COUNTA(UNIQUE(range)).
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.
Pull a clean, sorted list of accounts from a column with repeats.
=SORT(UNIQUE(A2:A6))COGS, Opex, Revenue| A | B | |
|---|---|---|
| 1 | Account | Result |
| 2 | Revenue | COGS, Opex, Revenue |
| 3 | COGS | |
| 4 | Revenue | |
| 5 | Opex | |
| 6 | COGS |
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.
5Common errors
#SPILL!Something is blocking the spill range.Fix: Clear the cells below the formula.
Duplicates remainTrailing spaces make values look different.Fix: TRIM the source, or clean upstream.
6Better functions & alternatives
- Remove Duplicates — One-off cleanup via Data → Remove Duplicates.
- PivotTable — Distinct values plus counts, interactively.
Want UNIQUE 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.
Get my free report →