In Excel: build a criteria range with the same headers — conditions side by side mean AND, conditions on separate rows mean OR — then run Data ▸ Sort & Filter ▸ Advanced.
On this page7
Copy the header cells of the columns you want to test into an empty area of the sheet.
Under those headers, put conditions on one row for AND, and on separate rows for OR.
Click inside the data, then Data ▸ Sort & Filter ▸ Advanced.
Set the List range and the Criteria range; choose Copy to another location if the result should be written elsewhere, and tick Unique records only to drop duplicates.
For a self-updating alternative, use FILTER with * for AND and + for OR between the conditions.
What this does
AutoFilter applies one set of conditions per column and combines them with AND. Advanced Filter reads a criteria range you lay out on the sheet, which lifts both limits: several conditions can be combined across columns and rows, results can be written to another location, and duplicates can be dropped in the same pass. In Microsoft 365 the FILTER function expresses the same logic as a formula that updates itself. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “advanced filter multiple criteria”. Start on a copy or a tiny sample, keep the affected filter visible, and compare the result with the tool above before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a data step that keeps your analysis trustworthy, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
F1:G3 holds the headers Region and Amount. Row 2 reads West and >1000; row 3 reads East and >5000. Data ▸ Advanced with List range A1:D200 and Criteria range F1:G3 returns rows that are (West AND over 1000) OR (East AND over 5000). The formula equivalent is =FILTER(A2:D200,((B2:B200="West")*(C2:C200>1000))+((B2:B200="East")*(C2:C200>5000))). Once a question needs two columns and an OR, AutoFilter starts producing confidently wrong answers, and the criteria range is the only way to see on the sheet what was actually asked. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.
In Google Sheets
Some functions on this page are newer additions to Excel: they are in current Microsoft 365 and Excel for the web, while older perpetual Excel versions return #NAME?. Google Sheets maintains its own function list, so confirm each function exists there before relying on the same formula. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. Treat “advanced filter multiple criteria” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.
Common mistakes
- Criteria headers that do not match the data headers exactly — a trailing space is enough to make the filter return everything.
- Leaving a blank row inside the criteria range, which matches every record.
- Running Copy to another location from the source sheet when the destination is a different sheet; start the command from the destination sheet instead.
- Writing a computed condition under a matching header — formula criteria need a header that is blank or different from any column name.
Frequently asked questions
How do I filter one column on two values?
Put each value on its own row under the same criteria header. Separate rows are combined with OR.
Can I use wildcards?
Yes. * matches any run of characters and ? matches one, so West* matches Western and Westgate.
How do I filter with a formula condition?
Leave the criteria header blank (or use a name no column has) and enter something like =C2>AVERAGE($C$2:$C$200) beneath it.
How do I get the list back?
Data ▸ Sort & Filter ▸ Clear removes the filter and shows every row again.