Loadingwiki693

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.

In Excel for Mac, you can sort a list of data by days of the week or months of the year. Or, create your own custom list for items that don't sort well alphabetically. You can also sort by font color, cell color, or icon sets. So, speaking of design flaws, Excel in Microsoft Office 2016 for the Mac tries to present 'intelligent' filtering for a column based on the data in the column. I'm talking about the filter menu in the filter 'Choose One' dropdown, entries such as Equals, Does Not Equal, Begins With, Ends With, etc. Mar 11, 2011 Arial Renaud teaches you how to Freeze Row's and Column's in Excel 2008 for the Mac. Excel 2008 for Mac- Resizing and Hiding Rows & Columns - Duration: 3:06.

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:

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:

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:

More relative articles:

Excel For Mac 2008 How To Filter On A Column 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.

• 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.

• 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?

• 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.

• 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?

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• 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...

• 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!

Excel For Mac 2008 How To Filter On A Column Diagram

• To post as a guest, your comment is unpublished.
  Thank you, Helpful information :)