Spreadsheet Practice

Google Sheets lesson 4 of 11, Sheets essentials

FILTER and SORT, the Sheets way

Pull the matching rows into a live list, then put them in order, with one formula each.

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.

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.