Sign in
For both routesPrimary dataSecondary data

Sort, filter and count in Google Sheets

Four spreadsheet moves cover everything this guide asks you to do with a data file. Three traps give you wrong numbers without any error message, so they matter more than the moves. Every example uses the Montreal Protocol file from the worked example.

Before you start, keep the download in its own tab and never change it. Do everything else on a copy. That untouched tab is your full dataset, and it is what goes in your appendix.

Four moves

  1. 1

    Sort the whole sheet

    Sorting is how you read a file. Sort by name, and gaps and odd rows show up. Sort by a number, and you see the smallest and largest values, the zeros, and anything negative.

    1. Freeze the header row first: View › Freeze › 1 row. Otherwise the headings get sorted in with the data.
    2. Click any cell in the column you want to sort by.
    3. Choose Data › Sort sheet › Sort sheet (A to Z). Choose Z to A to put the largest first.
    In the Montreal Protocol file

    Sorting the Entity column and reading down it took two minutes. It showed that no EU member state is in the file, which nothing else in the download says.

    Watch out: sorting one column scrambles your data
  2. 2

    Filter to your inclusion rule, then copy to a new tab

    Your inclusion rule says which rows count. A filter hides the rest, so you can look at what is left before you use it.

    1. Click any cell in the data and choose Data › Create a filter. A filter button appears in every heading.
    2. Click the button in a heading. Use Filter by values to tick the values you want to keep, or Filter by condition for a rule such as Greater than.
    3. Select the rows you can see, copy them, and paste them into a new tab. Only the visible rows are copied. Name the tab after your rule, for example 1996 countries.
    4. Do every calculation on that new tab.
    In the Montreal Protocol file

    In the Code column, untick (Blanks) and the seven codes that start OWID_. That removes the continents, the EU and the World. Then, in the Year column, keep only 1996. That leaves 165 rows: one for each country the comparison uses.

    Watch out: filtered rows still count Watch out: a filter on a shared sheet changes it for everyone
  3. 3

    Count and average by group

    Most questions compare groups. One formula per group gives you the count, the mean and the median, and a reader can see how each number was made.

    =COUNTIF(B:B, "Article 5")
    How many rows in column B say Article 5.
    =AVERAGEIF(B:B, "Article 5", C:C)
    The mean of column C, for those rows only.
    =MEDIAN(FILTER(C:C, B:B="Article 5"))
    The median of column C, for those rows only.

    Copy each formula and change the text in quotes for your other group. The text has to match the cells exactly, spaces included, or the formula finds nothing.

    In the Montreal Protocol file

    With each country's group in column B and its 1996 percentage in column C, these give 147 Article 5 and 18 non-Article 5 countries, with medians of 73.9% and 0.5%.

  4. 4

    Check the count

    The number of rows you analysed has to match the number in your method. If it does not, a row has gone missing or crept in, and every result after it is wrong.

    =COUNTA(A2:A)
    How many filled cells there are in column A, below the heading.

    Compare it with the number in your method. Then count each group with COUNTIF: the groups should add up to the total.

    In the Montreal Protocol file

    165 countries: 18 non-Article 5 and 147 Article 5. 18 + 147 = 165.

Three traps

Each one gives you a wrong number, and none of them shows an error.

Filtered rows still count

A filter hides rows, but AVERAGE, SUM and COUNT still use them. Filter down to 18 countries, average the column, and you get the average of all 165. Nothing warns you.

InsteadCopy the rows you can see to a new tab, and calculate there.

Sorting one column scrambles your data

If you select one column and use Data › Sort range, only that column moves. The numbers no longer sit next to the right names, and nothing tells you.

InsteadAlways use Data › Sort sheet. If names and numbers stop matching, undo straight away: Ctrl+Z, or ⌘+Z on a Mac.

A filter on a shared sheet changes it for everyone

If a partner or your class works in the same file, your filter hides rows on their screen too.

InsteadUse Data › Filter views › Create new filter view instead. It only changes what you see.

Back to the guide
Sort, filter and count in Google Sheets · ESS IA guide | Revise