Spreadsheets at Depth
Sorting and filtering a sheet
Work out what a sheet looks like after its rows have been put in order by a column, or narrowed to the rows that pass a test.
A sheet of a few hundred rows holds every answer somebody might want and shows none of them, because nothing about the order it was typed in matches the order anyone wants to read it. Two tools fix that, and they do genuinely different things. Sorting puts the rows into an order decided by one of the columns — smallest first, largest first, or alphabetically. Filtering leaves the order alone and hides the rows that fail a test, so that only the ones you asked about are on screen. Both can be undone, and both can be used one after the other.
A sort moves rows, and only what it was given
The point of a sort is that a whole row travels together: a name and the amount beside it belong to each other, and the row is what keeps them together. This is also where a sheet gets ruined. If only one column is selected when the sort runs, then only that column is put in order and every other column stays exactly where it was — so every row now says something that was never true. Nothing warns you and nothing looks broken afterwards, which is why it is worth being careful once rather than checking afterwards. A filter cannot do this kind of damage at all: hidden rows are still there, still whole, and come back untouched the moment the filter is switched off.
Worked example
One sheet, two tools. What is on screen after each of them?
red 16 blue 4 green 25 black 9
Sorting this by the second column, smallest first, leaves all four rows on screen in a new order: blue 4, black 9, red 16, green 25.
Nothing has been added or taken away. A sort is a rearrangement, and the sheet holds exactly what it held before.
Try it together
Now work one sheet through a filter and then a sort.
A B 1 pen 40 2 bag 12 3 cup 33 4 map 8 5 tin 21
Deal with the filter completely before you think about the order at all.
1.A filter is switched on to show only amounts of more than 15. How many rows are showing?
Have a go
Have a go on your own. This sheet is sorted by column B, smallest first. Which name is in the bottom row?
A B 1 jug 60 2 pot 15 3 cap 42 4 mug 27
Ready to practise?
Eight questions on what you have just read. Nothing is timed, and you can play as many times as you like.