Filter the rows, then sort them

Up to five conditions, joined with AND or OR, over fourteen operators including regex and is-in-list. Then up to three sort keys, each with its own direction. Numbers compare as numbers rather than as text, so 9 comes before 10, and blank cells sort to the bottom in both directions because a missing value is not the smallest one.

Numbers that compare as numbers

The single most common bug in a browser-based filter is comparing everything as text. It is invisible until it is catastrophic: filter for amount > 100 over text and you keep 99.99, because the string "99.99" sorts after "100". Sort a column of prices as text and you get 1, 10, 100, 1000, 11, 2.

Every comparison here checks both sides first. If they both read as numbers, the comparison is numeric. If either does not, it falls back to text, which is what you want for an ISO date or a version string. The decision is made per column and per condition rather than once for the whole file, so a file with a numeric column and a text column behaves correctly in both.

The same rule applies to sorting, and it is decided once per sort key over the rows that survived the filter. Deciding per cell is how a sort ends up unstable, with two rows swapping places depending on which comparison the algorithm happened to make first.

The fourteen operators

Six comparisons, six text tests, and two emptiness checks:

=            equals                 ≠   does not equal
<            less than              >   greater than
≤            at most                ≥   at least
contains     does not contain
starts with  ends with
matches regex
is in list   (comma separated)
is empty     is not empty

The six comparisons are numeric when both sides read as numbers and textual otherwise, which makes < and > useful on ISO dates as well: order_date ≥ 2024-03-01 works because ISO dates sort correctly as text. That is the strongest argument for ISO dates there is, and it is why the date normalizer defaults to them.

Worked example

Ten orders. Two conditions joined with AND, and two sort keys:

Keep rows where  region      is in list   East, West
and where        amount      ≥            100
Sort by          region      A to Z
then by          amount      Z to A

Out of ten rows, five survive:

order_id,customer_id,order_date,region,product,quantity,amount
1003,C001,2024-01-11,East,Thing,2,240.00
1007,C005,2024-03-03,East,Widget,1,199.99
1001,C001,2024-01-05,East,Widget,3,120.50
1005,C002,2024-02-14,West,Doohickey,4,310.20
1009,C009,2024-03-21,West,Gadget,3,132.40

East before West, and inside each region the largest amount first. Note that 240.00 comes before 199.99, which is only true if the comparison is numeric: as text, "240.00" sorts before "199.99" too, but "88.00" would have sorted above both of them.

The strip above the result reads:

10 rows in · 5 out · 2 conditions joined with AND · sorted by region, amount desc

AND, OR, and the grouping question

AND keeps rows matching every condition, which narrows as you add more. OR keeps rows matching at least one, which widens. The choice applies to all five conditions at once.

What this deliberately does not offer is arbitrary nesting: (A AND B) OR (C AND D) is not expressible here. That is a real limitation and it is a deliberate one. A visual builder for nested boolean logic is a genuinely difficult interface to make legible, and every attempt at one ends up as a tree of indented boxes that is harder to read than the expression it represents.

Two ways round it. Most nested conditions can be flattened: (region = East AND amount > 100) OR (region = West AND amount > 100) is just region is in list East, West AND amount > 100, which is why the in-list operator is here. When it genuinely cannot be flattened, chain two filter steps in a pipeline, or write the WHERE clause in SQL, which is the right tool for arbitrary boolean logic and always will be.

Sorting details that matter

Blanks sort last in both directions. Ascending or descending, empty cells go to the bottom. This is what spreadsheets do and it is right: a missing value is not smaller than every number, it is absent, and having it lead your descending sort is never useful.
The second key only breaks ties in the first. Sort by region then amount and the amounts are only ordered within each region. That is what a multi-column sort means, and it is worth saying because people sometimes expect the second key to reorder everything.
Text sorts by code point, not locale. Uppercase letters come before lowercase, and accented characters sort after plain ones. That is less pretty than a locale-aware sort and it has one large advantage: it produces the same order in every browser on every machine, so a file regenerated next month differs only where the data differed.
The limit applies after sorting. Sort descending by amount and keep the first ten, and you have the top ten by amount. Applied before the sort it would be the first ten rows of the file, which is a different and much less useful thing.

The three operators worth knowing about

Is in list takes a comma-separated set and keeps rows matching any of them. East, West, North in one condition instead of three ORs, and it composes with AND, which three ORs would not.
Matches regex is the escape hatch: ^(ACME|Globex) for a prefix set, \\d{5}$ for a trailing postcode, ^$|^N/A$ for two kinds of empty. An invalid pattern matches nothing and says so above the result rather than throwing the whole run away, so a half-typed pattern does not clear your screen while you type it.
Is empty and is not empty test the cell after trimming, so a cell holding three spaces counts as empty. They take no value, and the value box is ignored when they are chosen. Finding the rows with a missing id is the single most common data-quality question there is.

Letter case is ignored by default across all of the text operators, which is almost always what people mean. Switch Letter case to Exact when it matters, and it applies to the regex too.

Frequently Asked Questions

Why did filtering for amount greater than 100 keep a row with 99.99 somewhere else?

Because that tool compared the values as text, and as text 99.99 sorts after 100 for the same reason that banana sorts after apple. This page checks whether both sides read as numbers first and only falls back to text when they do not. It is worth testing on any filter tool you use: filter for greater than 100 on a column containing 99 and see what you get.

Can I do (A and B) or (C and D)?

Not directly. All five conditions are joined by the same connector. Most nested logic flattens: two AND groups differing in one column usually become one condition with the in-list operator. When it genuinely will not flatten, chain two filter steps in a pipeline, or write the WHERE clause in SQL in the browser, which handles arbitrary boolean logic properly.

Where do blank cells go when I sort?

To the bottom, whichever direction you sort in. A blank is a missing value rather than the smallest one, so leading a descending sort with a screenful of empty rows would be wrong. This matches what spreadsheets do and what most people expect.

Is the sort stable?

Yes. Rows that compare equal on every sort key keep their original relative order, so sorting by region and then separately by date gives a predictable result rather than reshuffling ties. The numeric-or-text decision is also made once per column rather than per cell, which is the other thing that makes a sort unstable.

How do I get the top ten by revenue?

Sort descending on the revenue column and set the limit to 10. The limit is applied after both the filtering and the sorting, so you get the ten largest surviving rows rather than the first ten rows of the file. Combine it with a filter to get the top ten within a category.

My regular expression is not matching anything.

The pattern is checked before it runs, and an invalid one is reported above the result rather than silently matching nothing. If the pattern is valid but still matches nothing, remember that matching is case-insensitive by default, that the pattern is not anchored unless you anchor it, and that it tests the raw cell including any leading spaces the file has.

Cut it down and put it in order

Five conditions, fourteen operators, three sort keys, and comparisons that treat numbers as numbers.

Back to the filter tool