Monday
Sorting and filtering without losing rows
No. 10 Keeping the Record 4 min read
Practical habits that keep farm spreadsheets honest when you sort or filter, so hidden rows, reordering and stale totals do not quietly change the record.
A sort or a filter changes what you see, not what exists. Rows are still there; some are just pushed out of view or moved to a new position. The trouble starts when a hidden row is treated as an empty one, or when a visible sequence is mistaken for the real order of the records. The habit that prevents most damage is simple: never submit, sum or share a sheet while a filter is active unless you have deliberately checked what is hidden.
What actually happens to a row when you sort or filter?
Sorting reorders rows by the value in one column. Filtering hides rows that do not match a condition. Neither deletes data, and both are reversible in a well-built sheet. In a spreadsheet exercise on farm data, sorting is used to order a feed column and filtering is used to display a single stock class, with the undo step used to return to the original view. That undo step is the tell. If the original view is the thing you need, the working view is temporary and should be treated that way.
The practical consequence: a filter is a lens, not an edit. Keep the lens and the record as two separate states, and you will not confuse them.
Why does the row number stop being a reliable identifier?
Row numbers are positional, so a sort moves them. A record that sat in row 14 can sit in row 6 afterwards. Any note, formula or printed list that refers to "row 14" now points at something else. This is the quiet failure mode: the reference still resolves, so nothing errors, but it resolves to the wrong record.
The fix is to give every record its own key that travels with it. A field name, a paddock code, or a unit label that you assign once and never sort away from the row. Then references follow the record rather than the position. This is the same discipline as naming land so others can follow your thinking, described in Naming units so others can follow.
How do I keep a total honest when rows are hidden?
Totals are where hidden rows cause real harm. A sum over a filtered range may count only visible rows, depending on how the range is referenced and which function is used. A total that looked right when everything was visible can change meaning the moment a filter is applied, without any number being edited.
Three checks catch nearly all of this:
- Clear the filter and read the total again.
- Confirm the total covers a fixed range, not only what is currently displayed.
- Compare a known subtotal against the grand total and see whether the difference is explained by the rows you know you hid.
This is the same logic behind Totals that can be rechecked: a total you cannot re-derive from its parts is not yet a record.
A short checklist for any working view
| Question | Safe habit |
|---|---|
| Is a filter active? | Check before submitting, summing or exporting |
| Does every record have its own key? | Sort on position only after the key exists |
| Is the total over a fixed range? | Re-derive it after clearing filters |
| Will someone else read this view? | Say in the sheet what is hidden |
| Do you need the original order back? | Know your undo or restore step |
When should I sort the underlying records rather than the view?
Sort permanently when the order itself carries meaning: a sequence of measurements along a transect, a list of photo points in walking order, or dates. Sort temporarily when you are only looking for something. A permanent sort on a key column is usually safe, because the key does not change. A permanent sort on a value column tends to age badly, because values get edited and edges of the order drift.
The test is whether the order would still be correct after the data changes. If not, it is a view, not a record.
How do filters interact with photos and dated notes?
Records that point to something physical need the same caution. A filtered list of photo points can look complete while three points are hidden, which is exactly the situation Photo points that stay comparable warns about. Dated and signed notes are the anchor here: the note carries its own timestamp and author, so it can be re-ordered without losing who wrote it or when.
If a filter can change which observations appear, the observations need a stable identifier that survives the sort. Otherwise a later reader cannot reconstruct what was actually recorded.
Where does the paper sheet fit in this?
The paper sheet is the honest baseline. It cannot be filtered, and its order is whatever was written down. Use it to check a spreadsheet that has been sorted heavily or filtered repeatedly. If the digital view and the paper sheet disagree, resolve the disagreement before either is treated as the record.
This pairing is the point of One paper sheet, one spreadsheet: one form is quick to edit, the other is hard to quietly change. Together they cover the other's weakness.
What should be built in before the season starts?
Decide three things early: the key field, the columns that may be sorted, and the totals that must always reflect everything. Write those decisions where the next person will find them. A column heading is a claim about what the column contains, and a claim that survives a filter is worth more than a tidy screen.
A further safeguard is to keep the working copy and the reference copy apart. The reference copy is never filtered and never sorted on a value column. When the working copy looks suspicious, compare against the reference rather than trusting the view in front of you.
The broader principle is the same one that makes a map usable: the reader should be able to tell what was measured, by whom, and what was left out. A filtered spreadsheet hides that last part by default. Restoring it deliberately, before anything is submitted or shared, is the whole habit.