Do you regularly work with large Excel tables and are tired of manually sorting your data? The FILTER function, introduced in recent versions of Excel, allows you to create dynamic views and save valuable time. In this article, I offer you a comprehensive overview: from the basic syntax to advanced use cases, including some tips to optimize your filters!
Introduction to the FILTER function
The FILTER function – or FILTER in the English version – is specially designed to automatically extract rows from a table that meet one or more criteria. Unlike classic automatic filters, FILTER returns a new dataset without altering the source. Thus, you can build live dashboards, reports that update as soon as the source changes…
Syntax and parameters
The syntax is very clear:
FILTER(array; include; [if_empty])
- array: the range of cells to filter (for example B2:D100).
- include: a logical criterion (or several combined criteria) that determines which rows are kept.
- if_empty (optional): value to return if no record matches (for example “No results”).
Practical examples of dynamic filtering
1. Filtering numerical data
Imagine you have a table of monthly sales with a “Amount” column and you want to display only sales greater than €1,000. The formula would look like:
=FILTER(B2:D100; D2:D100>1000; “No sales ≥1000 €”)
If you change a value or add a row, the subset updates automatically. For further calculations, you can chain a SUMIF function to total only these significant sales.
2. Filtering with text criteria
You have a list of products and want to keep only those whose name contains “Pro”. Combined with text functions like SEARCH or MID, it’s easy:
=FILTER(A2:C200; ISNUMBER(SEARCH(“Pro”; A2:A200))); “No Pro products”)
Here, ISNUMBER(SEARCH(“Pro”;…)) returns TRUE on each row where “Pro” is detected. Result: a 100% dynamic subset.
Combining FILTER with other functions
The power of FILTER is especially revealed when combined with other Excel formulas or tools.
Interactive pivot table vs FILTER
You probably know slicers to control your pivot tables. With FILTER, you can achieve a similar effect in a regular cell range, without using the data model.
VLOOKUP and FILTER: when to choose?
VLOOKUP is traditional for returning a single value associated with a key. FILTER, on the other hand, can return multiple rows matching the same key. If you have multiple orders for the same customer, FILTER will generate the complete list, whereas VLOOKUP would only give the first occurrence. For more details on VLOOKUP, you can consult our complete guide on VLOOKUP.
Illustration with a small table
| Client | Date | Amount |
|---|---|---|
| Dupont | 2023-01-10 | 850 |
| Durand | 2023-02-15 | 1200 |
| Martin | 2023-03-05 | 450 |
| Durand | 2023-03-20 | 1600 |
With =FILTER(A2:C5, C2:C5>1000, “—”), you automatically get:
Durand | 2023-02-15 | 1200
Durand | 2023-03-20 | 1600
Advantages and limits of the FILTER function
- Advantages: dynamic sub-tables, multi-criteria, easy coupling with other formulas.
- Limits: available only in Microsoft 365 and Excel 2021+ versions, sometimes tricky management of resizable ranges in older workbooks.
- Tips: lock your ranges with named ranges, and use IF to handle cases where the result is empty.
Tips to optimize your filters
- Prefer named ranges for better readability.
- Test your criterion separately (in an auxiliary column) before incorporating it into FILTER.
- Use array functions like SEQUENCE to generate indices or project dates.
- Consider structuring your data as a Structured Table (Insert > Table), so the reference automatically adjusts to added or deleted rows.
FAQ
- Does the FILTER function work on Mac?
- Yes, since Excel for Mac 2021 or Microsoft 365. Older macOS versions do not include this function.
- Can I combine multiple criteria (AND/OR)?
- Yes, they are combined with multiple logical operators:
=FILTRE(A2:C100; (C2:C100>500)*(B2:B100<"2023-01-01")))for an AND,
and+for an OR. - How to handle no results?
- The third argument of FILTER (if_empty) allows returning a message or zero instead of a #CALC! error.
In short, the FILTER function is a real asset for anyone wishing to automate the extraction of relevant data directly within a worksheet. Whether you are preparing monthly reports, filtered client lists, or performance sub-tables, it quickly becomes indispensable.