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.
FILTER
Dynamic arrays (Microsoft 365)Return the rows that meet a condition as a live, spilling result.
Difficulty
1What is it?
FILTER returns every row of a range that meets a condition, spilling the results into the cells below — a live query right in the grid that updates as the source data changes. Microsoft 365 / Excel 2021+.
2What it looks like
FILTER(array, include, [if_empty])- array
- The range to filter.
- include
- A TRUE/FALSE condition the same height as the array.
- [if_empty]
- What to return when nothing matches (avoids #CALC!).
3When you use it
- A live list of transactions for one account or customer.
- Open invoices, or rows over a threshold, that refresh automatically.
- Feed a clean subset into a chart or another formula.
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.
Return only the rows for the account you pick — a spilling mini-query.
=FILTER(A2:C5, A2:A5="Revenue", "None")Jan · $80,000| A | B | C | D | |
|---|---|---|---|---|
| 1 | Account | Month | Amount | Result |
| 2 | Revenue | Jan | $80,000 | Jan · $80,000 |
| 3 | Revenue | Feb | $90,000 | Feb · $90,000 |
| 4 | COGS | Jan | $32,000 | |
| 5 | Opex | Jan | $28,000 |
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
#CALC!No rows match and there's no if_empty.Fix: Add the if_empty argument.
#SPILL!Cells where the result needs to spill aren't empty.Fix: Clear the cells below/right of the formula.
6Better functions & alternatives
- AutoFilter — Manual, in-place filtering without a formula.
- SUMIFS / PivotTable — When you want an aggregate rather than the rows themselves.
Want FILTER 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 →