Filter an Excel Column Based on a List of Values

To filter a column in Excel for Microsoft 365 on Windows, select your data, choose Data > Filter, open the target column’s dropdown, and check the values you want to keep. This guide covers that built-in filter plus criteria ranges, formulas, Power Query, slicers, and VBA, so you can choose a method for a quick one-off filter or a repeatable workflow.

1. Select Values in the AutoFilter Checklist

For a quick interactive filter, click a cell in your data and choose Data > Filter; then open the dropdown on the column you want to filter and select the checkboxes beside the values to keep. Clear Select All first when the column contains many different entries, then tick the required values and click OK; Excel hides rows that do not match while leaving the underlying data unchanged. This is the simplest choice for a one-time review, and it supports as many selected values as appear in the dropdown, unlike a custom two-condition filter.

2. Search Within the Filter Dropdown

When the column has a long checklist, use the Search box at the top of the AutoFilter dropdown to find a value rather than scrolling through every entry. Search for the first value, check its result, then search for another and check that result too; clear the search text between searches so previously selected values remain selected, and click OK when the selections are ready. This method is useful for large category lists, but it is still a manual selection: if values change or new rows arrive, reopen the dropdown and update the choices.

3. Use Advanced Filter with a Criteria Range

Advanced Filter is a good built-in option when you want to filter by a reusable list without writing a formula, or copy matching records to another part of the sheet. Put the exact column heading, such as Status, in G1 and put Open and Pending in G2 and G3; then select the source data and choose Data > Advanced, set the criteria range to G1:G3, and choose Filter the list, in-place or Copy to another location. Criteria on separate rows mean OR, so this keeps rows whose Status is Open or Pending; the criteria heading must match the source heading exactly, and this command does not automatically expand the criteria range when you add more choices.

4. Return Matching Rows with FILTER and XMATCH

With statuses in A2:A8, owners in B2:B8, and allowed statuses in E2:E3, enter this formula in an empty cell: =FILTER(A2:B8,ISNUMBER(XMATCH(A2:A8,E2:E3)),"No matches"). In Microsoft 365 or Excel 2021 and later, it spills the matching two-column records into neighboring cells; for the sample list Open and Pending, the result is four records, with owners Ana, Cara, Dev, and Gus. This is ideal for a live report because editing the allowed list recalculates the output, but keep the spill area clear or Excel will return a #SPILL! error.

5. Match a List with COUNTIF and FILTER

With statuses in A2:A8, owners in B2:B8, and the accepted values Open and Pending in E2:E3, use =FILTER(A2:B8,COUNTIF(E2:E3,A2:A8)>0,"No matches") in an empty cell. COUNTIF checks each source status against the allowed-values range, and FILTER returns the records where the count is greater than zero; in this example it returns the same four matching records as the XMATCH approach. This alternative is straightforward when readers already understand COUNTIF, though the formula still needs Microsoft 365 or Excel 2021 for the dynamic FILTER results.

6. Add a Helper Column with MATCH

With source statuses in A2:A8 and the allowed list in E2:E3, enter =ISNUMBER(MATCH(A2,$E$2:$E$3,0)) in C2, then fill it down through C8. The helper column displays TRUE for Open and Pending and FALSE for the other statuses; for the sample data, rows 2, 4, 5, and 8 are TRUE, so apply Data > Filter and filter column C to TRUE. This method is handy when you need to see why each record is included or excluded, and it also works in older Excel versions that lack FILTER, although you must copy the formula to new rows.

7. Combine the Data and List in Power Query

Power Query suits recurring imports where both the main records and the permitted values are stored in Excel tables or other data sources. Convert each range to a table with Ctrl+T, load each table with Data > From Table/Range, then in Power Query choose Home > Merge Queries, select the status column in both queries, and choose an Inner join; expand the merged column if you need the original record fields. An inner join keeps only records with a matching status, and Refresh updates the result after the source tables change; remove duplicates from the allowed-values table first, since duplicate keys there can produce repeated matching records.

