To filter words in Excel, select a cell in your data, choose Data > Filter, open the arrow in the text column’s header, then choose Text Filters and a rule such as Contains. Enter the word or phrase and apply the filter to show only matching rows. These steps are for Excel for Microsoft 365 on Windows; you can use the same menu-based approach in Excel for Mac and Excel for the web, although the ribbon layout may look slightly different.
What You’ll Need
- A worksheet with a header row and text data arranged in columns. For example, a Product column might contain product names and a Status column might contain words such as Open or Closed.
- A clear idea of what should match: a complete cell value, text at the beginning or end of a cell, or a word or phrase anywhere in the text.
- Optional: an Excel table. It is not required, but tables make it easier to keep filters attached to the right data as you add rows.
Filtering hides rows that do not meet your rule; it does not delete them or change the cell contents. You can clear the filter later to display the full list again.
Step-by-Step
- Check the layout of your data. Put one type of information in each column, with a single header at the top of each column. Keep the records in a continuous block rather than placing blank rows or blank columns inside the list. For example, use columns named Product, Category, and Notes, with one record per row. Remove merged cells from the data area if possible, since they can make filtering awkward. If your range already has a header row, Excel can use it to label each filter arrow.
- Choose the data range or make a table. Click a cell inside the list. Excel usually detects the connected range when you turn on filtering. For a more dependable setup, select a cell in the data and choose Insert > Table, confirm that the range is correct, and make sure My table has headers is selected. Choose OK. A table adds filter arrows automatically and generally includes new rows that you add directly beneath it. If you do not want a table, leave the range as it is.
- Turn on the filter arrows. With a cell in the data selected, choose Data > Filter. In Windows desktop Excel, Ctrl+Shift+L also toggles AutoFilter on and off. On a Mac, use Data > Filter if you do not know or prefer not to use a keyboard shortcut; the ribbon arrangement can differ by version. In Excel for the web, select Data > Filter. Small dropdown arrows should appear in the header cells. If they appear on the wrong row, turn the filter off, select a cell in the proper range, and turn it on again.
- Open the arrow for the text column. Select the dropdown arrow in the header of the column you want to search, not an arrow in a neighboring number or date column. The menu normally shows a search box and a checklist of values. If the word you want is an entire cell value, you can type it in the search box, select the matching value in the checklist, and choose OK. This is a quick way to show one or several exact values that already occur in the column.
- Choose a text rule for partial matches. To find a word that may appear alongside other text, point to or select Text Filters in the menu, then choose Contains. Type the word or phrase in the Custom AutoFilter dialog and choose OK. For example, if a Comments column contains phrases such as Waiting for parts and Parts received, a Contains rule for parts shows rows containing that sequence of letters. Use Begins With when the text must start with a particular word or letters, Ends With when it must finish with them, and Does Not Contain to exclude rows that include a term. Choose Equals when the whole cell must match the entered text.
- Set two conditions when one rule is not enough. In the Custom AutoFilter dialog, you can combine two conditions. Select And when both must be true, such as text that begins with North and contains Depot. Select Or when either condition is enough, such as text that contains urgent or contains delayed. Enter a condition in each box and check the selected And or Or option before applying it. These conditions apply to the same column. To filter by text in one column and a different condition in another, apply a filter to each column; Excel combines those column filters so that a row must pass both.
- Review the visible results and clear the filter when finished. Check the filtered rows to confirm they meet your goal. A filter indicator on the header shows that a column is filtered, and the row numbers may skip hidden records. To remove a rule from one column, open that column’s arrow and choose Clear Filter From [column name], where Excel inserts the actual column name. To show all rows, use Data > Clear. This clears active filter criteria without removing the filter arrows. Choose Data > Filter again if you want to turn the arrows off entirely.
Text matching in Excel’s standard AutoFilter is generally not case-sensitive, so a search for urgent can also match Urgent. The filter works on the cell’s stored text, not on a visual impression of the worksheet. If a cell displays a shortened value because the column is narrow, widen the column or inspect the formula bar before deciding what rule to use.
Common Mistakes
- Filtering the wrong range: A blank row can cause Excel to treat data below it as a separate block. Turn off the filter, remove unintended gaps, select a cell in the complete list, and apply the filter again. Check that every relevant column has an arrow.
- Using the checklist when you need a partial match: The checklist searches the distinct values in the column. To find text inside longer entries, use Text Filters > Contains instead of selecting only a complete value from the checklist.
- Expecting a whole word when the rule finds a character sequence: Contains checks for the entered text anywhere in the cell. Searching for art can match entries such as cart. Search for a longer, distinctive phrase when you need fewer accidental matches.
- Overlooking another active filter: A filter in a second column can continue hiding rows even after you clear the first column’s rule. Use Data > Clear to remove all current criteria, then apply the filters you actually need.
- Assuming hidden rows were removed: Filtering hides nonmatching rows; it does not delete them. Avoid editing or exporting a filtered list without checking which rows are visible and what your next action will include.
Tips
- Use a specific phrase rather than a very short fragment. Searching for ship in a Notes column may also find shipment or friendship, while a phrase such as delayed shipment is more selective.
- For a list of known values, the dropdown checklist is often fastest. Use its search box to narrow a long checklist, select the values to keep, and apply. For partial text, choose a Text Filters rule instead.
- Use a table for frequently updated lists. When you add records within or directly below a table, its formatting and filter controls are easier to maintain than a manually selected range.
- Before sharing a filtered workbook, state that a filter is active or clear it if recipients should see every record. The filter arrow and skipped row numbers can help you notice that the view is restricted.
- If you need to keep the matching records separately, filter first, then copy the visible results carefully. Excel’s normal copy behavior with filtered ranges generally copies visible cells, but verify the pasted rows before relying on the copy. Do not mistake a filtered view for a permanent separate list.
Troubleshooting
- No filter arrows appear: Click inside the data and choose Data > Filter. Confirm that the worksheet is not protected in a way that prevents filtering and that you have selected a normal range or table. If necessary, select the complete list, including its header row, and apply Filter again.
- The filter arrow or header is on the wrong row: Excel may have identified the wrong range or interpreted a title row as the header. Turn filtering off, remove any extra title rows from the range if appropriate, select the actual header and records, and reapply Data > Filter. Keep one header row only.
- The expected word is missing from the results: Check for spelling differences, extra spaces, punctuation, or a different phrase in the cell. Try a shorter distinctive fragment with Contains. Also clear other column filters, since an active condition elsewhere may be excluding the row. If the displayed text comes from a formula, inspect the resulting value in the cell rather than the formula itself.
- Too many rows match: Replace a broad Contains search with a longer phrase, Equals, Begins With, or Ends With, depending on the intended rule. Review whether your chosen text is only part of other words. Standard AutoFilter text matching is not case-sensitive, so changing capitalization alone will not separate matches.
- New records do not appear: Check that the new rows are inside the filtered range. If you used a normal range, turn off the filter and apply it again with the expanded data selected. If you used a table, add the rows within or directly below the table and confirm that the table expanded.
- The filter seems stuck: Open the relevant column menu and clear its filter, or choose Data > Clear to remove all active criteria. If you cannot see the command, check that you are in the worksheet with the filtered list and that the workbook is not restricting changes.
Frequently Asked Questions
Can I filter a word that appears anywhere in a cell?
Yes. Open the arrow for the text column, choose Text Filters > Contains, enter the word or phrase, and apply the rule. This finds the text inside longer cell entries as well as entries that contain only that text.
How do I filter for cells that do not contain a word?
Open the column’s dropdown, choose Text Filters > Does Not Contain, enter the word, and choose OK. If rows are still missing, clear filters in other columns because those rules also affect which rows remain visible.
Can I filter for more than one word?
Yes. In Custom AutoFilter, set two conditions and choose And if both terms must match, or Or if either term may match. For exact, separate cell values, you can also select multiple items in the dropdown checklist.
Does Excel distinguish uppercase and lowercase?
Standard AutoFilter text rules are generally case-insensitive. A Contains rule for blue can match Blue or BLUE. If you need case-sensitive matching, the ordinary Text Filters menu is not designed to provide that distinction.
Can I filter words in Excel for Mac or the web?
Yes. Use the Data tab’s Filter command, open the text column’s dropdown, and choose a text-filter rule. Menu placement and appearance can vary slightly, but the basic filtering process is similar to Excel for Windows.
Does filtering delete the rows that do not match?
No. It only hides them from the current view. Clear the filter to show the complete dataset again. If you intend to delete records, first confirm that you have selected the correct rows and understand that deletion is a separate action.
Related
- Export an Excel Table as an Image: Step-by-Step
- How to Export an Excel Tab to Its Own Workbook
- Export an Excel Table as a Picture: Step-by-Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMPRODUCT in Excel: Examples, Criteria, and TipsA simple example of SUMPRODUCT in Excel is =SUMPRODUCT(B2:B5,C2:C5): if B2:B5 contains quantities and C2:C5 contains unit prices,…
- NPV Formula Examples in Excel: Cash Flows ExplainedIn Excel, calculate net present value with =NPV(rate, future_cash_flows)+initial_cash_flow: enter the discount rate and future cash flows in…
- Excel Formula to Fill Blank Cells in a List AboveTo fill blank cells with the value from the row above in Excel for Microsoft 365, select the…
- XLOOKUP Match Mode Examples: Exact and ApproximateXLOOKUP’s match_mode argument controls what counts as a match: use 0 for an exact match, -1 for an…
- Combine HLOOKUP and VLOOKUP in ExcelYes. You can nest VLOOKUP inside HLOOKUP so VLOOKUP finds the row number and HLOOKUP finds the column…
- Excel: Sum VLOOKUP Values Across Multiple Matching RowsTo total every row that matches a lookup value, use SUMIF, not a regular VLOOKUP: with the matching…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.