Excel Filter Based on 2 Criteria: AND or OR

Formulas & functions · Updated November 19, 2026

To filter Excel data using two criteria, apply a filter to two columns: Excel combines those conditions with AND, showing rows that meet both. If both criteria apply to one column, choose multiple values or use Text Filters, Number Filters, or Date Filters to set two conditions; use OR when either condition should qualify. For more control, Advanced Filter and the FILTER function can also handle two criteria.

What AND and OR Mean in a Filter

The key question is whether a row must satisfy both conditions or only one. For example, a sales list might need to show orders from the East region with a value of at least $100. A row qualifies only if its region is East and its value is $100 or more. That is an AND condition.

If the request is to show orders from the East region or orders worth at least $100, either condition is enough. That is OR. The results can include a row meeting both conditions, but it does not have to meet both.

Excel’s regular column filters work naturally for these cases:

  • Different columns: Filter each column. Excel displays rows that meet the selected criteria in all filtered columns, so this is AND.
  • Several selected values in one dropdown: Excel displays rows matching any selected value in that column, so the selections work as OR.
  • Two comparisons in one column: Use the column’s Custom Filter dialog and select AND or OR to connect the comparisons.

Deciding which logic is intended prevents a common mistake: selecting two criteria and assuming Excel will interpret them the way you mean. The filter interface has different behaviors depending on whether the conditions are in the same column or separate columns.

Filter Two Different Columns with AND

For a straightforward AND filter, use a header row and a consistent table of data. Click a cell in the list, then choose Data > Filter. Excel adds a dropdown arrow to each header. Open the first column’s dropdown and select its criterion, then open the second column’s dropdown and select the other criterion. The remaining visible rows meet both filters.

  1. For an example, imagine columns named Region and Sales. Open the Region dropdown and select East.
  2. Open the Sales dropdown and choose Number Filters > Greater Than Or Equal To, then enter 100.
  3. Confirm the dialog. The visible records are in the East region and have sales of at least 100.

Each filter is applied to the same list, and applying another column filter narrows the current results. The order does not change the final answer: filtering Sales first and Region second produces the same qualifying rows. To remove a condition, open its dropdown and choose Clear Filter From followed by the column name. To remove all filters at once while keeping the dropdowns, use Data > Clear.

For text, select or clear values in the dropdown checklist, or use Text Filters for rules such as Begins With or Contains. For dates and numbers, the dropdown offers relevant comparison options. The exact available choices depend on the type of values in the column. A mixed column—for example, dates stored as text alongside real dates—may not offer the expected date behavior.

Set Two Conditions in the Same Column

When both rules concern one field, open that field’s dropdown and choose a suitable custom filter command. For a text column, choose Text Filters > Custom Filter. For a numeric column, choose Number Filters > Custom Filter. Date columns have date-specific choices, and the labels can vary with the type of filter you select.

The Custom AutoFilter dialog provides two comparison boxes and an AND/OR choice. Suppose a quantity column should show values from 10 through 25. Set the first comparison to is greater than or equal to 10, the second to is less than or equal to 25, and connect them with AND. Both comparisons then have to be true for a row to remain visible.

To show text values beginning with A or beginning with B, set those two text comparisons and connect them with OR. In some cases, selecting the relevant distinct values directly in the dropdown checklist is quicker. The checklist is useful for known values; the custom dialog is better for ranges, partial text matches, and comparisons such as greater than or less than.

Keep in mind that two conditions in one Custom AutoFilter apply to the one column whose dropdown you opened. If the task is Region = East and Sales is at least 100, use filters on both columns rather than putting both conditions into the Region dialog.

Use the FILTER Function for a Live Result

If you want matching rows to appear in a separate area and update when the source values change, use the FILTER function instead of hiding rows with dropdowns. FILTER is available in Microsoft 365 and Excel 2021 or later. It returns a dynamic array, so the output spills into adjacent cells; leave enough empty space below and to the right of the formula.

Assume the source table is in A1:D100, with headers in row 1, Region in column B, and Sales in column D. Suppose the data includes an East order for 120, a West order for 150, an East order for 80, an East order for 140, and a West order for 90. Enter this formula outside the source data:

=FILTER(A2:D100,(B2:B100="East")*(D2:D100>=100),"No matches")

The multiplication sign combines the two TRUE/FALSE tests as AND. In the example, the East orders worth 120 and 140 are returned; the East order worth 80 fails the sales test, and the West orders fail the region test. The final argument displays No matches if no row qualifies. The source and criteria ranges must have the same number of rows.

For OR logic, use addition between the tests. With the same layout, this example returns rows where the region is East or sales are at least 140:

=FILTER(A2:D100,(B2:B100="East")+(D2:D100>=140),"No matches")

