The FILTER function in Excel: practice with answers
FILTER returns every row that matches a condition, and updates by itself. Six exercises with answers, from one condition to two, then SORT and UNIQUE.
FILTER returns every row that matches a condition. You type one formula in one cell, and the matching rows fill the cells below it. When the data changes, the list changes by itself.
FILTER, SORT and UNIQUE need Excel 2021 or later, or Microsoft 365. They also work in Excel for the web. Excel 2019 and older do not have them.
Type this table in A1 to D9. Write each answer in F2. Keep the cells below and to the right of F2 empty.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Order | Customer | City | Amount |
| 2 | 1001 | Kaya | Lisbon | 250 |
| 3 | 1002 | Moreno | Porto | 90 |
| 4 | 1003 | Lind | Lisbon | 400 |
| 5 | 1004 | Osei | Faro | 150 |
| 6 | 1005 | Moreno | Porto | 310 |
| 7 | 1006 | Kaya | Lisbon | 75 |
| 8 | 1007 | Tan | Faro | 220 |
| 9 | 1008 | Lind | Porto | 130 |
The shape is =FILTER(what to return, test, if nothing matches).
The third part is optional.
Exercise 1 — one condition
List every order from Lisbon.
Answer: =FILTER(A2:D9,C2:C9="Lisbon") fills F2
to I4:
| 1001 | Kaya | Lisbon | 250 |
| 1003 | Lind | Lisbon | 400 |
| 1006 | Kaya | Lisbon | 75 |
One formula fills 3 rows and 4 columns. This is called spilling. If a cell in that space is not
empty, you get #SPILL! instead.
Now change C3 to Lisbon. The list grows to 4 rows, and you did not touch the formula.
Exercise 2 — a number test, one column back
List the customers with an order of 200 or more. Show only their names.
Answer: =FILTER(B2:B9,D2:D9>=200) fills F2 to
F5:
| Kaya |
| Lind |
| Moreno |
| Tan |
The first part is what to return. It can be one column, while the test reads a different one.
The test is a real comparison, with no quote marks. COUNTIF is different. Write
D2:D9>="200" and Excel compares numbers with text. No row
passes, and you get #CALC!.
Exercise 3 — when nothing matches
List the orders from Braga. There are none.
Answer:
=FILTER(A2:D9,C2:C9="Braga","No orders") fills F2 only:
| No orders |
The third part is what to show when no row passes. Leave it out and FILTER shows
#CALC!. A SUM around it then shows the error too. For an empty
cell, use "" as the third part.
Exercise 4 — two conditions, both true
List the Lisbon orders of 200 or more.
Answer:
=FILTER(A2:D9,(C2:C9="Lisbon")*(D2:D9>=200)) fills F2 to I3:
| 1001 | Kaya | Lisbon | 250 |
| 1003 | Lind | Lisbon | 400 |
Each test goes in its own brackets. The star multiplies them. TRUE counts as 1 and FALSE as 0. So a row gives 1 only when both tests are TRUE.
AND does not work here. It gives one answer for the whole range, not one answer for each row.
Exercise 5 — one condition or the other
List the orders from Porto or Faro.
Answer:
=FILTER(A2:D9,(C2:C9="Porto")+(C2:C9="Faro")) fills F2 to I6:
| 1002 | Moreno | Porto | 90 |
| 1004 | Osei | Faro | 150 |
| 1005 | Moreno | Porto | 310 |
| 1007 | Tan | Faro | 220 |
| 1008 | Lind | Porto | 130 |
The plus sign adds the tests. A row passes when the total is not 0, so one TRUE is enough. The rows stay in the same order as the table.
For two conditions, remember: the star means "and", and the plus means "or".
Exercise 6 — add SORT and UNIQUE
First, list the Porto orders with the biggest amount first. Then list each Porto customer once.
Answer:
=SORT(FILTER(A2:D9,C2:C9="Porto"),4,-1) fills F2 to I4:
| 1005 | Moreno | Porto | 310 |
| 1008 | Lind | Porto | 130 |
| 1002 | Moreno | Porto | 90 |
SORT takes the list, the column to sort by, and the order. Here 4 is the Amount column. The -1 means largest first, and 1 means smallest first.
Answer:
=UNIQUE(FILTER(B2:B9,C2:C9="Porto")) fills F2 to F3:
| Moreno |
| Lind |
Moreno has 2 orders from Porto but is listed once. UNIQUE keeps each value one time, in the order it first appears.
Questions people ask
Can I total or count what FILTER returns?
Yes. Put a function around it. =SUM(FILTER(D2:D9,C2:C9="Lisbon"))
gives 725. =ROWS(FILTER(A2:D9,C2:C9="Lisbon")) gives 3. The
result is one number in one cell, so nothing spills.
Is FILTER the same as the Filter button?
No. The Filter button hides rows in the table itself. FILTER does not change the table. It copies the rows that match to another place. The copy changes when the data changes.
What does #SPILL! mean?
Something is in the way. The result needs empty cells, and one of them is not empty. Clear those cells, and the list comes back.
When should I use XLOOKUP instead?
When you want one match. XLOOKUP returns the first match only. FILTER returns all of them. The XLOOKUP vs VLOOKUP article covers lookups.
Where can I practise this with checking?
In the FILTER, UNIQUE and SORT lessons below. Each lesson is a real sheet you type into, and your answer is checked when you press Enter.