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