Spreadsheet Practice

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)
FreeSheets mode12 minutes, 3 exercisesFILTERSORTIFNA

Watch it done

4 steps

Watch 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 sort

Watch 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

  1. 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.
  2. 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.
  3. 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

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.