Filtering and searching a sheet
A filter narrows a sheet to the rows that match. Filters on different columns apply together, so each one you add can only reduce the result. Search looks across the sheet rather than at one field. Both run over the whole sheet in the database, not over the rows that happen to be loaded — and what you type is treated as a value, never as a pattern.
Filter, search, and the difference#
- A column filter
- A condition on one field: this stage, that owner, a date after some point. Precise, and you can see exactly what it is doing.
- Search
- One box against the sheet as a whole. Useful when you know the name and not the field it lives in.
- Together
- Search first to find the neighbourhood, then filter to state the condition you actually mean. A saved view is worth making out of the second, not the first.
The two are complementary rather than competing. Search is how you find a row; a filter is how you define a set of rows you will do something to — and a set is what bulk actions need.
How several filters combine#
Filters on different columns all have to be satisfied. Adding one never widens the result, which makes an empty sheet easy to reason about: the last condition you added is the one to remove first.
Add one filter and read the row count.
Result You know what that condition costs before adding another.
Add the next, and read the count again.
Result A count that drops to zero tells you which pair of conditions contradict each other.
If the sheet is empty and you expected rows, clear filters one at a time from the most recent.
Result You find the offending condition rather than starting again from nothing.
What you type is a value, not a pattern#
There is no wildcard syntax in the filter box. A % you type is a per cent sign and matches a per cent sign; an underscore is an underscore; an asterisk is an asterisk. If you want partial matching, that is what the contains-style operator is for, chosen explicitly rather than implied by a character you happened to include.
The reason is not stylistic. Filter values travel as parameters, never spliced into a query, so a value that looks like syntax stays a value all the way down. That is what makes it safe to paste a customer's reference straight out of an email into a filter without wondering what the punctuation in it might do.
Filtering runs over the whole sheet#
Rows are virtualised, so only a screenful is rendered at a time. Filtering and counting still happen over the entire sheet in the database, which is the difference between an answer and an artefact of how far you had scrolled.
Connect has been on the wrong side of this before. A list that read a fixed slice of rows and then counted, filtered and paged within it reported a Closed tab of zero on a workspace that had plenty, and an empty page five. The lesson, written up in headline numbers that stop at the page size, is that a count computed over a page is not a count.
Questions#
Do filters combine with OR anywhere?
Conditions on different columns apply together. Where you want alternatives, the practical route is a wider condition on one column, or two views rather than one — which also documents the two cases for whoever reads them next.
Does filtering change what is exported?
An export follows the set you have defined, which is the main reason to build the filter before exporting rather than trimming a spreadsheet afterwards. Importing and exporting covers what comes out and in what shape.
Why did search find a row that my filter then hid?
They are asking different questions. Search matched text somewhere on the row; the filter is a condition on one field, and that field did not satisfy it. Both answers are correct, which is why the two controls stay separate.