Function Problems

How to Use Excel FILTER Function for Row and Column Criteria

Olivia Miller Sep 29, 2026 868 views

Question details

The user needs to learn how to filter data in Excel by applying multiple criteria to rows and extracting specific columns simultaneously using the FILTER function.

How to Use the Excel FILTER Function for Row and Column Criteria

Product: Excel
Device & OS: not provided
Scenario: Filtering complex datasets where conditions apply to multiple rows, and only specific columns from the result need to be extracted.
Observed behavior: Requires practical formula examples to dynamically extract specific rows and columns matching defined criteria.

Before you start

Ensure your version of Excel or WPS Spreadsheet supports dynamic array functions. The FILTER and CHOOSECOLS functions are available in Microsoft 365, Excel 2021, and the latest versions of WPS Office.

Solution 1

Filter Rows Based on Multiple Criteria

Use the FILTER function with the multiplication operator (*) to apply multiple AND conditions to your dataset rows.

The FILTER function normally evaluates a single condition. By placing conditions in parentheses and multiplying them, you create an array of 1s and 0s where both conditions must be true.

Select the destination cell

Click on an empty cell where you want the top-left corner of your filtered data to appear.

Input the FILTER formula

Type the formula: =FILTER(A2:D100, (B2:B100="Open")*(C2:C100>100), "No results"). Adjust the ranges to match your specific dataset.

Execute the function

Press Enter to execute the formula. The dynamically filtered array will spill into the adjacent cells, showing rows that meet both criteria.

Understanding the logic: The asterisk (*) acts as an AND operator. If you need an OR condition, use a plus sign (+) between the condition arrays instead.

Solution 2Recommended

Extract Specific Columns using FILTER and CHOOSECOLS

Combine the FILTER function with the CHOOSECOLS function to return only the exact columns you need from the filtered rows.

Advanced Data Analysis

Filter Complex Data Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and CHOOSECOLS. You can effortlessly manage complex data criteria and extract precise information without complex VBA code, all within a familiar interface.

1. Open your dataset: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx).
2. Select a blank cell: Click on an empty cell where you want your new dynamic table to start.
3. Enter the formula: Type your =CHOOSECOLS(FILTER(...)) formula exactly as you would in standard Excel.
4. View your results: Press Enter. The data will spill perfectly, extracting your chosen rows and columns instantly.

Fully compatible with Microsoft Excel formulas, including dynamic arraysLightning-fast processing for large datasets with multiple conditionsFree to use with a clean, intuitive tabbed interfaceCross-platform support allows filtering on Windows, Mac, and mobile

microsoft office alternative - wps office

Frequently Asked Questions

Why is my FILTER function returning a #CALC! error?

This error occurs when the FILTER function finds no matching records and the [if_empty] argument is missing. To fix it, add a fallback text at the end of your formula, like =FILTER(A2:B10, A2:A10="Criteria", "No match").

Can I use OR logic instead of AND logic in the FILTER function?

Yes. To apply OR logic, use a plus sign (+) between your conditions instead of an asterisk (*). For example, =FILTER(A2:D100, (B2:B100="Open")+(C2:C100>100)) returns rows that meet either condition.

What if my version of Excel doesn't support the CHOOSECOLS function?

If CHOOSECOLS is unavailable in your version, you can achieve similar results by nesting the FILTER function inside the INDEX function, or by simply filtering all columns and manually hiding the columns you do not wish to display.

Excel & Data Problems