Table of Contents
To filter data in Excel by a list of values, follow these steps:
1. Select the column containing the data you want to filter.
2. Click on the “Data” tab on the top menu bar and then click on the “Filter” button.
3. A drop-down arrow will appear next to the column heading. Click on it and select “Filter by Selected Cell’s Value”.
4. In the “Custom AutoFilter” box, select “equals” from the first drop-down menu and then enter the list of values you want to filter by in the second drop-down menu.
5. Click “OK” to apply the filter. The data will now be filtered by the selected values, showing only the rows that match the criteria.
Filter by List of Values in Excel
You can use the following syntax to filter a dataset by a list of values in Excel:
=FILTER(A2:C11,COUNTIF(E2:E5,A2:A11))
This particular formula filters the cells in the range A2:C11 to only return the rows where cells in the range A2:A11 contain a value from the list of values in the range E2:E5.
The following step-by-step example shows how to use this syntax in practice.
Step 1: Enter the Data
First, let’s enter the following dataset in Excel that contains information about various basketball players:
Step 2: Define List of Values
Next, let’s define a list of Team values that we’d like to filter by:
Step 3: Filter by List of Values
Next, let’s type the following formula into cell A14 to filter the dataset by the list of Team names we defined:
=FILTER(A2:C11,COUNTIF(E2:E5,A2:A11))
The following screenshot shows how to use this formula in practice:
Notice that the filtered dataset only contains the rows where the Team names from original dataset in the range A2:C11 are in the list of names in the range E2:E5.
Note: The FILTER function is case-insensitive. This means if you type “nets” instead of “Nets”, the function will still return the row with the “Nets” in the Team column.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I filter data in Excel by a list of values?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-filter-data-in-excel-by-a-list-of-values/
stats writer. "How can I filter data in Excel by a list of values?." PSYCHOLOGICAL SCALES, 26 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-filter-data-in-excel-by-a-list-of-values/.
stats writer. "How can I filter data in Excel by a list of values?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-filter-data-in-excel-by-a-list-of-values/.
stats writer (2024) 'How can I filter data in Excel by a list of values?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-filter-data-in-excel-by-a-list-of-values/.
[1] stats writer, "How can I filter data in Excel by a list of values?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I filter data in Excel by a list of values?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.