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
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.
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...
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...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.