To apply a custom text filter in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data > Filter, open the column’s filter arrow, and choose Text Filters. Pick a condition such as Contains or Begins With; to combine two conditions, choose Custom Filter and set the criteria to match either both (AND) or either (OR).
A custom text filter narrows a list to rows whose values in a chosen column match text conditions. It hides rows that do not match; it does not delete or change the underlying data. This is useful for finding items such as customer names, product descriptions, locations, or status labels without rearranging the source list.
What Excel’s text filter does
Excel’s ordinary AutoFilter provides a checklist of the distinct values in a column. Selecting or clearing items in that checklist is handy when you know the exact labels to include. Text Filters are for rules instead: you can ask Excel to show values that contain a word, start with certain letters, or meet another text condition. The results update when you apply the filter, and a filter icon on the column indicates that the list is filtered.
Text filtering works on the values in cells, not their fill color, font, or other formatting. It also applies to the selected column’s contents rather than searching every column in the workbook. If you need to search several columns, apply a separate filter to each relevant column or use a formula-based method described below.
Apply a basic custom text filter
- Make sure each column has a header and that the data is arranged in a continuous block, with no completely blank rows splitting the list.
- Click inside the data. Select Data > Filter. Excel adds a drop-down arrow to each header. If your data is formatted as an Excel table, filter arrows are usually already present.
- Open the arrow on the column you want to filter. Choose Text Filters, then select a condition such as Contains, Does Not Contain, Begins With, Ends With, Equals, or Does Not Equal.
- Enter the text to match in the dialog box and confirm. Excel hides rows that fail the condition.
For example, if a Product column contains “Northwind mug,” “Mug gift set,” and “Desk lamp,” a Contains filter for mug returns the first two rows. A Begins With filter for North returns “Northwind mug,” but not “Mug gift set.” The match is generally not case-sensitive, so searching for north also finds “Northwind.”
Combine two text conditions with AND or OR
Choose Text Filters > Custom Filter to open the custom criteria dialog. It provides two condition rows and options for AND or OR. AND means a cell must satisfy both conditions; OR means it may satisfy either. The logic applies to the text in that one column, not to separate columns.
Suppose a list’s Region column includes “North,” “Northwest,” “South,” and “Southwest.” To show entries containing “North” or “South,” set the first condition to Contains “North,” select OR, and set the second to Contains “South.” Both groups of locations qualify. If you instead set AND, a value would need to contain both strings, so those four example values would not qualify.
AND is useful when you want to narrow a search. For instance, in a Comments column, Contains “delivery” AND Does Not Contain “delayed” can keep rows mentioning delivery while excluding comments that also mention delays. Check the actual wording in your data: a row containing “delivery was not delayed” would still be excluded because it contains both words, even though the sentence is positive. Text filters match characters, not meaning or context.
Text conditions, wildcards, and exact matches
Choose the condition that describes what you mean. Equals is for a matching text value; Contains looks for the entered characters anywhere in the value; Begins With and Ends With check the corresponding edge; Does Not Contain excludes values with the specified text. When you need two rules, use the custom dialog rather than trying to encode several ideas in one search term.
Excel also supports wildcard characters in filter criteria. An asterisk (*) represents any number of characters, and a question mark (?) represents one character. For example, a criterion like pro* can match text starting with “pro,” while te?t can match “test” or “text.” A tilde (~) escapes a wildcard when you want to search for it literally; for example, ~* represents an asterisk character. Wildcard matching is useful for consistent codes, but test the results if the column contains punctuation or inconsistent spelling.
Do not confuse the filter’s search box with a custom condition. The search box in the filter menu helps locate values in the checklist; selecting the results filters to those listed values. Text Filters instead applies a rule such as Contains or Begins With. Either approach can be appropriate, depending on whether you want to select known values or define a pattern.
Prepare data so the filter behaves predictably
Filters work best when the range has one header row and one record per row. Avoid merged cells in the data area, blank header names, and blank rows or columns that make the data appear to be separate blocks. If the list will grow over time, convert it to a table with Insert > Table, confirm that the range is correct, and make sure My table has headers is selected. Table filters expand more naturally as rows are added.
- Use consistent labels. “In progress,” “In Progress,” and “In-progress” may look similar to a person but are distinct text values in the checklist.
- Remove accidental leading or trailing spaces if values that appear identical filter differently. The TRIM function can help clean imported text, but apply it in a helper column or to a copy if you need to preserve the original.
- Check whether a cell contains a formula. A filter evaluates the displayed result of a formula, not the formula’s source text.
- Keep a note of active filters when sharing a workbook. A colleague may otherwise mistake the visible subset for the complete dataset.
Why a custom text filter may return unexpected results
First, check that the filter is on the intended column and that the selected condition matches the goal. Contains “East,” for example, can include “Northeast” as well as “East.” Use Equals if you need only the exact label, or Begins With if position matters. Also check whether a second filter is active on another column; Excel combines filters across columns, so a row must pass every active column filter to remain visible.
Another common issue is inconsistent source text. Extra spaces, line breaks from copied data, nonprinting characters, and spelling variations can make two values that look alike behave differently. A practical way to investigate is to clear the filters, inspect a few affected cells in the formula bar, and compare their contents. If the data came from another system, clean it in a helper column or during import rather than repeatedly trying broader criteria.
If no rows appear, verify the AND/OR setting, clear other column filters, and try a simpler condition. If too many rows appear, replace Contains with Equals or a more specific condition. To remove a filter from one column, open its arrow and choose Clear Filter From [column name]. To remove all filters while keeping the arrows, use Data > Clear; to toggle the arrows themselves, use Data > Filter.
Custom filters compared with alternatives
The filter checklist is best when you know which existing values to select. Text Filters are better for a repeatable rule such as “contains west.” Advanced Filter is a separate Excel feature for more elaborate criteria ranges and copying results elsewhere; it is useful in structured workflows but requires setting up a criteria range. Conditional formatting can highlight matching cells, but it does not hide nonmatching rows. These tools solve different problems, so highlighting is not a substitute for filtering when you need a smaller visible list.
For a live results range that updates without hiding source rows, Excel 2021 and Microsoft 365 include the FILTER function. With names in A2:A20, regions in B2:B20, and other fields in C2:C20, this formula returns rows where the region contains “north,” without regard to capitalization:
=FILTER(A2:C20,ISNUMBER(SEARCH("north",B2:B20)),"No matches")
The assumed layout is records in A2:C20 and the text to inspect in B2:B20. SEARCH returns a position when it finds “north”; ISNUMBER turns found positions into TRUE values for FILTER. If two records match, the formula spills those two complete rows into the cells below and beside it; if none match, it returns “No matches.” Keep the spill area empty. This formula creates a separate output, unlike AutoFilter, which hides rows in place.
Excel for Mac and Excel for the web
The general process is similar in current Excel for Mac: select the data, use the Data tab’s Filter command, then open the column menu and select a text-filter condition. Ribbon layout and dialog appearance can vary by release, so use the visible Data and filter menus if the Windows labels do not match exactly. On Excel for the web, filter arrows and text-filter options are also available, although some advanced workbook features and dialogs can differ from the desktop application.
On Windows, Ctrl+Shift+L toggles filter arrows for a selected range. Mac keyboard shortcuts can differ by keyboard and Excel release, so Data > Filter is the dependable route on Mac. The shortcut only toggles the filter controls; it does not itself choose a text condition.
Frequently Asked Questions
Is a custom text filter case-sensitive?
Excel’s standard text filters are generally case-insensitive. A Contains condition for “north” can match “North.” If capitalization must be treated as meaningful, use a formula-based approach designed for case-sensitive comparison rather than relying on AutoFilter.
Can I filter for text in more than one column?
Yes. Apply a condition to each column. Excel combines active column filters, showing only rows that satisfy all of them. For example, filter Department to “Sales” and Status to “Open” to show open sales records.
Does filtering delete rows?
No. AutoFilter hides rows that do not meet the criteria. Clear the filter to show them again. This is different from deleting rows, though copying only visible rows elsewhere can create a separate subset.
How do I find cells that are blank?
Open the column’s filter menu and select (Blanks), when that option is available in the value checklist. Blank-looking cells may actually contain spaces or formulas returning an empty string, so they may not behave like genuinely empty cells.
Can I save a custom filter for later?
The filter state is generally saved with the workbook, so it can reopen showing the same filtered view. If you need multiple named views for different audiences, consider Excel’s Sheet View feature where available, or document the criteria so they can be reapplied.
Related
- Copy Paste in Excel: Keep Column Widths When Pasting
- Copy an Entire Excel Spreadsheet to a New File
- Copy and Paste X Times in Excel: Repeat Cells or Rows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIFS Between Dates: Formula and ExamplesTo count dates between two boundaries in Excel, use COUNTIFS with the same date range twice: once for…
- How to Show Formulas in Excel for MacTo show formulas in Excel for Mac, open the worksheet and choose Formulas > Show Formulas. You can…
- Excel Formula for a Future Date: Add Days or MonthsIf the starting date is in A2 and the number of days to add is in B2, enter…
- Percentage Calculator in Excel: Formulas and ExamplesTo calculate a percentage in Excel, divide the part by the total, then format the result as a…
- Excel Rule for Dates Before Today: Formula and SetupTo highlight dates before today in Excel for Microsoft 365 on Windows, select the date cells and create…
- Excel Query Function: What to Use InsteadExcel does not have a worksheet function named QUERY like Google Sheets does. In Microsoft 365 for Windows,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.