To filter for multiple text values in Excel, open the column’s filter menu and select each value you want to keep; Excel shows rows matching any selected value. For a longer list or repeatable criteria, use Advanced Filter or, in Microsoft 365, a FILTER formula.
How Excel filters multiple text values
An Excel filter hides rows that do not meet your conditions; it does not delete them or change the underlying data. When you select several entries in a column’s checkbox list, Excel treats them as alternatives: a row is shown if its cell matches one selected entry. In logical terms, that is an OR condition.
For example, suppose a table has a City column containing Seattle, Portland, and Boise. Selecting Seattle and Boise keeps rows for either city and hides the Portland rows. You can select many distinct values this way, not just two. The selection list is most convenient when the values already occur in the column.
Excel also offers text conditions such as Contains, Begins With, and Equals. Those are useful when you need to match a pattern rather than choose existing entries. The custom text filter dialog supports two conditions at once, joined by And or Or. That limit applies to the custom-condition dialog, not to selecting multiple entries in the checkbox list.
Filter a column by selecting several values
- Click a cell inside the data range. If the range is not already an Excel table, choose Data > Filter on the ribbon. Excel adds a dropdown arrow to each header.
- Open the dropdown for the text column you want to filter.
- Clear Select All, then check each value to include. Use the search box in the dropdown to find an entry in a long list; check the result before applying the filter.
- Choose OK. Excel displays rows matching at least one selected value.
For example, to show orders whose Status is either Open or Pending, open the Status dropdown, clear Select All, and check Open and Pending. You do not need to create a separate filter for each value. To restore the full list, reopen that dropdown and choose Clear Filter From followed by the column name, or use Data > Clear to clear filters across the range.
Keep a header row and avoid completely blank rows or columns inside the dataset. Excel uses the surrounding range to determine what the filter applies to, so a break in the data can cause some rows to be left out. Converting the range to a table with Insert > Table can make the data range easier to manage as it grows.
Use Text Filters for conditions and partial matches
When the values are not easy to select from a list, open the column dropdown and choose Text Filters. Options include Equals, Does Not Equal, Begins With, Ends With, Contains, and Custom Filter. These choices work on the text in the column, rather than on the value checklist alone.
Choose Text Filters > Custom Filter to build two conditions. The dialog has an And option, which requires both conditions to be true, and an Or option, which accepts either one. For instance, if a Product column contains descriptive names, a Contains condition for “desk” OR a Contains condition for “chair” can show names containing either word. Choosing And instead would show only cells satisfying both conditions, such as a description containing both words.
Text comparisons in Excel’s standard filters are generally not case-sensitive, so “north” and “North” are treated alike for these purposes. If case is important, a normal text filter may not be sufficient; use a formula designed for case-sensitive comparison or prepare a helper column. Also distinguish filtering a cell that equals a word from filtering a cell that contains that word as part of a longer description. Use Equals for the former and Contains for the latter.
Use wildcards to match text patterns
Wildcards can help when you know a part of a value but not the full text. In filter criteria, an asterisk (*) represents any number of characters, while a question mark (?) represents one character. A tilde (~) can be used before a wildcard character when you want to search for the literal asterisk or question mark rather than use it as a pattern.
- North* matches text beginning with North, such as Northside or Northwest.
- *road matches text ending in road, such as Oak Road.
- Jo?n can match John or Joan, because the question mark stands for one character.
Wildcards are useful for variations in a naming convention, but they can also match more than intended. For instance, a pattern beginning with “North” can include every longer value with that beginning, not only a value named exactly North. If you need to include several unrelated patterns, the two-condition custom filter may not cover your full list. Use the checkbox list, Advanced Filter, or a formula instead.
Filter for a longer list with Advanced Filter
Advanced Filter is useful when you have a separate list of exact values to include, especially when that list is too long to select conveniently in a dropdown. In desktop Excel for Microsoft 365 on Windows, place the criteria in a small range with a header that exactly matches the source column header. Put each alternative value on its own row beneath that header. Separate rows in an Advanced Filter criteria range represent OR alternatives.
For example, if the source header is City, make a criteria range with City at the top and Seattle, Portland, and Boise in separate cells below it. Click inside the source data, then choose Data > Advanced in the Sort & Filter group. In the dialog, confirm the List range, select the criteria range, and choose whether to filter the list in place or copy results to another location. If you choose to copy results, specify the destination. Advanced Filter works with a criteria range; it is not the same as checking multiple entries in the ordinary dropdown.
Header spelling matters: the criteria header must correspond to the source field, and the criteria values must be arranged correctly. Criteria on the same row across different columns are generally combined as AND conditions; alternatives on separate rows are OR conditions. Advanced Filter is a desktop feature, so if you are working in Excel for the web and do not see the command, use the standard filter dropdown or a formula, or open the workbook in the desktop app.
Use a formula for a reusable criteria list
In Microsoft 365, FILTER can return matching rows to a separate area of the worksheet. This is helpful when you want a visible results list that updates as the source or criteria change. Unlike a regular filter, the formula spills a separate result and leaves the original rows in place. FILTER is available in Microsoft 365 and Excel 2021 or later.
Assume the source records are in A2:C100, the text to check is in B2:B100, and the exact values to include are listed in E2:E4. Enter this formula in an empty cell outside the source data:
=FILTER(A2:C100,ISNUMBER(MATCH(B2:B100,E2:E4,0)),"No matches")
MATCH looks for each value in B2:B100 in the criteria list E2:E4, with 0 requesting exact matching. ISNUMBER turns successful matches into TRUE values for FILTER. If the criteria cells contain Seattle, Portland, and Boise, the formula returns every source row whose column B value is one of those cities; if none match, it displays No matches. Keep the output area clear because the results spill into neighboring cells, and do not place the formula inside the source range it is filtering.
This example checks complete cell values, not whether a cell contains one of the words inside a longer description. If you need partial-text matching across many terms, a helper column or a carefully built formula is often easier to inspect and maintain. Formula results are also not the same as hiding rows: other rows remain visible in the original data unless you apply a separate filter.
Common problems and how to fix them
- A value is missing from the dropdown. Check whether the filter covers the complete data range, whether the entry has leading or trailing spaces, and whether a different filter is already active. Clear existing filters and inspect the source cells.
- The filter returns too many rows. You may have used Contains or a wildcard where an exact match was needed. Choose Equals or select the exact value from the checkbox list.
- Only two phrases can be entered. That is the limit of the Custom AutoFilter dialog’s two-condition setup. Select multiple checkbox values, use a criteria range with Advanced Filter, or use a formula for a longer criteria list.
- The formula reports no matches. Confirm that the lookup values are actually in the checked column and that the criteria match the cell contents. Extra spaces, spelling differences, and selecting the wrong range are common causes. MATCH with 0 requires an exact value.
- Some records do not filter with the rest. Check for blank rows or columns dividing the data and make sure all records are included in the filter range. A formatted Excel table can help keep later additions in scope.
Filters can also interact across columns. If you select multiple values in City and then apply a Status filter, Excel shows records that satisfy the City selection and the Status filter. Within a single checkbox list the selected values work as OR alternatives, but filters applied to different columns narrow the results together.
Which method should you choose?
For a one-time choice among values already present in a column, the dropdown checklist is usually fastest. Use Text Filters when you need a condition such as “contains this phrase,” or when you need two conditions combined with And or Or. Choose Advanced Filter when you have a separate list of exact criteria and want to filter in place or copy matching records. Use FILTER when a dynamic, formula-driven copy of matching rows is useful and your Excel version supports it.
Consider how the workbook will be used, too. A regular filter is easy for another person to adjust, but it hides rows in the original list. Advanced Filter is handy for a one-off extraction. A formula can update automatically when inputs change, but it depends on a compatible Excel version and an unblocked spill area. If you are sharing a workbook with users on older Excel releases, prefer the built-in dropdown or Advanced Filter over a formula that may not be supported.
Frequently Asked Questions
Can I filter for more than two text values in Excel?
Yes. In the column dropdown, clear Select All and check as many values as needed. The two-condition limit applies to the Custom AutoFilter dialog, not to the checkbox list. For a long external list of criteria, use Advanced Filter or a suitable formula.
How do I filter cells that contain either of two words?
Choose Text Filters > Custom Filter, set the first condition to Contains, and choose Or before setting the second Contains condition. The dialog supports two conditions. For more than two words, use a helper column, Advanced Filter where appropriate, or a formula-based approach.
Does Excel’s filter search match part of a value?
The dropdown search helps locate entries in the list, while Text Filters includes a specific Contains condition for matching text within a cell. If you need an exact whole-cell match, choose Equals or select the exact listed value instead.
Can I use a list of values stored in cells as filter criteria?
Yes. Advanced Filter can use a criteria range with a matching header and alternative values on separate rows. In Microsoft 365 or Excel 2021 and later, FILTER with MATCH can also return rows whose values appear in a criteria range.
Why does Excel show rows that do not match my selected values?
Check whether filters on other columns are also active, whether the selected condition is Contains rather than Equals, and whether the data range includes the intended rows. Clear the filters and apply them again after confirming the column and criteria.
Related
- Excel Ribbon Keyboard Shortcuts: A Practical Guide
- Excel SUM with Multiple Criteria: How to Do It
- Excel: Subtract the Same Value From Multiple Cells
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF Positive Numbers in Excel: Formula and ExamplesTo sum positive numbers in Excel, use SUMIF with the criterion ">0". If the numbers are in B2:B10,…
- Excel Filter Shortcut Key: Turn Filtering On or OffTo apply a filter in Excel for Microsoft 365 on Windows, click a cell in your data range…
- Excel MIN Ignore Errors: Formulas and Common FixesTo find the smallest number in a range while ignoring error cells, use =AGGREGATE(5,6,A2:A10). The 5 selects MIN,…
- Excel Spreadsheet Substitute: Find the Right ToolIf you need a substitute for an Excel spreadsheet, Google Sheets is the easiest choice for browser-based collaboration,…
- Sort Excel Column Without a Header Safely in ExcelTo sort an Excel column that has no header, select the data cells, then choose Data > Sort…
- COUNTIF Excel Criteria Examples and How to Use ThemExcel’s COUNTIF function counts cells that meet one condition. With A2:A9 containing Paid, Pending, Paid, Overdue, paid, Pending,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.