Concept
1. Syntax
=FILTER(array, include, [if_empty])
- array — the rows/columns to return
- include — a TRUE/FALSE test, one per row
- if_empty — what to show if nothing matches (otherwise #CALC!)
2. One condition
=FILTER(tblSales, tblSales[Region]="North", "No sales")
Returns all 6 North rows (all 7 columns). Headers aren't included — type or copy them above, or use =tblSales[#Headers].
3. Driven by an input cell
Put a region in B1 (with a data validation dropdown from =UNIQUE(tblSales[Region])):
=FILTER(tblSales, tblSales[Region]=B1, "No sales")
Change B1 → the list changes. A mini report with no pivot and no refresh.
4. AND — multiply the conditions
=FILTER(tblSales, (tblSales[Region]="West") * (tblSales[Category]="Furniture"), "None")
3 rows: Chair 45,000, Desk 48,000, Chair 27,000 → total 1,20,000. TRUE × TRUE = 1; anything × FALSE = 0.
5. OR — add the conditions
=FILTER(tblSales, (tblSales[Region]="North") + (tblSales[Region]="West"), "None")
11 rows. For many OR values use ISNUMBER(MATCH): ISNUMBER(MATCH(tblSales[Region], {"North","West"}, 0)).
6. Ranges, dates and text search
=FILTER(tblSales, (tblSales[Date] >= DATE(2026,5,1)) * (tblSales[Date] <= DATE(2026,5,31)))
=FILTER(tblSales, tblSales[Amount] >= 100000)
=FILTER(tblSales, ISNUMBER(SEARCH("lap", tblSales[Product])))
May 2026 → 6 rows. Amount ≥ 1,00,000 → 4 rows. "lap" → the 4 Laptop rows (SEARCH is not case-sensitive).
7. Return only some columns
Use CHOOSECOLS (Microsoft 365 / 2024):
=CHOOSECOLS(FILTER(tblSales, tblSales[Region]="North"), 1, 4, 7)
Date, Product, Amount only. Or filter a column range like tblSales[[Product]:[Amount]] when the columns are next to each other.
8. Aggregate the filtered result
=SUM(FILTER(tblSales[Amount], tblSales[Region]=B1, 0))
=ROWS(FILTER(tblSales, tblSales[Region]=B1))
(SUMIFS/COUNTIFS are faster for simple totals; FILTER shines when you need the rows.)
Common mistakes
Using AND()/OR() functions inside FILTER (they return one value, not one per row — use * and +). Forgetting if_empty (#CALC!). include and array of different heights (#VALUE!).