That result includes the East orders and the West order for 150. A row meeting both tests still appears once. In these formulas, the conditions are array calculations applied row by row. If FILTER returns a #SPILL! error, clear cells blocking its output. If a criterion uses text, check spelling and extra spaces; if it uses numbers, confirm that the source values are numbers rather than text.

Use Advanced Filter for More Complex Criteria

Advanced Filter is useful when you want to copy matching records to another location or express more elaborate combinations of criteria. It is available in desktop Excel for Windows and Mac; it is not the same as the ordinary dropdown filter. Set up a small criteria range with headers that exactly match the source headers, then enter the conditions below those headers.

Advanced Filter reads criteria arranged in a grid. Conditions on the same criteria row are combined with AND; conditions on different rows are combined with OR. For example, put Region = East and Sales >= 100 on the same row to require both. To request Region = East OR Sales >= 100, place those conditions on separate rows under their matching headers. When the filter has more than one OR branch, each row represents a branch, and conditions across that row must all be met.

To run it, select a cell in the source list and choose Data > Advanced. In the dialog, check the List range and Criteria range, then choose Filter the list, in-place or Copy to another location. If copying, specify a destination outside the source list. Header text in the criteria range must match the source exactly; a misspelled or extra-space header can make the criteria fail. Advanced Filter is most practical for a one-time or manually refreshed extraction, rather than a result that recalculates automatically like FILTER.

Choose the Best Method for the Job

For most everyday tasks, the standard dropdown filters are the quickest choice. They are easy to inspect and remove, and they keep the original rows in place while hiding those that do not match. Use a Custom AutoFilter when two comparisons apply to one column, such as a date range or a range of quantities.

Choose a formula when the matching records need to appear elsewhere and update as the source changes. A formula is also convenient when its criteria come from cells, so a user can change a selection without reopening filter menus. Choose Advanced Filter when the criteria grid better expresses several AND/OR branches or when you need to copy filtered records as a separate static result.

  • Quick interactive review: Data > Filter.
  • Two comparisons in one field: The field’s Custom Filter dialog.
  • Automatically updating extracted rows: FILTER in Microsoft 365 or Excel 2021 and later.
  • Multi-branch criteria or a copied extract: Advanced Filter in desktop Excel.

On Windows, Ctrl+Shift+L toggles the filter arrows for a selected range or table. On Mac, use Data > Filter if you do not want to rely on a shortcut, since keyboard shortcuts can vary with keyboard settings. Excel for the web supports ordinary filters, but Advanced Filter is a desktop feature; menus and capabilities can differ between the web app and desktop versions.

Common Problems and How to Fix Them

If a filter gives no results, first verify the intended logic. A row must meet every active column filter, so a narrow condition in either column can exclude it. Clear the filters and reapply them one at a time to see which condition removes the expected rows. For an OR request across different columns, applying two ordinary column filters will not do: those filters impose AND. Use a FILTER formula or an Advanced Filter criteria range instead.

Also check the data itself. Spaces before or after text can prevent an exact match. Numbers or dates stored as text may sort and filter differently from real numeric or date values. Blank cells, inconsistent headers, and values that look alike but use different spelling can also change the results. If a filter dropdown seems to omit values, confirm the range includes all rows and that there are no completely blank rows separating the list.

Convert a well-structured list to an Excel table with Insert > Table if it should grow as new records are added. Tables include filter arrows by default and generally expand to include adjacent new data. For an ordinary range, confirm that the selected filter range covers all relevant columns and rows before filtering. Filters hide rows; they do not delete them. To see everything again, clear the filter rather than copying visible rows unless a separate extract is actually needed.

Frequently Asked Questions

Can I filter two columns at the same time in Excel?
Yes. Turn on Data > Filter, then set a condition in each column dropdown. Excel shows rows satisfying both column filters, which is AND logic.

How do I use OR between two Excel filter criteria?
For two values in one column, select both values in its dropdown checklist or connect two comparisons with OR in Custom AutoFilter. For OR conditions across different columns, use Advanced Filter with conditions on separate criteria rows or a FILTER formula that adds the condition tests.

Can I filter a column between two numbers or dates?
Yes. Open that column’s dropdown, choose its Number Filters or Date Filters option, and set a lower and upper comparison in Custom Filter. Connect the comparisons with AND so the value falls within the range.

Why does filtering two columns return no rows?
Excel combines separate column filters with AND, so a row must match both. Check that the criteria are not too restrictive and that the source values use the expected spelling, data type, and date or number format.

Does Excel’s FILTER function change or hide the original data?
No. FILTER returns matching rows as a separate dynamic-array result. The source records stay where they are, and the result updates when its source data or criteria change. It requires Microsoft 365 or Excel 2021 and later.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.