Auto filter dropdowns answer simple questions. Advanced Filter answers the ones with compound logic: "show every order from the North region above 1,000, or anything flagged urgent regardless of region, but only from this quarter." RixSheets ships Advanced Filter for exactly these cases — a criteria range you write like a sentence, AND and OR logic that reads top to bottom, and the option to copy matching rows somewhere else entirely instead of hiding rows in the source table.
How the criteria range works
The criteria range is a small block that mirrors your table's headers:
- The first row holds header names copied from your table — spelled exactly the same.
- Each row beneath holds one combination of conditions.
- Conditions on the same row are joined with AND.
- Conditions on separate rows are joined with OR.
For a table with Region, Amount and Date columns, a criteria block reading North and 1000 on one row means "Region is North and Amount is above 1,000". Add a second row that says Urgent under a Status header, and the filter now also passes anything urgent — the OR branch.
Comparison operators work in criteria cells exactly as you would hope: values like ">=1000", "<>Cancelled" and ">2026-01-01" are all read as conditions. Text criteria match on "begins with" by default, so "North" also matches "North East" — narrow it with an exact-match form when that matters. Because the criteria are plain cells, building the block is ordinary spreadsheet work: copy the headers, type a few conditions, adjust until the preview shows the rows you meant.
Run an Advanced Filter
- Copy the table's header row to a clear area of the sheet, with a blank row above it.
- Type your conditions beneath the copied headers, using the same row for AND conditions and separate rows for OR branches.
- Click any cell inside the source table.
- Open Data, Advanced Filter.
- Set the criteria range to the block you built, headers included.
- Choose filter in place to hide non-matching rows, or copy to another location and pick a destination cell.
- Run it and review the result. Edit the criteria and re-run — nothing in the source changes when copying out.
The copy mode is the quiet superpower: your raw data stays pristine while the extract becomes a report fragment, a chart's source range or a pivot's input.
Reusing the extract as a living report
The copy-to-location mode turns Advanced Filter from a one-off into a repeatable report builder:
- Reserve a sheet for the extract and place the criteria block beside it.
- Point the filter's copy destination at a fixed cell on that sheet.
- When the underlying data updates, re-run the filter — the extract is rebuilt in place, same shape, same location.
- Point any chart or pivot at the extract once; it stays valid through every re-run.
Because the criteria live on the sheet, "how was this report filtered?" is answerable by looking, not by archaeology through dialogs. A colleague can tune the threshold from 1,000 to 2,500 and re-run without asking you what held the number — the number is right there, in a cell, doing its job.
Patterns worth stealing
| Goal | Criteria setup |
|---|---|
| One column, several allowed values | Same header repeated on multiple rows, one value each |
| Two conditions that must both hold | Both values on the same row |
| Either-or branches | Each branch on its own row |
| Exclude a value | Use the not-equal form of the comparison in the cell |
| Range between two numbers | Two comparisons on the same header, same row |
Because criteria are ordinary cells, you can change them with edits instead of dialogs — swap 1000 for 2500 and re-run, or point a cell at a threshold used elsewhere on the sheet.
In-place versus copy: choosing the mode
The mode choice changes what the tool is for:
| Mode | Source afterward | Best for |
|---|---|---|
| Filter in place | Non-matching rows hidden | Quick working views, then unfilter |
| Copy to another location | Source untouched | Extracts, reports, pivot sources |
In-place is the fast path when you are mid-analysis: filter, look, clear, move on. Copy mode is the deliberate path — the extract becomes a durable object with its own address, immune to whatever filtering happens in the source later. A practical middle ground many teams settle on: explore in place, then, once the criteria are right, switch to copy mode and make the extract official.
Two small mechanics prevent surprises. The destination for copy mode needs only its top-left cell — the filter sizes the output itself. And re-running with a fixed destination overwrites the previous extract completely, which is what you want for a repeatable report but worth knowing if something else occupied that range.
Where it fits with the rest of the toolkit
- Auto filter remains the right tool for quick single-column questions — see sort, filter and dedupe.
- Slicers and timelines serve interactive dashboards for other readers — see slicers and timelines.
- Advanced Filter serves the analyst: precise, repeatable, criteria-on-the-sheet extractions that feed Subtotal and Consolidate or a fresh pivot table.
One caution: an OR branch row with a blank cell under one header means "any value" for that header, not "empty" — blank criteria cells are ignored, which is usually what you want, but worth knowing when a filter returns more rows than expected.
What to explore next
- Sort, filter and dedupe — the everyday filtering toolkit.
- Slicers and timelines — visible controls for dashboard readers.
- Subtotal and Consolidate — summarize what the filter extracts.
- Pivot tables in RixSheets — pivot the extract instead of the raw rows.