To filter an Excel table for several values, open a column’s filter arrow, clear Select All, and check each value you want to keep; Excel shows rows matching any checked value. For more flexible conditions, use the column’s Text Filters or Number Filters, a slicer, or an Advanced Filter criteria range.
Quick steps for selecting several values
In Excel for Microsoft 365 on Windows, click anywhere in the table and use the filter arrow in the relevant header. Tables normally show filter arrows automatically. If yours does not, select a cell in the table and choose Data > Filter; you can also use Home > Sort & Filter > Filter.
- Open the filter arrow for the column you want to filter.
- Clear Select All to uncheck the listed values.
- Check each value to include. You can select several nonadjacent items.
- Choose OK to apply the filter.
For example, if a Region column contains East, West, North, and South, checking East and North displays rows from either region. It does not remove the other rows; it temporarily hides rows that do not match. To restore them, open that column’s dropdown and choose Clear Filter From the column name, or use Data > Clear to clear filters across the range.
How Excel combines filter choices
It helps to distinguish a list of selected values from a set of conditions. When you check several items in one dropdown, Excel treats those choices as alternatives: a row can match one value or another. Selecting East and North therefore means East OR North for that column.
Filters on different columns are combined differently. If you filter Region to East or North and also filter Status to Open, a row must meet both column filters: it must be in East or North and have an Open status. Excel hides rows that fail any active filter. This is useful for narrowing a large table in stages, but it can also explain why a chosen value seems to be missing: a filter on another column may already be excluding its row.
Filtering only changes what is visible. It does not alter the underlying cell values, delete records, or change the table’s sort order. Copying a filtered range can have visibility-related details, so check the selected cells before copying or editing a subset of records.
Method 1: Check multiple exact values
The checkbox list is the simplest option when you know the exact entries to include. It works for text, dates, and numbers. In a date column, Excel may group dates into years, months, or days; expand the relevant group to choose individual dates, or use Date Filters when you need a range such as dates after a particular day.
Use this method for a short, explicit set such as particular departments, product names, or order IDs. It is also a good way to include a few specific numbers without building a condition. Keep in mind that Excel matches the values represented in the cells, not merely how they appear after formatting. Two numbers displayed with different decimal formats may still be the same underlying number.
Method 2: Find choices with the dropdown search box
When the column has many distinct entries, use the search box at the top of its filter menu instead of scrolling. Enter part or all of a value to narrow the checklist, then check the result you want. To include several distant values, search for one, select it, change the search, and select another before applying the filter. Review the checked items before choosing OK, because searching narrows the visible checklist rather than changing the table itself.
Search is especially handy for long product or customer lists. It is not the same as a contains condition on the table: it helps locate entries in the dropdown’s value list. For example, searching the list for “North” helps you find a value such as North District; it does not automatically define a rule that includes every cell containing the word North. For that broader rule, use Text Filters > Contains.
Method 3: Set text or number conditions
Use condition-based filters when you want to describe a rule rather than pick individual entries. Open the column dropdown, point to Text Filters or Number Filters, and choose a condition such as Equals, Contains, Greater Than, or Between. The available menu depends on the kind of data in the column.
The Custom AutoFilter dialog can hold two conditions for one column and lets you choose And or Or between them. For example, a text filter can show values that begin with “A” OR begin with “B”; a number filter can show values greater than 100 AND less than 500. This is more concise than checking many items when the values follow a pattern. It is not a substitute for an unlimited list of alternatives: for a large set of unrelated exact values, the checkbox list or another method is usually easier.
Text conditions are useful for partial matches, but be precise about the operator. Contains matches the specified text anywhere in a cell; Begins With only matches at the start. If a column mixes numbers stored as text with genuine numeric values, Excel may offer different filter behavior for the two types.
Filter by more than one column
For a combination such as several regions and one status, apply the region choices first and then open the Status dropdown and select the required status. Excel combines the active filters across columns, so the result is the intersection of those conditions. A useful example is keeping East and North orders while showing only rows marked Pending.
To inspect the current filters, look for the filter icon in each header and reopen the dropdown. If a result is unexpectedly empty, clear the filters one at a time rather than assuming the values are absent. A filter on a date, category, or status column can be responsible even when the dropdown you are currently viewing appears correct.
Use a slicer for a clickable filter
A slicer presents a column’s values as buttons, making it convenient to select several values without repeatedly opening dropdowns. Click inside the table, choose Table Design > Insert Slicer, select the column or columns, and confirm. Click a slicer button to filter; hold Ctrl on Windows while clicking to select multiple nonadjacent buttons. The slicer’s multi-select control can also be used where available.
Slicers are useful for dashboards or workbooks shared with people who may not know how to use filter menus. They take up worksheet space, and a slicer filters only the table or connected data it is configured for. In Excel for Mac, the slicer feature and ribbon labels can vary by release; Excel for the web supports working with many existing slicers, but creating or editing them may be limited compared with desktop Excel.
Use an Advanced Filter for a criteria range
Advanced Filter is a desktop Excel option for more elaborate criteria, especially when you want a reusable criteria area. Put the criteria in worksheet cells with the same header text as the table column. Choose Data > Advanced, specify the table as the list range and the criteria cells as the criteria range, then filter the list in place or copy matching records to another location.
Criteria on the same row are combined with AND; criteria on separate rows are combined with OR. For example, to include Region East or North, place the Region header above East on one criteria row and North on another. To require Region East and Status Open, put both matching headers and values on the same row. Advanced Filter is helpful when the criteria are too complex for a two-condition dropdown, but the criteria headers must match the source headers, and the range needs to be arranged carefully. It is available in desktop Excel; it is not a practical option in Excel for the web.
Return matching rows with a formula
If you need a separate, automatically updating results area instead of hiding rows in the original table, Excel 365 offers the FILTER and XMATCH functions. This is a formula-based alternative, not an ordinary table dropdown filter. Suppose the source data is an Excel table named Sales with a Region column, and the values to include are in H2:H4. Enter this formula in a blank cell outside the table:
With the source table named Sales and the Region choices in H2:H4, use =FILTER(Sales,ISNUMBER(XMATCH(Sales[Region],H2:H4)),"No matches"). If H2:H4 contains East, North, and West, the formula returns the complete table rows whose Region is one of those three values; if none match, it displays No matches.
FILTER and XMATCH are available in Microsoft 365 and Excel 2021 or later. The result spills into neighboring cells, so leave enough empty space and do not place the formula inside another Excel table. Unlike a dropdown filter, it creates a separate output and does not hide unmatched source rows. This is useful for reports, but if you only need to inspect the original table, the built-in filter is simpler.
Common problems and how to fix them
- A selected value does not appear: Check other column filters, clear them temporarily, and confirm the source rows are within the table.
- Similar-looking entries behave differently: Check for leading or trailing spaces, inconsistent spelling, and numbers stored as text. The dropdown treats distinct underlying values as separate items.
- The filter list seems incomplete: Confirm that the table includes all intended rows and that no blank row split the original data before it was formatted as a table. Use the table resize controls if new data was added outside its boundary.
- New rows are not included: Adding data immediately beneath a table often expands it, but data pasted elsewhere may not. Select a cell in the table and use Table Design > Resize Table to check or change its range.
- You want the full list back: Clear the relevant column filter or choose Data > Clear. Clearing a filter restores visibility; it does not undo edits made to cell contents.
On Mac, the core dropdown filtering behavior is similar, though the ribbon layout and some dialog details can vary. Excel for the web also supports ordinary table dropdown filters, but advanced desktop commands and some slicer tasks may be unavailable. If the exact command is missing, check whether the workbook is open in the desktop app and whether the selected range is an actual Excel table.
Frequently Asked Questions
Can I filter an Excel table for multiple values at once?
Yes. Open the column’s filter dropdown, clear Select All, check each value to include, and select OK. Excel shows rows matching any checked value.
Can I filter for multiple words with OR?
Yes. For two text conditions in one column, choose Text Filters and set the Custom AutoFilter connector to Or. For several exact words, select them in the checklist; for a longer criteria list, consider Advanced Filter.
Can I filter multiple columns at the same time?
Yes. Apply a filter to each column. Excel combines different columns with AND, so each visible row must satisfy every active column filter.
Does filtering delete rows that do not match?
No. A filter hides nonmatching rows from view. Clear the filter to show them again; use a separate delete operation only if you truly intend to remove records.
Why can’t I see all of my table’s values in the filter menu?
First check the search box and other active filters. Then confirm the table range includes all rows and that entries are not separated by inconsistent spaces, spelling, or data types.
Related
- Excel Shortcut to Refresh a PivotTable and All Data
- Excel Shortcut to Select All Data in a Column Quickly
- Excel Shortcut to Select a Whole Column in Seconds
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Insert a Row in Excel With a Formula: Real OptionsA formula cannot insert a physical worksheet row or move existing cells. To show a blank row inside…
- Excel VLOOKUP Name Errors: Causes, Fixes, and Real Examples#NAME? in an Excel VLOOKUP means Excel cannot recognize a name or text token in the formula; common…
- Excel Data Filter Multiple Criteria: Practical MethodsTo filter Excel data by multiple criteria, turn on Data > Filter, then set conditions in the column…
- Excel Sort Dates From Earliest to Latest: A Clear GuideTo sort dates in Excel from earliest to latest, select a cell in your date column, then choose…
- Excel Absolute Value Formula: How ABS Works With ExamplesIn Excel, use the ABS function to return a number’s absolute value: it removes the number’s negative sign…
- Excel Sum by Month: Formulas, Dates, and PivotTablesTo sum values by month in Excel, use SUMIFS with a start date for the month and an…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.