Text Filter in Excel With Multiple Criteria Explained

Formulas & functions · Updated November 20, 2026

To filter text in Excel by multiple criteria, select your data, choose Data > Filter, open a column’s dropdown, and use Text Filters or Custom Filter to combine conditions with AND or OR. To filter across several columns, apply a filter to each column; Excel keeps rows that meet all of those column filters.

What a text filter does

Excel’s AutoFilter hides rows that do not match the conditions you set; it does not delete them or change the underlying cell values. Text filters are available from the dropdown on a column containing text. They let you find entries that equal, begin with, end with, contain, or do not contain a specified string. For example, a product column can be narrowed to descriptions containing the word blue.

A text condition generally does not have to match an entire cell. Contains blue can match Blue mug, navy blue pen, and BLUE notebook. Excel’s text filtering is not case-sensitive, so capitalization alone will not separate those results. If the column contains dates or numbers, Excel may show date or number filtering choices instead; use the criteria type that fits the values in that column.

Filtering is useful when you want to inspect or temporarily work with a subset of a list. Hidden rows remain in the worksheet, and clearing the filter makes them visible again. This differs from deleting rows, copying matching records to another sheet, or using a formula to produce a separate live result list.

Apply a text filter with two conditions

In Excel for Microsoft 365 on Windows, use these steps for a basic custom text filter:

  1. Make sure the list has one header row, with a distinct heading for each column, and avoid blank rows inside the data.
  2. Select a cell in the data range, then choose Data > Filter. You can also select the range first. Excel adds dropdown arrows to the headers.
  3. Open the dropdown for the text column you want to filter, point to Text Filters, and choose a condition such as Contains or Custom Filter.
  4. Enter the first condition. In the custom dialog, choose And or Or, then enter the second condition and select OK.

For example, if a Comments column should contain both the word urgent and the word customer, set the first condition to Contains urgent, choose And, and set the second to Contains customer. A row must satisfy both conditions to remain visible. If you want entries containing either term, choose Or instead. The filter dropdown’s menu wording can vary slightly with the selected data type, but the custom text-filter dialog provides the two-condition choice.

To turn AutoFilter off or on in Windows, press Ctrl+Shift+L while working in the range. This shortcut toggles the filter arrows; it is not the same as clearing the active criteria. On Mac, use Data > Filter if the shortcut differs on your keyboard or in your Excel setup. Excel for the web also provides filtering from the Data tab and column dropdowns, though the layout may look different.

How AND and OR work

The most important distinction is whether conditions are combined with AND or OR. With AND, a row must meet every condition. With OR, meeting either condition is enough. In a custom filter for one column, two conditions can express cases such as “contains blue and ends with mug” or “begins with A or begins with B.”

When you filter different columns one after another, Excel combines the active column filters with AND. Suppose a table has Product, Region, and Status columns. Filtering Product to contain blue, Region to equal West, and Status to equal Open displays only rows matching all three selections. A row with a blue product in the East will be hidden even if its status is Open.

A frequent misconception is that two values selected from the same dropdown are treated like two AND conditions. Selecting multiple items in the checkbox list normally means show rows matching any selected item, which is OR behavior. Use the custom text filter when you need to specify conditions such as contains this phrase and does not contain that phrase. For more than two conditions or more elaborate combinations, consider Advanced Filter or a formula.

Use wildcards for flexible text matching

Wildcards can represent unknown characters in filter criteria. In Excel’s supported text-filter criteria, an asterisk (*) represents any number of characters, while a question mark (?) represents one character. A tilde (~) escapes a wildcard when you want to search for the symbol itself rather than use it as a pattern.

  • *east* matches text with east anywhere in the cell, such as Northeast or East warehouse.
  • AB? matches a three-character value beginning with AB, such as AB7, but not AB12.
  • ~* searches for a literal asterisk character rather than treating it as a wildcard.

The Contains and Begins With menu choices are often easier than typing wildcard patterns because they describe the intent directly. Wildcards are particularly helpful in Custom Filter or Advanced Filter criteria when a match needs to be embedded in a broader condition. Remember that matching is not case-sensitive; wildcards do not make a text filter case-sensitive.

Filter across multiple columns

For common tasks, applying one filter per column is the simplest approach. If a worksheet lists orders, you might filter Customer to contain a name fragment, Region to equal West, and Notes to contain delayed. Excel shows rows that meet all three active filters. Each dropdown also lets you refine or change that column’s condition without removing the others.

Before filtering, check that each column has a header and that the whole dataset is included. If a new column or block of rows sits outside the detected range, it may not receive filter arrows or respond to the same filter. Converting a consistent range to an Excel table with Insert > Table can make a growing dataset easier to manage because table filters and formatting extend as table rows are added.

To remove a condition from one column, open that column’s dropdown and choose the clear-filter option shown there. To clear all applied filters while keeping the dropdowns, use Data > Clear. Choosing Data > Filter instead toggles the filter controls off or on, so it may not be the right choice when you simply want to reveal the hidden rows and continue filtering.

Use the FILTER function for a live results list

