Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 29 ยท Working with Data

Sorting and Filtering Data

Sort ranges and sheets, use filters and filter views, and build live results with the SORT and FILTER functions in Google Sheets.

Once a sheet holds more than a few dozen rows, you need to reorder it and hide what you do not care about. Google Sheets gives you two families of tools for that: menu commands that change the sheet in place, and functions (SORT, FILTER) that write a sorted or filtered copy somewhere else and keep it up to date.

This lesson explains when to use each, the traps that scramble data, and how multi-level sorting and multi-condition filtering work. Examples assume a table with a header row, like the ones you built in Entering and Editing Data.

Change it, or compute it?

Menu sort Reorders the real rows.
Filter Hides rows, keeps the data.
Functions SORT and FILTER build a live copy.

Menus change the sheet. Functions leave the source alone.

1 / 2

Sorting in place

Select a cell in your table and open Data โ†’ Sort range โ†’ Advanced range sorting options. Tick Data has header row so the headers stay on top, choose a column and A โ†’ Z or Z โ†’ A, and use Add another sort column to break ties. Sorting by Region and then by Sales descending groups rows by region with the best seller first in each.

Three habits prevent most disasters:

  1. Select the whole table, not one column. Sorting a single column alone separates its values from the rest of each row. When you click inside a table, Sheets usually selects the whole thing, but check.
  2. Freeze the header row (View โ†’ Freeze โ†’ 1 row) before using Sort sheet. Sort sheet orders every row of the sheet, but frozen rows stay at the top.
  3. Know what is sorted by value. Numbers sort before text, dates sort as numbers (so they order correctly), and text numbers like "10" sort alphabetically, putting "10" before "9". If a column sorts oddly, check whether its numbers were stored as text.

You can also sort by cell color or text color, which pairs well with conditional formatting.

Python Stand-in: multi-level sort

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Filters and filter views

Data โ†’ Create a filter puts a dropdown arrow in each header cell. Use it to filter by condition (greater than, text contains, date is before...), to filter by values (untick the ones to hide), or to filter by color. Filtered-out rows are hidden, not deleted, and the row numbers turn green.

A plain filter belongs to the sheet: everyone looking at it sees your hidden rows, and a collaborator who changes the filter changes it for you. A filter view (Data โ†’ Filter views โ†’ Create new filter view) is saved by name and applies only to the person who selects it, so several people can look at the same sheet in different ways. Use them in shared sheets and keep the plain filter for your own files.

Filtering step by step

  1. Click any cell in your table and choose Data > Create a filter. Arrows appear in the header row, and a green frame marks the filtered range.
  2. Click an arrow. Under Filter by condition, pick a rule such as Text contains, Text starts with, Greater than or Date is before, and type the value. Under Filter by values, untick values to hide them (use Clear then tick only the ones you want).
  3. Add a second filter on another column to narrow the rows further. The filters combine with and.
  4. To reset one column, open its arrow and choose Clear (or select every value again). A filtered column shows a colored filter icon in its header, so you can see at a glance which columns are filtered.
  5. To remove the filter completely, choose Data > Remove filter. The rows come back and the arrows disappear.

A filter only hides rows. If a formula must react to the filter (a total of the visible rows), use SUBTOTAL(109, ...) as shown in Group and Hide Rows and Columns, or use the FILTER function below, which returns the matching rows as live data.

Alphabetizing

To put a list in alphabetical order, select the table, open Data > Sort range > Advanced range sorting options, tick Data has header row, choose the column and A to Z. For a single column with no neighboring data, right-click the column letter and choose Sort sheet A to Z, or select the range and choose Data > Sort range by column A, A to Z. To alphabetize by last name when the column holds full names, add a helper column with the surname (Separating names) and sort on that column. On the mobile app (Android and iOS), tap the column letter to select it, tap it again to open the menu, choose Sort A to Z or Sort Z to A, and check that the header row is not included.

The SORT function

SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...]) returns a sorted copy:

TEXT
=SORT(A2:C20, 3, FALSE)              sort by 3rd column, high to low
=SORT(A2:C20, 1, TRUE, 3, FALSE)     by column 1 A-Z, then column 3 Z-A
=SORT(A2:C20, B2:B20, TRUE)          sort by a separate column of the same height

The sort column is a position inside the range (1 is the first column of the range), not the column letter. Put the formula in one cell and the result spills into the cells below and to the right. Those cells must be empty, or you see #REF! with a message that the result would overwrite data.

Because it is a formula, the copy updates when the source changes. Add UNIQUE(range) around or inside it to remove duplicate rows, and SORTN(range, n, ...) to keep only the top n rows, such as the three best sellers.

The FILTER function

FILTER(range, condition1, [condition2, ...]) returns only the rows where all conditions are true. Each condition is a column of TRUE/FALSE values with the same height as the range:

TEXT
=FILTER(A2:C20, C2:C20 > 500)                       sales over 500
=FILTER(A2:C20, A2:A20 = "East", C2:C20 > 500)      AND: both conditions
=FILTER(A2:C20, (A2:A20 = "East") + (A2:A20 = "West"))   OR: add the tests
=FILTER(A2:C20, ISNUMBER(SEARCH("an", B2:B20)))     text contains "an"
=IFERROR(FILTER(A2:C20, C2:C20 > 9999), "No matches")

Several conditions are combined with AND. For OR, add the tests together (TRUE counts as 1, so any non-zero sum passes). When nothing matches, FILTER returns #N/A, so wrap it in IFERROR to show a friendly message (see Understanding Formula Errors).

Combine the two for a sorted, filtered report: =SORT(FILTER(A2:C20, C2:C20 > 500), 3, FALSE). Using a cell for the threshold, such as C2:C20 > $F$1, turns it into an interactive report.

Python Stand-in: FILTER with AND, OR and no match

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Choosing the right tool

Need Use
One-off reordering of the real data Sort range
Look at part of a shared sheet without bothering others Filter view
A report that follows the source data SORT / FILTER
A top-N list SORTN or SORT with ARRAY_CONSTRAIN
Unique values UNIQUE

Remember that function results are formulas: you cannot edit one cell of the spilled result, and sorting the source range in place may reshuffle things you were comparing. Next you will use rules to highlight rows instead of hiding them.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is it risky to sort a single column of a table without selecting the others?

    Only the selected cells move, so each row's data no longer lines up. Select the whole table.
  2. 2. What is the main difference between a filter and a filter view?

    A plain filter is shared by everyone using the sheet. A filter view is a named, personal way of looking at the data.
  3. 3. In =SORT(A2:C20, 3, FALSE), what does the 3 mean?

    The sort column is a position inside the range. Here the range has three columns, so 3 is its last one.
  4. 4. How do you get an OR condition in FILTER?

    Separate conditions are ANDed. Adding TRUE/FALSE tests gives a non-zero number when either is true.

Summary

  • Select the whole table before sorting, and tick "Data has header row".
  • Plain filters are shared; filter views are named and personal.
  • SORT(range, column, ascending, ...) returns a live sorted copy; the column is a position inside the range.
  • FILTER(range, conditions...) keeps rows meeting all conditions; add tests for OR and wrap in IFERROR for empty results.
  • Spilled results need empty cells, otherwise you get #REF!.

Next: Conditional Formatting