Skip to main content

How to filter multiple values in only one column in Excel?

Author: Xiaoyang Last Modified: 2019-12-03

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

Filter multiple values from one column with the Advanced Filter function

Filter multiple values from one column with a helper column

Filter multiple values from one column and save the filter criteria for future using

Filter multiple values from one column with the Advanced Filter function

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.

Filter multiple values from one column with a helper column

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:

=COUNTIF($E$2:$E$5, A2)

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

Filter multiple values from one column and save the filter criteria for future using

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.

Tips:To apply this Super Filter feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

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:

Click to Download Kutools for Excel and free trial Now!

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?

  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Que complicado que es para algo tan sencillo, hay cosas q deberían de copiar a Google sheets!
This comment was minimized by the moderator on the site
Thank you, Helpful information :)
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations