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 30 days. |
After installing Kutools for Excel, please do as follows:
1. Click Kutools Plus > 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
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- 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!
