Secondary data

Sort, filter and count in Google Sheets

Five spreadsheet moves cover everything this guide asks you to do with a data file. The traps give you wrong numbers without any error message, so they matter more than the moves. Most examples use the Shark Control Program 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.

Five 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 Shark Control Program file

    Sorting NumberCaught from largest to smallest took a minute. The top rows record several sharks at once, which shows that a row is not one animal: it is one kind of animal on one gear at one beach in one month. 4,003 rows in the file record more than one, and nothing else in the download says so.

  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 Shark Control Program file

    Filter Year by condition to Is between 1996 and 2023, SpeciesGroup to SHARK, Gear to Net and Drum, and Area to Gold Coast, Sunshine Coast North, Sunshine Coast South and Rainbow Beach. That leaves 4,299 rows, which together hold 5,228 sharks.

  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.

    =SUMIFS(O:O, F:F, "Net")
    The total of column O (NumberCaught) for the rows where column F says Net.
    =SUMIFS(O:O, F:F, "Net", P:P, "White shark")
    The same, for the rows that also say White shark in column P.
    =AVERAGEIF(F:F, "Net", O:O)
    The mean of column O, for the rows that say Net.
    =MEDIAN(FILTER(O:O, F:F="Net"))
    The median of column O, 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 Shark Control Program file

    With a helper column P that says White shark or Other shark (=IF(J2="WHITE SHARK", "White shark", "Other shark")), these give 3,296 sharks in nets, 73 of them white sharks, and 1,932 on drumlines, 74 of them white sharks. Add up NumberCaught with SUMIFS: counting rows would count each row once, however many sharks it records.

  4. 4

    Join two files on a shared key

    If your two lines are in two files, the rows have to be matched by what they describe: the same station, the same state, the same year. Never by their position on the screen.

    1. Put each file in its own tab. Find the column both files share: a station code, a country or state name, a year. If a row is one place in one year, the key is both: add a column in each tab such as =A2&"-"&C2, which joins the station code and the year.
    2. In the tab that will hold your dataset, add a column that fetches the other file's value for each key.
    3. Count the rows that found no match, and find out why: a name spelt differently, a code with a leading zero, a year one file does not have.
    4. Pick three rows and check each one by hand against both original files. If any is wrong, every row is suspect.
    =XLOOKUP(D2, Nutrients!D:D, Nutrients!E:E, "no match")
    Finds this row's key (D2) in the other tab's key column, and brings back the value beside it. Writes no match when the key is not there.
    =COUNTIF(F2:F, "no match")
    How many rows found nothing to join to.

    Use the exact key, never the name alone where names repeat. Keep only the rows where both values exist, and say in your method how many that left.

    In the Shark Control Program file

    The sewage starter investigation joins Geneva's nutrients file to its invertebrate file on station code and year. Station names repeat (twenty-two stations are called Embouchure, because every river has a mouth), so the key is CODEMESURE joined to the year, and 408 station-years find a match.

  5. 5

    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 Shark Control Program file

    4,299 rows, and =SUM(O2:O) gives 5,228 sharks: 3,296 in nets and 1,932 on drumlines. 3,296 + 1,932 = 5,228.

Five traps

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

Two columns pasted side by side

Copy one column from each file and paste them next to each other, and the rows match only if both files happened to be sorted the same way. One publisher lists places by region, another alphabetically, and every row pairs one place's value with a different place's. The graph and the test still run, and give an answer about nothing.

InsteadJoin on the key with XLOOKUP, then check three rows by hand against both original files.

Adding two cells instead of a range

=B2+Z2 adds the first year and the last. It does not add the twenty-three years between them, and the total looks just as believable.

InsteadTotal across years with SUM over the whole range, such as =SUM(B2:Z2), and check one row by hand.

Filtered rows still count

A filter hides rows, but AVERAGE, SUM and COUNT still use them. Filter down to the four areas, add up NumberCaught, and you get the total for the whole file. 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.

Sort, filter and count in Google Sheets · ESS IA guide