# FILTER and SORT, the Sheets way (Google Sheets lesson 4 of 17)

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

Source: https://spreadsheetpractice.com/google-sheets/learn/filter-and-sort · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Sheets essentials"
- **Time:** about 12 minutes, 3 graded exercises
- **Functions:** `FILTER`, `SORT`, `IFNA`
- **Access:** free
- **Key formula:** `=FILTER(A2:D9,C2:C9="Open")`: Rows where one condition is true

## 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

| Formula | What it does |
|---|---|
| `=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

Solve them in the live grid at [FILTER and SORT, the Sheets way](https://spreadsheetpractice.com/google-sheets/learn/filter-and-sort). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

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.

Previous: [VLOOKUP and XLOOKUP in Google Sheets](https://spreadsheetpractice.com/google-sheets/learn/vlookup-xlookup) · Next: [Conditional formatting with custom formulas](https://spreadsheetpractice.com/google-sheets/learn/conditional-formatting)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
