Excel Filter Multiple Criteria in One Column: 5 Methods

Formulas & functions · Updated November 19, 2026

To filter one Excel column by multiple criteria, open its filter dropdown and select several values, or choose a custom filter to combine two rules with AND or OR. For more complex conditions, use Advanced Filter or a formula; the right choice depends on whether you want to hide rows, extract a separate result, or keep a reusable criteria setup.

Quick answer: choose the kind of criteria

Excel’s standard AutoFilter handles several common meanings of “multiple criteria.” If you want rows matching any of several listed values—for example, Status is Open or Pending—select both values in the column’s checklist. If you need a rule such as “greater than 100 and less than 500,” use the column’s custom number filter. If you need more than two rules, or need complex combinations, Advanced Filter or a formula may be a better fit.

To start, click a cell in your data and choose Data > Filter on the Windows ribbon. Excel adds dropdown arrows to the header row. Open the arrow for the column you want to filter, then choose the method that matches your criteria. If the data is an Excel table, filter arrows are generally already present; use the arrow in the relevant table header.

  • Several exact values: select multiple entries in the dropdown checklist.
  • Two comparisons on one field: use Text Filters or Number Filters and select AND or OR.
  • Many or more complicated conditions: set up an Advanced Filter criteria range or use a formula.

How Excel combines filter criteria

It helps to separate “criteria within one column” from filters applied to different columns. Selecting Open and Pending in the same Status checklist means a row can match either value: Status is Open or Status is Pending. By contrast, filtering Status to Open and Region to East keeps rows that meet both column filters: Status is Open and Region is East.

Within a custom filter dialog, you can explicitly choose AND or OR between two conditions. AND requires a row to meet both rules; OR accepts a row that meets either one. For example, a price filter with “greater than 100” AND “less than 500” describes a range, while “less than 20” OR “greater than 900” selects values at either extreme. The choice changes the result, so check the connector before applying the filter.

Filtering hides rows that do not match; it does not delete them. Clearing a filter restores the hidden rows. This distinction matters when you copy visible results, calculate totals, or share a workbook: confirm whether the recipient should see the filtered view or the complete dataset.

Select several values from the filter dropdown

For a list of known values, the checklist is usually the quickest method. Open the column’s dropdown, clear (Select All), check each value you want to keep, and select OK. For example, in a Status column, check Open and Pending to show either status while excluding Closed.

The checklist reflects values in the column, not a set of independent rules. If a value is missing, check whether the source data contains extra spaces, spelling variations, or values stored in a different form. “Open” and “Open ” (with a trailing space) can behave like distinct text values. A blank entry may also appear if the source range includes empty cells.

For a long list, use the search box in the dropdown to find a value, then select the values you need. Be careful not to assume that searching changes the underlying data or permanently limits the filter: it is a way to locate checklist entries. When finished, review the checked values before confirming.

To remove the filter for just that column, open its dropdown and choose Clear Filter From followed by the column name. To remove AutoFilters from the range, use Data > Filter to turn filtering off. The exact menu wording can vary slightly by Excel build.

Use custom Text or Number Filters

When criteria are comparisons rather than a handful of listed values, open the column dropdown and choose Text Filters or Number Filters. The menu depends on the data type Excel recognizes. Choose a rule such as Contains, Begins With, Equals, or Greater Than, enter the condition, and use the second condition and AND/OR controls when needed.

For instance, to show amounts from 100 up to but not including 500, choose a number condition greater than or equal to 100, choose AND, and set the second condition to less than 500. To show names containing either “North” or “West,” use a text custom filter with Contains for the first word, select OR, and enter the second word. This dialog is convenient for two rules, but it is not designed as an unlimited list of conditions.

Text filters can use wildcard characters in relevant criteria: an asterisk (*) represents any sequence of characters, and a question mark (?) represents one character. To search for a literal wildcard, precede it with a tilde (~). For example, a criterion that includes a literal asterisk needs the tilde escape. Wildcards are useful for patterns, but a simple checklist selection is clearer when you want a few exact values.

Date columns have their own date filter options, including choices for date periods and custom conditions. If Excel treats dates as text, those date-specific options may not behave as expected. Correct the source values first rather than relying on a comparison against text that only looks like a date.

Use Advanced Filter for a criteria range

Advanced Filter is useful when you want a visible criteria area, more than two conditions, or the option to copy matching records to another location. It is available in the Windows desktop version through Data > Advanced. Arrange the source as a rectangular list with one header row, and make a separate criteria range whose header matches the source column header exactly.

For an OR condition on Status, make a criteria range with the heading Status and put Open and Pending on separate rows beneath it. In Advanced Filter, set the list range to the source data and the criteria range to that small block. Conditions on separate rows are alternatives: a row matches Open or Pending. Choose to filter the list in place or copy the matching records to another location, then apply the dialog.

