Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

 How to 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 multiple columns based on single criteria with helper formula column

Filter multiple columns based on single criteria with Kutools for Excel


Filter multiple columns based on single criteria with helper formula column


Here, you can create a helper formula column, and then filter the data based on the helper cells, please do as this:

1. Enter this formula: =ISERROR(MATCH("Helen",A2:C2,0)) into cell D2, and then drag the fill handle down to the cells to apply this formula, and the FALSE and TRUE displayed into the cells, see screenshot:

Note: In the above formula: “Helen” is the criteria that you want to filter rows based on, A2:C2 is the row data.

2. Then select the helper column, and click Data > Filter, see screenshot:

3. And then click the drop down arrow in the helper column, and then check FALSE from the Select All section, and the rows have been filtered based on the single criteria you need, see screenshot:


Filter multiple columns based on single criteria with Kutools for Excel

If you have Kutools for Excel, the Super Filter utility also can help you to filter multiple columns based on single criteria.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. 

After installing Kutools for Excel, please do as follows:

1. Click Enterprise > Super Filter, see screenshot:

2. In the Super Filter dialog box:

(1.) Click button to select the data range that you want to filter;

(2.) Choose and set the criteria from the criteria list box to your need.

3. After setting the criteria, then click Filter button, and you will get the result as you need, see screenshot:

Click Download Kutools for Excel and free trial Now!


Demo: Filter multiple columns based on single criteria with Kutools for Excel

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.