Excel’s standard Text Filters menu lets you combine only two conditions in its Custom Filter dialog. To filter for more than two text criteria, use Data > Advanced with one criterion per row, or use a formula; if you only need to select several exact values, you can also tick them in the filter’s checkbox list.
Why the Custom Filter Has a Two-Condition Limit
In Excel for Microsoft 365 on Windows, apply a normal column filter from Data > Filter, open the column’s dropdown, and choose Text Filters > Custom Filter. The dialog provides two comparison lines, joined by either And or Or. That is convenient for a pair of rules, such as text that begins with “North” or text that begins with “South,” but there is no third line for another condition.
The limit applies to that particular dialog, not to Excel’s ability to filter a longer list of criteria. The checkbox list in the dropdown is a different feature: it can select multiple distinct values, such as several department names. It does not add more “contains” rules to the Custom Filter dialog. For more than two text conditions, choose a method based on whether you need a reusable filter, a formula-generated result, or a quick selection of exact values.
Method 1: Use Advanced Filter for More Than Two Rules
Advanced Filter is a practical choice when the conditions should be easy to inspect in worksheet cells, or when you want to filter a list in place or copy matching rows elsewhere. Set up a small criteria range with a header that exactly matches the header in your data. Put each alternative condition on a separate row beneath that header. Separate rows mean OR: a record is included when it satisfies any one of those criteria.
For example, suppose your table is in A1:C100 and the column to search is headed City in B1. Place City in E1, then enter *north* in E2, *south* in E3, and *west* in E4. These wildcard criteria find cells containing those text fragments. The rows matching North, South, or West are included; a value such as “Northwest” also matches because it contains “north” and “west.”
- Keep the source list’s headers in its first row and make sure there are no completely blank rows inside the list.
- Build the criteria range outside the list, using the exact source column heading above the criteria.
- Select a cell in the source list and choose Data > Advanced.
- In the Advanced Filter dialog, confirm the List range and select the full criteria range, including its header.
- Choose Filter the list, in-place, or Copy to another location, then select OK.
Advanced Filter is available in the desktop Excel app; Excel for the web does not provide the same Advanced Filter dialog. In Excel for Mac, look for Advanced on the Data tab. Ribbon layout can vary by release, but the criteria-range logic is the same.
How Advanced Filter Combines AND and OR
Rows in an Advanced Filter criteria range have an important meaning. Criteria on the same row are combined with AND; separate rows are combined with OR. This lets you describe more than one kind of rule without adding a new dialog condition for each one.
For example, suppose you want cities containing “north” only when the Status column is “Open,” or cities containing “south” only when Status is “Pending.” Put City and Status as adjacent headers in the criteria range. On the first criteria row, enter *north* under City and Open under Status. On the next row, enter *south* under City and Pending under Status. The result is (north AND Open) OR (south AND Pending). If you instead put north and south on one row under the same City heading, you would be asking a single cell to meet both conditions, which is usually not the intended result.
Use the same matching style consistently. A wildcard at both ends, such as *west*, means “contains west”; west* means “begins with west”; and *west means “ends with west.” The asterisk represents any number of characters. A question mark represents one character, and a tilde can be used to treat a wildcard character literally. Check the results for near matches, especially when short search terms could occur inside unrelated words.
Method 2: Return Matching Rows with FILTER
If you want a separate, automatically updating list of matching records, FILTER can test several text terms at once. FILTER is available in Microsoft 365 and Excel 2021 or later. The example below checks the Description column for any of three words and returns the corresponding full rows. SEARCH is case-insensitive, so “north” also matches “North.”
With sample records in A2:C7, descriptions in B2:B7, and the complete records in A2:C7, enter this formula in an empty cell outside the source data:
=FILTER(A2:C7,(ISNUMBER(SEARCH("north",B2:B7))+ISNUMBER(SEARCH("south",B2:B7))+ISNUMBER(SEARCH("west",B2:B7)))>0,"No matches")
Suppose the descriptions are North depot, South branch, West route, Central office, Northwest hub, and East branch. The formula returns four records: North depot, South branch, West route, and Northwest hub. Northwest hub appears only once even though it matches two terms. The plus signs combine the three TRUE/FALSE tests, and a total greater than zero means at least one search term matched. If none match, the formula displays “No matches.”
Because FILTER spills its result into neighboring cells, keep the output area clear. If Excel shows #SPILL!, remove any blocking values or merged cells in the spill range. To change the rules later, edit the quoted search terms. This formula is useful for a live report, but it does not turn the source table’s dropdown into a filter with additional conditions; it creates a formula result separate from the source.
Method 3: Add a Helper Column and Filter TRUE
A helper column is a straightforward alternative when you want to keep using the regular column dropdown or when your Excel version does not have FILTER. Add a heading such as Match? beside the source data, enter a test on the first data row, and fill it down. Then filter the helper column to show TRUE. The formula below assumes descriptions are in B2 and checks for the same three terms used in the previous example:
=OR(ISNUMBER(SEARCH("north",B2)),ISNUMBER(SEARCH("south",B2)),ISNUMBER(SEARCH("west",B2)))
For a row containing “Northwest hub,” the result is TRUE; for “Central office,” it is FALSE. Copy the formula down for every record, then open the helper column’s filter dropdown and select TRUE. This works with older desktop Excel versions that support these long-established functions. If the list is an Excel Table, a formula entered in the helper column usually fills down automatically for its data rows, and new table rows can inherit the calculated column.
The helper column makes the logic visible and editable, but it does add a column to the data. For many search terms, putting the terms in cells and building a formula around that list can be easier to maintain than editing a long formula. In Microsoft 365, a dynamic-array approach can also be built around a criteria list, but it is worth testing carefully for blank criteria: searching for an empty string can match every text cell.
When the Checkbox List Is Enough
If your goal is to show several exact categories rather than find text fragments, the standard filter dropdown may already do what you need. Open the column dropdown and select the checkboxes for the values to keep. For example, selecting Sales, Support, and Operations shows rows whose Department value is one of those three names. You can search within the dropdown’s value list to locate an item, then check additional values.
This is different from asking for values that contain any of several words. Selecting the exact checkbox “North depot” will not automatically select “North warehouse” just because both contain “North.” Use Advanced Filter or a formula for partial-text rules. Also note that the checkbox list is based on values present in the filtered column; it is not a place to type an unlimited set of independent text comparisons.
Choose the Method That Fits the Job
- Use Advanced Filter for a worksheet-based set of criteria, multiple AND/OR combinations, or copying qualifying records to another location.
- Use FILTER for a separate results area that recalculates when source data changes, provided you have Microsoft 365 or Excel 2021 or later.
- Use a helper column when a TRUE/FALSE test is easy to understand and you want to filter the original list with a normal dropdown.
- Use the checkbox list when the criteria are simply several exact values already present in the column.
For a one-time task, Advanced Filter can be quicker than writing a formula. For a report that others will refresh, a criteria range or helper column can make the rules easier to review than hard-coded terms hidden in a formula. Consider who will maintain the workbook and whether the results should replace the visible source view or appear as a separate output.
Common Problems and Checks
If Advanced Filter returns no rows, first check that the criteria header matches the source heading exactly; extra spaces or a slightly different spelling can prevent Excel from recognizing the intended field. Confirm that the criteria range includes its header and all criteria rows. Also verify that each OR alternative occupies its own row and that the source list has a single header row without blank lines interrupting the records.
If the results seem too broad, inspect wildcard placement. *art* matches “cart,” “artist,” and “partial,” not just the standalone word “art.” Excel’s SEARCH function likewise finds a term within a longer word. If you need whole-word matches, simple contains searches are not sufficient on their own; a more specific formula or cleaned, consistently structured source data may be necessary. SEARCH ignores letter case, while FIND is case-sensitive, so use the appropriate function when capitalization matters.
For formula results, check for misspelled terms, leading or trailing spaces in the source, and blocked spill cells. If the helper-column formula shows FALSE unexpectedly, confirm that it references the text cell on the same row and that the formula was filled through the entire list. Sorting or adding records can also expose gaps if the helper formula was not copied down; an Excel Table helps reduce that maintenance risk.
Frequently Asked Questions
Can Excel’s Custom Filter handle three conditions?
No. The Text Filters > Custom Filter dialog provides two condition lines. Use Advanced Filter, a formula, or a helper column for a third condition or more.
How do I filter for several words in one column?
For “contains any of these words,” put wildcard criteria such as *north* and *south* on separate Advanced Filter criteria rows, or use a formula that tests each term. Separate criteria rows mean OR.
Can I select more than two text values in a filter dropdown?
Yes. You can tick several distinct values in the dropdown’s checkbox list. That selects exact listed values; it does not create several partial-text conditions.
Does Advanced Filter change or delete the source data?
Filtering in place hides rows that do not match; it does not delete them. Choosing Copy to another location creates a separate filtered output and leaves the original list in place.
Are text searches case-sensitive?
Excel’s standard text filters and SEARCH-based examples here are not case-sensitive. Use FIND in a formula when case-sensitive matching is required, and account for that change in the formula’s logic.
Related
- Excel Shortcut for Selecting a Column in One Step
- Excel Shortcut for Filtering a Table: Keys and Tips
- Excel Shortcut for Inserting a Row on Windows and Mac
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Exact Match: XLOOKUP and MATCHWith the value to find in E2, lookup values in A2:A6, and results in B2:B6, use =XLOOKUP(E2,A2:A6,B2:B6,"Not found",0)…
- Copy and Transpose in Excel: Steps, Formulas, and TipsTo copy and transpose data in Excel for Microsoft 365 on Windows, select and copy the cells, choose…
- HLOOKUP Examples for Practice: Formulas and Fixes in ExcelFor an exact-match HLOOKUP practice example, put product codes in A1:D1, prices in A2:D2, and stock counts in…
- Excel Timesheet Formula With Breaks and Overnight ShiftsWith a shift start in B2, shift end in C2, break start in D2, and break end in…
- Sort Excel Spreadsheet by Date: A Clear GuideTo sort an Excel spreadsheet by date, click a date cell and choose Data > Sort Oldest to…
- Excel Days Until Formula: Calculate a CountdownTo calculate calendar days until a date in Excel, put the target date in A2 and enter =A2-TODAY()…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.