To filter an Excel table by multiple criteria, open a column’s filter arrow and set its rule; rules on different columns are combined as AND conditions, while selected values in one column act as OR choices. For more flexible OR logic across columns, use Data > Sort & Filter > Advanced, or use the FILTER function in Microsoft 365 to return a live results list.
What “multiple criteria” means in Excel
A criterion is a condition that a row must meet, such as Region equals West, Status equals Open, or Sales is greater than 1,000. Combining criteria can mean different things, so decide whether your rules require AND or OR before applying them.
- AND: every condition must be true. For example, show orders from the West region whose status is Open.
- OR: at least one condition must be true. For example, show orders from either the West or East region.
- Mixed logic: combine groups, such as West or East region, but only when the order is also Open.
This distinction matters because Excel’s ordinary table filters combine different columns with AND. A row must satisfy the active filter on every filtered column to remain visible. Within a single column, selecting several listed values generally means any selected value is acceptable, which is OR logic.
Apply basic filters to an Excel table
In Excel for Microsoft 365 on Windows, click a cell in the table and use the arrow in a header to open that column’s filter menu. If the arrows are not visible, select a cell in the table and choose Data > Filter. A newly created Excel table normally displays filter arrows automatically.
- Open the filter arrow for the first column.
- Select or clear checkboxes to choose the values to keep, or choose a text, number, or date filter for a condition.
- Choose OK, then repeat for each other column that needs a rule.
For example, filter Region to West and then filter Status to Open. The remaining rows must match both rules. To show either West or East, open the Region filter and select both values; this does not require two separate filters. The filter menu’s search box can help locate a value in a long list.
Text Filters, Number Filters, and Date Filters offer comparisons such as Contains, Equals, Greater Than, and Before. The available choices depend on the data type Excel recognizes. A date column may offer calendar-based options, while a text column may offer Begins With or Contains. Choosing a comparison is useful when manually checking many individual checkbox values would be awkward.
Combine conditions within one column
For two conditions on the same field, choose the relevant filter type and then use Custom Filter, where available. For instance, a quantity column can be filtered to show values greater than 10 and less than 50. The dialog lets you choose whether the two conditions are joined by AND or OR.
Use AND when a value has to fall inside both boundaries. Use OR when either condition is enough, such as showing dates before January 1 or after December 31 of a specified year. Check that the selected comparison operators match the intended boundary: Greater Than excludes values equal to the limit, whereas Greater Than Or Equal To includes them.
When the task is simply to include several exact text values in one field—such as East, West, and Central—select those values in the column’s checkbox list. That is often clearer than creating separate text conditions. Checkbox selection works for exact listed values; it is not the same as a Contains rule, which can match text that appears as part of a longer entry.
Filter across different columns
Excel combines active filters from separate columns using AND. Suppose a table has Customer, Region, Order Date, and Status fields. Applying Region equals West, Status equals Open, and Order Date is on or after January 1, 2026 leaves rows that satisfy all three conditions. Excel hides the other rows; it does not delete them.
Each column’s menu shows its own current filter state. To broaden a result, revise the relevant rule rather than assuming a later filter replaces the earlier one. To remove just one condition, open that column’s menu and choose Clear Filter From [column name]. To clear all table filters, use Data > Sort & Filter > Clear. Clearing a filter makes hidden rows visible again, although it does not necessarily remove sorting.
Filters are useful for temporarily inspecting a subset of a table, such as reviewing overdue items or checking a region’s sales. If other people need to see the same subset, document the criteria or use a saved view where available; a filtered table is still the original data, not a separate report.
Use Advanced Filter for flexible OR rules
Advanced Filter is useful when ordinary dropdowns cannot express the desired combination conveniently. In Excel for Microsoft 365 on Windows, prepare a small criteria range with headers that match the table’s column headers, then choose Data > Sort & Filter > Advanced. You can filter the list in place or copy matching rows to another location. Keep the criteria range separate from the list and include the matching header names.
The key rule is that criteria on the same row are AND conditions, while separate criteria rows are OR conditions. For example, to find orders where Region is West and Status is Open, put those two criteria under their respective headers on one criteria row. To find West-and-Open orders or East-and-Open orders, use two rows: one with West and Open, the other with East and Open. The repeated Status condition makes the logic clear.
Advanced Filter is also helpful for more complicated combinations, such as (Region is West AND Status is Open) OR (Region is East AND Sales is greater than 1,000). Put each AND group on its own criteria row. Criteria headers must correspond to the list headers; a misspelled or differently spaced heading can prevent the intended condition from working.
Choose Filter the list, in-place when you want to hide nonmatching rows in the original range. Choose Copy to another location when you need a separate extracted result. The copied result is a snapshot, not a continuously updating formula output; run Advanced Filter again after the source data changes. Advanced Filter is a desktop feature and is not the same as the ordinary filter menus available in Excel for the web.
Return matching rows with the FILTER function
The FILTER function is a good choice when the result should update as the source table changes and should appear separately from the original data. It is available in Microsoft 365 and Excel 2021 or later. Enter the formula in an empty cell outside the source table, with enough clear space beneath and to the right for the returned rows. Dynamic array results cannot spill into cells occupied by another table or other content.
Assume the Excel table is named Orders, with columns Region, Status, and Sales. If the illustrative table contains one West/Open order with sales of 1,200, one East/Open order with sales of 900, and other rows that do not meet those conditions, this formula returns the matching West and East Open rows:
=FILTER(Orders,((Orders[Region]="West")+(Orders[Region]="East"))*(Orders[Status]="Open"),"No matches")
In the include argument, addition represents OR between the two region tests, and multiplication represents AND with the status test. Excel treats TRUE and FALSE results as 1 and 0 for this array logic. The example returns two rows; the optional third argument displays No matches if no row qualifies.
For a stricter condition, the following formula keeps only West orders that are Open and have Sales of at least 1,000. With the illustrative rows above, it returns the single West/Open row with sales of 1,200:
=FILTER(Orders,(Orders[Region]="West")*(Orders[Status]="Open")*(Orders[Sales]>=1000),"No matches")
Keep the criteria grouped with parentheses, especially when mixing addition for OR and multiplication for AND. The formula returns the table’s columns as written; to return only selected fields, build a formula using a suitable selection method or place the desired columns in a separate report. If the result displays a spill error, clear obstructing cells or move the formula. A formula-based result is an output range, not a filtered view of the source table.
Choose the right filtering method
- Use header dropdowns for quick inspection, exact value selection, and straightforward conditions across columns.
- Use Custom Filter for two comparisons on one field, such as dates within a range.
- Use Advanced Filter for complex AND/OR combinations or for a one-time copied extract.
- Use FILTER when a separate result should recalculate as source data changes.
Think about who will maintain the result, too. A colleague can usually understand visible filter arrows quickly, while a formula-based report needs its conditions explained and its spill area kept clear. Advanced Filter criteria ranges are explicit, but they must be rerun when a static copied extract needs refreshing. None of these methods changes the underlying values simply because a row is hidden.
Common problems and how to fix them
- A row seems to match but is missing: check every filtered column. A row must pass all separate column filters, and a filter may still be active from an earlier task.
- A value does not appear in the checkbox list: check for spelling differences, extra spaces, or inconsistent data types. Text “100” and numeric 100 can behave differently in some operations.
- A date filter gives unexpected results: confirm the cells contain real Excel dates rather than text that only looks like dates. Regional date formats can make entries ambiguous.
- The FILTER formula returns no rows: verify the table name and header spelling, confirm that at least one row meets every AND condition, and check whether text has unexpected spaces.
- The FILTER formula shows a spill error: clear cells in the destination spill range or move the formula to an open area outside a table.
- Advanced Filter returns too many or too few rows: check that AND conditions are on the same criteria row and OR alternatives are on separate rows, and verify the criteria headers.
One common misconception is that filtering removes records. Standard filters only hide rows that do not match; clearing the filter restores them. If you copy filtered rows elsewhere, however, that copied set is a separate extract and will not automatically track changes unless you use a dynamic formula or repeat the extraction.
Frequently Asked Questions
Can I filter an Excel table by two conditions at once?
Yes. Apply a condition in one column, then apply another in a different column. Excel displays rows that meet both conditions. For two conditions in one column, use that column’s Custom Filter option.
How do I filter for either of two values?
For two exact values in the same column, open its filter arrow and select both values. For conditions across columns, use Advanced Filter criteria rows or a FILTER formula to express the OR logic explicitly.
Can I filter by multiple criteria and keep the original data?
Yes. Table filters hide nonmatching rows without deleting them. A FILTER formula also returns matching rows elsewhere while leaving the source table intact.
Will a FILTER formula update when I add rows to a table?
When the formula refers to the Excel table by its structured references, the table reference expands as rows are added, and the dynamic result recalculates. Keep the output spill area clear.
Can I save a filtered result as a separate list?
Use Advanced Filter’s Copy to another location option for a one-time extract, or use FILTER for a result that recalculates. Copying and pasting visible rows also creates a separate static copy, which will not update automatically.
Related
- Excel Work Schedule Template: Build and Customize One
- Excel Work Timesheet Template: Build One That Adds Work Hours
- Excel Won’t Group Dates in a PivotTable? Fixes That Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Subtotal Filtered Rows: Formula, Steps, and FixesYes. In Excel for Microsoft 365 on Windows, use the SUBTOTAL function to calculate from rows that remain…
- Excel IF Blank: Formulas for Empty Cells and ResultsIf you want Excel to show one result when a cell is blank and another when it is…
- Rank Low to High in Excel: Formulas, Sorting, and TipsYes. To assign ranks with the lowest value as rank 1, use Excel’s RANK.EQ function with its order…
- Excel Formula to Count Cells With a Value, Blanks, or TextTo count cells that contain something in Excel, use =COUNTA(A2:A20), replacing A2:A20 with the range you want to…
- Excel PMT Function for Loans: Formula and ExamplesExcel’s PMT function calculates the regular payment for a loan with a constant interest rate and equal payments.…
- Rank in Excel by Multiple Criteria: Formulas and TipsWith primary scores in B2:B5, secondary scores in C2:C5, and a third tie-breaker in D2:D5, enter this in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.