AutoFilter changes which source rows are visible. If you want matching records displayed elsewhere and updated when the source data changes, use the FILTER function. It is available in Microsoft 365 and Excel 2021 or later. The function returns a dynamic array that spills into neighboring cells, so leave enough empty space for the results.

Assume A2:C5 contains Product, Region, and Units, with the sample rows Blue mug / West / 12, Blue notebook / East / 18, Green mug / West / 7, and Navy blue pen / West / 20. To return rows where the product contains blue, the region is West, and units are at least 10, enter this formula in an empty cell:

=FILTER(A2:C5,ISNUMBER(SEARCH("blue",A2:A5))*(B2:B5="West")*(C2:C5>=10),"No matches")

The multiplication of the three TRUE/FALSE tests applies AND logic. The example returns Blue mug / West / 12 and Navy blue pen / West / 20. SEARCH finds blue anywhere in each product description and ignores case; ISNUMBER converts a successful search to TRUE. The ranges in the formula must cover corresponding rows, and the output area must be clear or Excel may report a spill problem.

For two alternative words, assume product descriptions are in A2:A100 and regions are in B2:B100. This formula returns the two columns for rows whose product contains blue or green:

=FILTER(A2:B100,(ISNUMBER(SEARCH("blue",A2:A100))+ISNUMBER(SEARCH("green",A2:A100)))>0,"No matches")

Adding the TRUE/FALSE tests and checking whether the result is greater than zero gives OR logic: either search can match. You can combine this OR test with another condition by multiplying it by a third TRUE/FALSE test. Unlike AutoFilter, the formula does not hide source rows; it creates a separate result array. In Excel for Mac and the web, FILTER is available in supported current versions, with the same basic formula behavior.

Use Advanced Filter for more criteria

Advanced Filter is useful when criteria involve more than two conditions or when you need to copy matching records to another location. On Windows, choose Data > Advanced in the Sort & Filter group. The dialog lets you filter the list in place or copy results to another area of the worksheet. The Mac ribbon may arrange the command differently; look on the Data tab for Advanced Filter.

Set up a criteria range with headings that exactly match the source column headings. Conditions written on the same criteria row are combined with AND; conditions on separate rows are alternatives, or OR. For instance, a criteria range with Product and Region headings and the entries *blue* and West on the same row asks for products containing blue in the West region. A second criteria row containing *green* under Product, with Region left blank, adds an alternative: products containing green in any region.

Advanced Filter reads the criteria range when you run it; it is not a live formula result. If you copy results to another location, select a destination outside the source list and criteria range. This approach can be easier to audit than a long formula, but the criteria layout must be maintained and the filter must be run again when you want refreshed copied results.

Choose a method and fix common problems

Choose the method based on the result you need. Use AutoFilter for quick inspection and routine filtering, Advanced Filter for more complex criteria or a copied extract, and FILTER when you want a formula-driven list that updates as source values change. A PivotTable may be a better choice when the goal is to summarize matching records by category rather than view each matching row.

  • The filter arrow is missing: Select a cell in the data and choose Data > Filter. Check for blank header cells or a range that was not included.
  • A matching row is absent: Check whether another column has an active filter. Clear filters one at a time, and look for leading or trailing spaces or inconsistent spellings.
  • A text condition behaves unexpectedly: Confirm that the column contains text rather than values stored in a different format. For literal * or ? characters, use the tilde escape where applicable.
  • The formula shows an error or incomplete output: Confirm that the formula ranges have matching dimensions and that cells in the spill area are empty. FILTER is not available in older perpetual versions such as Excel 2019.
  • You expect case-sensitive results: Standard AutoFilter text matching and SEARCH are case-insensitive. A different formula-based approach is needed for case-sensitive logic.

Also distinguish a filter from Find. Find locates matching text but does not maintain a filtered view of the dataset. Likewise, a filter condition does not permanently remove nonmatching records. If you plan to delete or overwrite data after filtering, verify which rows are visible and consider saving a separate copy first.

Frequently Asked Questions

Can I filter for two words in the same Excel cell?

Yes. Open the column dropdown, choose Text Filters and a custom condition, then set both conditions to Contains and connect them with And. For either word to qualify, connect the two Contains conditions with Or.

Can I apply multiple filters to one worksheet?

Yes. Apply a condition from each relevant column dropdown. Excel combines conditions across different columns with AND, so a displayed row must pass every active column filter.

Can Excel filter text that contains one word but not another?

Yes. In the custom text-filter dialog, set one condition to Contains and the other to Does Not Contain, then connect them with And. This keeps rows that include the required text while excluding rows containing the unwanted text.

Do Excel text filters distinguish uppercase and lowercase?

No. Standard AutoFilter text matching is case-insensitive, so Blue and blue are treated alike. Use a case-sensitive formula-based solution if capitalization is essential to the criteria.

Why does my FILTER formula return a spill error?

The result array may be blocked by existing content in the cells where Excel needs to place the results. Clear those cells, check for merged cells in the spill area, and ensure the formula is entered outside the source range when appropriate.

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.