Formula Errors

How to Filter Excel Rows When a Value Appears in Any Column

Steven Parker Oct 13, 2026 869 views

Question details

The user wants to filter a dataset to display rows where a specific target value exists in at least one of multiple columns.

How to Filter Excel Rows When a Value Appears in Any Column

Product: Excel
Device & OS: not provided
Scenario: Attempting to filter data across multiple columns using standard tools results in rows being excluded if the value is in a different column than the currently filtered one.
Observed behavior: Standard column filtering uses AND logic, hiding rows if the value is in another column. The goal is to apply an OR condition across columns to retain all relevant rows.

Before you start

Ensure your dataset has clear column headers and no merged cells before applying advanced filters or dynamic array formulas.

Solution 1Recommended

Use the FILTER Function with an OR Condition

Best for Microsoft 365 and newer versions of Excel/WPS Office that support dynamic arrays.

In modern spreadsheet applications, the FILTER function can dynamically extract records based on criteria. By using the addition operator (+), you can combine multiple arrays to create an 'OR' logical condition across different columns.

Select an output area

Click on an empty cell where you want the new filtered data to appear. Ensure there is enough empty space below and to the right for the results to spill over.

Enter the FILTER formula

Type the formula: =FILTER(A2:C13, (A2:A13="Target") + (B2:B13="Target") + (C2:C13="Target"), "No matches").

Customize the formula parameters

Replace A2:C13 with your full data range, update the individual column ranges, and change "Target" to the exact value you are searching for.

Execute the formula

Press Enter. The formula will automatically spill the results into the adjacent cells, showing all rows that contain the target value in any of the specified columns.

Dynamic Updates: Because this is a formula, your filtered results will automatically update if you change the original source data.

Solution 2

Apply an Advanced Filter with Separate Criteria Rows

Ideal for older versions of Excel or when you prefer a non-formula approach to extract data in place.

Effortless Data Filtering with WPS Spreadsheet

Easily Filter Complex Data Across Columns Using WPS Spreadsheet

WPS Spreadsheet provides powerful data manipulation tools, including Advanced Filter features and dynamic array formulas, helping you quickly extract exactly the rows you need without complex workarounds.

1. Open your file: Launch WPS Spreadsheet and open the workbook containing your dataset.
2. Set up criteria: Create a criteria range by copying headers and placing your target value diagonally beneath them.
3. Use Advanced Filter: Navigate to the Data tab and select Advanced Filter.
4. Apply logic: Define your List range and Criteria range, then click OK to instantly filter your rows based on values across multiple columns.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Intuitive Advanced Filter interface for complex OR/AND criteria setups.Lightweight software that processes large datasets quickly and smoothly.

Frequently Asked Questions

Why does standard AutoFilter hide rows containing my value in other columns?

Standard AutoFilter applies an 'AND' condition between different columns. If you filter Column A for a specific word, it hides all rows where that word isn't in Column A—even if it exists in Column B. You cannot use AutoFilter to create an 'OR' condition across different columns.

Can I use conditional formatting instead of filtering to find these rows?

Yes, if you only need to see where the values are without hiding the rest of the data. Select your entire data range, go to Home > Conditional Formatting > Highlight Cells Rules, and choose 'Text that Contains' to highlight the specific values.

What does the plus sign (+) mean in the FILTER formula?

In Excel and WPS dynamic array formulas, the plus sign (+) acts as an 'OR' logical operator. It adds the boolean arrays together, so if any column condition evaluates to TRUE (1), the sum is greater than zero and the row is included in the final result.

Excel & Data Problems