8. Set a Custom AutoFilter for Two Values

If you need exactly two text values and want a conventional dialog, open the column dropdown, choose Text Filters > Custom Filter, set the first condition to equals Open, select Or, and set the second condition to equals Pending. Excel displays rows matching either condition without requiring you to maintain a separate criteria range, which makes this a quick option for a short, clearly defined filter. The dialog supports only two conditions in this setup, so for a longer list use the checklist, Advanced Filter, or a formula instead.

9. Add a Slicer to an Excel Table

A slicer gives a table a clickable filter panel that is easier to use during a meeting or in a shared workbook than opening dropdown menus. Click inside the table, choose Table Design > Insert Slicer, select the Status field, and click OK; select the slicer’s multi-select button before clicking Open and Pending to keep both values visible. Slicers work well for repeated hands-on exploration and can be cleared with the clear-filter icon, but they occupy worksheet space and are not a good choice when the goal is to create a separate, formula-driven output range.

10. Type a Comma-Separated List with TEXTSPLIT

With source statuses in A2:A8, owners in B2:B8, and the exact text Open,Pending in H2, enter =FILTER(A2:B8,ISNUMBER(XMATCH(A2:A8,TEXTSPLIT(H2,","))),"No matches") in an empty cell. TEXTSPLIT separates the typed list at each comma, XMATCH checks each status against those entries, and the example returns four records; do not add spaces after the commas unless you also trim the split values, because a leading space can prevent an exact match. TEXTSPLIT is available in Microsoft 365 and Excel 2024, making this a compact option when you want to edit criteria in one cell rather than maintain a vertical list.

How to Choose

Start with how often the filter will change. For a one-off task, the AutoFilter checklist is usually fastest; for a compact rule with only two alternatives, Custom AutoFilter avoids extra setup. If your list is maintained on the sheet and you want a live result, choose FILTER with XMATCH or COUNTIF, and use a helper column when transparency matters more than having a compact formula.

For repeatable data imports, Power Query is usually easier to refresh and audit than repeating manual clicks, provided the source and allowed values can be maintained as tables or queries. Choose Advanced Filter when you need a native one-time copy of matching records, and choose a slicer when people need to change the filter visually. Consider your Excel version before using newer functions: FILTER and XMATCH require Excel 2021 or Microsoft 365, while TEXTSPLIT requires Microsoft 365 or Excel 2024.

Frequently Asked Questions

Do these methods match values exactly?

XMATCH and MATCH use exact matching in the examples, and the criteria-list methods also require the values to correspond to the source entries. AutoFilter can also apply text conditions such as Contains, but that is a different rule from selecting exact list values.

Does the order of the allowed values affect the result?

No. The formulas return matching source rows in their original order, not in the order of the list in E2:E3. To sort the output, add a sort operation separately, such as SORT in Microsoft 365 or Excel 2021.

What if the source contains extra spaces?

Leading or trailing spaces can make an entry appear not to match, especially with exact-match formulas. Clean the source and criteria consistently, for example with TRIM in a separate cleanup column, and check for nonbreaking spaces if copied data still behaves unexpectedly.

Will the formulas include new rows automatically?

Not when they use fixed ranges such as A2:A8. Convert the source data to an Excel Table so it expands as rows are added, then use structured references in your formulas; for Advanced Filter or a helper column, update the selected ranges or fill the helper formula into new rows.

Can I use these steps on a Mac or in Excel for the web?

Most filtering concepts and the formula methods are also available in current Excel for Mac and Excel for the web, but ribbon placement and some Power Query or VBA capabilities differ. The Windows shortcut Ctrl+T for creating a table is Command+T on Mac; VBA macros require a desktop Excel app that supports VBA, not Excel for the web.

Final Thoughts

For a quick selection, use the column dropdown; for a list that changes, use Advanced Filter or a formula, and for repeated imports, consider Power Query. Match the method to how often the criteria change and who needs to operate the workbook, then test it on a few known records before relying on the filtered output.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.