Google Sheets lesson 4 of 17, Sheets essentials
FILTER and SORT, the Sheets way
Quick answer
Pull the matching rows into a live list, then put them in order, with one formula each.
- Key formula
=FILTER(A2:D9,C2:C9="Open")(Rows where one condition is true)
Watch it done
4 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
FILTER returns every row of a range that meets your conditions, and the results fill the cells below and to the right. In Google Sheets each condition is its own argument, and a row has to pass all of them: =FILTER(A2:D9, C2:C9="Open", D2:D9>1000). Every condition must cover the same rows as the range.
That is the main difference from Excel. Excel's FILTER takes a single include argument, joins conditions with *, and has a third argument for what to show when nothing matches. In Sheets, extra arguments are simply more conditions. For OR, add two tests inside one argument: (C2:C9="Open")+(C2:C9="On hold") keeps rows that pass either.
When no row matches, FILTER returns #N/A. Wrap it to show a message instead: =IFNA(FILTER(A2:A9, B2:B9=H2), "No orders").
SORT takes the range, the column to sort by (counted from the left edge of the range), and TRUE for ascending or FALSE for descending: =SORT(A2:D9, 4, FALSE). Add more column and order pairs to break ties. Excel's SORT uses 1 and -1 for the order instead. Put FILTER inside SORT to get a filtered list in order: =SORT(FILTER(A2:D9, C2:C9="Open"), 4, FALSE).
Syntax
=FILTER(A2:D9,C2:C9="Open")Rows where one condition is true=FILTER(A2:D9,C2:C9="Open",D2:D9>1000)Every condition must be true=FILTER(B2:B9,(C2:C9="Open")+(C2:C9="On hold"))Either condition: add the tests=IFNA(FILTER(A2:A9,B2:B9=H2),"No orders")A message instead of #N/A=SORT(A2:D9,4,FALSE)Sort by the 4th column, largest first=SORT(FILTER(A2:D9,C2:C9="Open"),4,FALSE)Filter, then sortWatch out for
- Leave empty cells below and to the right. FILTER and SORT need room for their results, and Sheets shows an error if something is in the way.
- The range and every condition must cover the same rows. A2:D9 with C2:C10 returns an error.
- In Sheets, a third argument to FILTER is another condition, not a "no results" message. Use IFNA for the message.
- SORT counts columns from the left edge of its range, so column 2 of B2:D9 is column C.
The exercises
- Big open orders. In F2, use FILTER to list every Open order worth more than 1000. Return all four columns of A2:D9, and give each condition its own argument.
- Either status, and a fallback. In F2, list the customer names from B2:B9 for orders that are Open or On hold. Then, in H3, list the order numbers for the customer in H2, showing "No orders" when that customer has none.
- A sorted shortlist. In F2, list the Open orders (all four columns of A2:D9) sorted by value, largest first. Put the FILTER inside SORT.
The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Big open orders
F2, use FILTER to list every Open order worth more than 1000. Return all four columns of A2:D9, and give each condition its own argument.