FILTER practice exercises

Return every row that matches, not just the first: FILTER spills the matching rows into the cells below, with one condition, two conditions or a message when nothing matches.

Start the first exercise

How FILTER works

FILTER returns every row that meets a condition, not just the first. It takes three arguments: the range to return, a test that is TRUE or FALSE for each row, and optionally what to show when nothing matches. The result spills into the cells below and to the right, and it updates when the data changes. FILTER needs Excel 365 or Excel 2021.

=FILTER(B2:C7,A2:A7="North") returns the rep and sales for every row where the region is North. To combine conditions, multiply the tests: =FILTER(B2:C9,(A2:A9="North")*(C2:C9>300)) keeps only the North rows with sales over 300.

Keep the space below and to the right of the formula empty, or Excel shows a spill error. If nothing matches, FILTER returns an error, so give it a third argument such as "No one". The test range must have the same number of rows as the range you return. The exercises below cover one region, two conditions and a message when nothing matches.

  1. 1Keep the rows for one region with FILTERIn E2, list the rep and sales of every North row. FILTER the columns you want, B2:C7, by a test on the region column: A2:A7="North".
  2. 2FILTER with two conditionsPremiumIn E2, list the rep and sales of the North rows with sales over 300. Multiply the two tests so a row must pass both: (A2:A9="North")*(C2:C9>300).
  3. 3A message when FILTER finds nothingPremiumIn E4, list the reps whose sales reached the target in F1. Nobody did this month, so give FILTER a third argument to show "No one" instead of an error.

Functions in this topic

Read the guide

Questions

How do I filter with OR instead of AND in Excel?

Add the tests instead of multiplying them: =FILTER(B2:C9,(A2:A9="North")+(A2:A9="South")) returns the rows from either region. Multiplying means AND, so both tests must be true. Adding means OR, so one is enough. Use the same pattern with more regions by adding another test in parentheses.

How do I sort filtered results in Excel?

Wrap FILTER in SORT: =SORT(FILTER(B2:C9,A2:A9="North"),2,-1) sorts the matching rows by the second column, largest first. The 2 is the column to sort by, and -1 means descending. Leave it out and you get 1, ascending order.

Why does FILTER show #SPILL! instead of results?

Something is blocking the cells where the results should go, such as another value, a merged cell or an Excel table. FILTER cannot spill into a table. Clear the cells and the results appear. The error can also show up later if the data grows into cells that are not empty.