For AND conditions, put criteria on the same row in the criteria range. To apply two comparisons to the same source field, repeat its heading in adjacent criteria columns, then put the first and second rules beneath the matching headings on the same criteria row. This layout is less intuitive than the dropdown, so keep the criteria block labeled and close to the data. Advanced Filter reads the range as a set of instructions; it does not automatically update a copied result every time source data changes. Reapply it when the source or criteria change.

Advanced Filter is a desktop feature; do not expect the same dialog in Excel for the web. On Mac, use the Data tab and look for the Advanced filtering command if it is available in your Excel build. If it is not present, use AutoFilter or a worksheet formula instead.

Return matching rows with FILTER

If you want a separate, automatically recalculating result rather than hiding source rows, the FILTER function can return records that meet several conditions. It is available in Microsoft 365 and Excel 2021 or later, including Excel for the web. Put the formula in an empty area outside the source data so the returned rows have room to spill.

Assume A1:C7 contains a table with headers ID, Status, and Region, and rows 2–7 contain: 101/Open/East, 102/Pending/West, 103/Closed/East, 104/Open/West, 105/Pending/East, and 106/Open/North. With those values in A2:C7 and statuses in B2:B7, enter this formula in an empty cell:

=FILTER(A2:C7,(B2:B7="Open")+(B2:B7="Pending"),"No matches")

The plus sign combines the two tests as OR: a matching row has Open or Pending status. The formula returns five records—IDs 101, 102, 104, 105, and 106—and excludes 103, whose status is Closed. The optional third argument displays No matches if no row qualifies. In a FILTER include test, adding Boolean tests represents OR, while multiplying tests represents AND.

FILTER returns a live result, but it does not change or hide the original rows. If the formula shows a spill error, check that the cells where results need to appear are empty and not blocked by merged cells. A regular AutoFilter is simpler when you only need to inspect the existing list.

Choose the method that fits the job

Use the dropdown checklist for several exact categories, and use a custom filter for two straightforward comparisons. Those built-in options are easy to understand and preserve the source data. Use Advanced Filter when a criteria range makes the logic easier to document, when there are several conditions, or when you need a one-time copied extract. Use FILTER when the output should update as the source changes and a separate result range is useful.

For repeated reporting, consider converting a well-formed data range into a table with Insert > Table. Tables expand as rows are added and make headers and filter controls easier to manage. A formula using fixed ranges, such as A2:C7, will not automatically include new rows beyond its specified range; a table-based approach or appropriately extended ranges can avoid that maintenance issue.

Common problems and how to fix them

  • The filter misses an expected record: look for leading or trailing spaces, inconsistent spelling, blank cells, or text values that resemble numbers or dates. Clean or standardize the source column.
  • Only one condition seems to work: inspect whether the custom-filter connector is set to AND or OR. With Advanced Filter, confirm whether conditions belong on the same row or separate rows.
  • Filtering one field removes too many rows: another column may still have an active filter. Clear other column filters or use Data > Clear to restore the full list before checking the result.
  • Rows outside the data are unaffected: Excel may have detected only part of the list. Keep the data in a continuous rectangle with one header row and no completely blank rows or columns separating records.
  • A formula does not update as expected: check whether its source range includes new records, whether calculation is set to automatic, and whether the spill area is blocked.

When you need a count or total of visible records, do not assume an ordinary calculation responds to AutoFilter. Functions such as SUM can include values in hidden rows. For filter-aware totals, SUBTOTAL is commonly used; select the appropriate function number for the calculation and confirm whether manually hidden rows should also be excluded.

Frequently Asked Questions

Can I filter one Excel column for more than two values?

Yes. For exact values, check as many entries as needed in the dropdown checklist. The two-condition limit applies to the standard custom-filter dialog, not the checklist. For more elaborate logic, use Advanced Filter or FILTER.

How do I filter for either of two words?

Open the column dropdown, choose Text Filters > Custom Filter, enter the first text rule, select OR, and enter the second. If the words are complete values in the column, selecting both from the checklist is often simpler.

How can I filter for a range of numbers?

Choose Number Filters > Custom Filter, set the lower comparison, select AND, and set the upper comparison. Use greater than or equal to and less than or equal to if both endpoints should be included.

Do multiple column filters use AND or OR?

AutoFilter selections across different columns are combined as AND. A row must pass each active column filter. Within a single column’s checklist, selected values work as OR alternatives.

Does filtering delete rows?

No. AutoFilter hides rows that do not match; clearing the filter shows them again. Advanced Filter can copy matching records elsewhere, and FILTER can return a separate result, but neither requires deleting the source rows.

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.