How to filter multiple values in only one column in Excel?
It is easy for us to filter a specific value in a column with the Filter function in Excel, but, sometimes, you may need to filter multiple values from a long column as below screenshot shown. To check the items one by one into the long Filter list box may not be the best way for filtering. In this article, I will talk about some quick ways for solving this job.
|Original Data||Filter based on||Filter result|
In Excel, the Advanced Filter function can help you to filter multiple values in a column quickly and easily. Please do as this:
1. Click Data > Advanced, see screenshot:
2. In the Advanced Filter dialog box, please do the following operations:
(1.) Select Filter the list, in-place option from the Action section;
(2.) Then, select the data range that you want to filter in the List range, and specify the list of multiple values you want to filter based on in the Criteria range; (Note: The header name of the filter column and criteria list must be the same.)
3. Then, click OK button, your specified items have been filtered out from the data range.
In this section, you can also use a helper column for dealing with this job.
1. Enter the below formula into a cell – C2 beside your data:
Note: In the above formula, E2:E5 is the value list that you want to filter based on, and A2 is the first cell of the column that you want to filter.
2. Then, drag the fill handle down to the cells that you want to use, and the cells will display 1 or 0, 1 indicates the values that you want to filter based on.
3. Then, select the helper column, and click Data > Filter, in the filter list box, check 1 from the Select All section, see screenshot:
3. And, all the values you specified have been filtered out, see screenshot:
|Original Data||Filter based on||Filter result|
Sometimes, you may want to save the filter criteria after filtering for reusing next time, with Kutools for Excel’s Super Filter feature, you can not only filter data by multiple criteria in one or more columns, but also can save the filter criteria as you need.
After installing Kutools for Excel, please do as this:
1. Select the data range that you want to filter, and then, click Kutools Plus > Super Filter, see screenshot:
2. In the Super Filter pane, set the following filter criteria as you need:
(1.) In the Relationship in Group drop down, select Or option;
(2.) Then specify the filter criteria in the criteria box one by one as you need;
(3.) Click Filter button.
3. And, you will get the filter result as you need, see screenshot:
Tips: You can save the current filter criteria and reuse it next time as below screenshot shown:
More relative articles:
- Apply Same Filter To Multiple Sheets In Excel
- It may be easy for us to apply the Filter function to filter data in a worksheet, but, sometimes, you may need to filter across multiple worksheets which have common data formatting with the same filter criteria. To filter them one by one will waste lots of time, here, I can introduce an easy way to solve it at once.
- Filter Data By Multiple Colors In Excel
- Normally, in Excel, you can quickly filter rows with only one color, but, have you ever considered filtering rows with multiple colors at the same time? This article, I will talk about quick trick for you to deal with this problem.
- Filter Multiple Columns Based On Single Criteria In Excel
- If you have multiple columns which you want to filter some of them based on single criteria, for example, I need to filter the Name 1 and Name 2 columns if the cell contains the name “Helen” in any one of the two columns to get the following filter result. How could you finish this job quickly as you need?
- Filter Data From One Worksheet To Another Dynamically In Excel
- We can easily filter data and copy the filtered data to another location of active worksheet by using the Advanced Filter function, but, have you ever tried to filter data from one worksheet to another sheet and make the filter dynamically? That means, if data changes in the original sheet, the new filtered data will be changed as well. This article, I will introduce the Microsoft Query feature in Excel to solve this job.
- Filter Multiple Columns Simultaneously In Excel
- When you apply the Filter function, after filtering one column, the next columns will be only filtered based on the result of the previous filtered column. It means that only AND criteria can be applied to more than one column. In this case, how could you apply both the AND and OR criteria to filter multiple columns simultaneously in Excel worksheet?
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 months agoThanks for sharing social security information, it helps me lot in my work .
- To post as a guest, your comment is unpublished.· 4 months agoHello and thank you for this guide. Can you please share with us how to remove anchor from Exel File ? thank you.
Clara from microphone USB : https://boutiqueaz.com/tech/comparatif-hi-tech/comparatif-micro-usb/