How to Filter for Multiple Values in Excel
Last Updated on April 25, 2024
If you work with Excel data, chances are you’ll eventually need to filter multiple values in one cell to rearrange or apportion your worksheet. Checking items one after another might become a cumbersome task depending on how large your worksheet is.
Thankfully, Excel provides options to automatically filter multiple values in your worksheet, saving you time and energy. In this article, we’ll explore different methods of filtering values you can use in different situations.
How to Filter for Multiple Values in Excel
Data filtering is a process that involves examining your worksheet to exclude, rearrange, or apportion data based on specific criteria. Filtering for multiple values in a worksheet is a simple task that doesn’t require extensive Excel training.
Below are the four different ways you can filter multiple values in Excel:
Filter Multiple Values in a Column
If you have values in a single column you want to filter for, click on the ‘Data’ tab, and open the ‘Advanced Filter’ dialog box.
In the dialog box, click ‘Action,’ then choose the list filter option. Below are the meanings of the options in the pop-up window:
- List range—the section of your data sheet that you want to filter.
- Criteria range—what you use to define the criteria for filtering the listed range.
- Copy to—used to assign the location where you want your filtered results to be copied.
In the pop-up window, select the values in the column you want to filter for. In the ‘Criteria’ range, specify the list of multiple values you want to filter. Note that you need to use the same name for both the filter column and the criteria list. Click ‘OK,’ and Excel will filter the data range you selected using the specified criteria.
Filtering Multiple Values in Excel
The’ Filter’ tool can easily filter multiple values in Excel. First, select the range of data you want to filter to use the filter tool.
After selecting the data, from the ‘Data’ tab on the top of your screen, go to ‘Filter’ in the ‘Sort and Filter’ category.
Click on ‘Filter,’ and a filter drop-down will appear in the header of every column you have selected. When you click the filter drop-down, a new window with different criteria to filter the column will appear.
Alternatively, the pop-up contains the list of values in that column. Select one of the values and press ‘OK’ to filter the information on the selected value from your worksheet.
Filter Multiple Values in One Column With a Helper Column
A helper column is a new column added to a dataset to help simplify a complex operation. To use this method, enter the COUNTIF command into the cell next to the data you want to filter for. You can also use the COUNTIF command to remove rows in Excel based on criteria.
For example, enter ‘=COUNTIF($C$2:$C$9, A2)’ into cell D2. ‘$C2:$C9’ is the value list (criteria) you want to use in filtering, and A2 is the first cell of the column that you want to filter.
After entering the function, drag the fill handle down to the cells you want to use (Down D2). The cells you fill will display 1 or 0, with 1 representing the values you want to filter based on.
Highlight the helper column (D), click on the ‘Data’ tab, select ‘Filter,’ and check ‘1’ from the ‘Select All’ section. When you do this, all the values you specified will be filtered out. Another simple option you can master to help with this is how to find the max value in Excel.
In Conclusion
The filter command is a powerful tool for working with large Excel sheets. It’s easy to learn and will save you the significant time you would otherwise spend manually examining values. If you have any questions, contact our team to learn more about our Excel certifications and courses.
