Others

How to Filter an Excel Column by Multiple Values

Camila Milosovich Sep 28, 2026 869 views

Question details

The user needs a quick way to filter a large dataset based on a predefined list of multiple criteria without having to select each value manually.

How to Filter an Excel Column by Multiple Values

Product: Microsoft Excel
Device & OS: not provided
Scenario: Filtering thousands of rows of data against a specific external list of approved IDs or values.
Observed behavior: Standard filters require checking values one by one, which is extremely inefficient when filtering by a large list of criteria.

Before you start

Ensure your main dataset contains clear column headers and no completely blank rows or columns to prevent filtering errors.

Solution 1Recommended

Use the Advanced Filter Tool

The Advanced Filter is the most efficient built-in way to filter a column by a large list of multiple values in place.

By setting up a separate criteria range on your spreadsheet, you can command Excel to filter your source data using as many specific values as needed simultaneously.

Create a Criteria Range

Place the list of values you want to filter by (e.g., your 15 material IDs) in a separate blank range. Ensure the header cell of this criteria list exactly matches the header of the column you are filtering in the source data.

Open Advanced Filter

Click anywhere inside your main source dataset, then go to the Data tab on the ribbon and select Advanced in the Sort & Filter group.

Configure Filter Settings

Choose the option 'Filter the list, in-place'. Set the 'List range' to encompass your entire source data and set the 'Criteria range' to highlight the cells containing your newly created criteria header and values.

Apply the Filter

Click OK. The spreadsheet will instantly hide all rows that do not match the values specified in your criteria list.

Tip: If you want to extract the matching data instead of hiding rows, you can choose 'Copy to another location' in the Advanced Filter dialog box.

Solution 2

Use the FILTER and XMATCH Functions

For newer versions of Excel, combining dynamic array functions allows you to extract filtered data automatically without altering the original list.

Efficient Data Management

Filter Spreadsheets by Multiple Criteria in WPS Office

WPS Spreadsheet provides powerful data processing tools, including Advanced Filter and dynamic array formulas, allowing you to manage, analyze, and filter massive datasets easily.

1. Open the Workbook: Launch WPS Spreadsheet and open your existing data file.
2. Set Up Criteria: Copy your target filtering values into a blank column area and add a header that identically matches your source data's column header.
3. Access Data Tools: Navigate to the Data tab on the top ribbon and click on Advanced.
4. Apply the Filter: Highlight your List range and Criteria range in the prompt window, then click OK to instantly filter the data.

Fully compatible with Microsoft Excel (.xlsx and .xls) file formatsBuilt-in Advanced Filter tool for handling complex multi-value criteriaSupports modern dynamic array formulas like FILTER and XMATCHLightweight application with a familiar, easy-to-use interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is the Advanced Filter not working for my data?

The most common reason for an Advanced Filter failing is mismatched headers. Ensure the column header in your criteria range is spelled and formatted exactly the same as the header in your source data. Additionally, clear any entirely blank rows from your dataset.

Can I use standard AutoFilter to filter multiple values?

Yes, but standard AutoFilter requires you to manually click and check the boxes for each value in the drop-down menu. This becomes extremely tedious and error-prone if you need to filter by dozens of specific values, which is why using an Advanced Filter is highly recommended.

Why am I getting a #NAME? error when using the FILTER function?

The #NAME? error typically occurs if you are using an older version of Excel that does not support modern dynamic array functions. If your version lacks the FILTER or XMATCH functions, you should use the Advanced Filter method instead.

Office Quick Solutions