Function Problems

How to Filter Excel Data Using Optional Criteria in Any Order

Camila MilosovichCamila Milosovich Nov 10, 2026 870 views

Question details

The user wants to create a dynamic Excel query page that allows filtering data using any combination of criteria, where blank criteria fields act as a match-all.

How to Filter Excel Data Using Optional Criteria in Any Order
Product
Excel
Device & OS
not provided
Scenario
Building a dynamic query page to search and filter data tables without needing to fill out every single filter field.
Observed behavior
The user needs a formula that treats blank filter inputs as wildcards (match-all), outputting the filtered dataset based only on the provided criteria.
Before you start

Ensure your dataset is formatted as an Excel Table (Ctrl+T) and note the table name and column headers you intend to filter. This solution requires dynamic array support, available in modern versions of Excel and WPS Spreadsheet.

Solution 1Recommended

Use the FILTER and ISBLANK Functions for Dynamic Optional Filtering

Combine the FILTER function with IF and ISBLANK to treat empty input cells as match-all conditions.

By multiplying boolean logic arrays, you can evaluate multiple criteria simultaneously. Using the IF and ISBLANK functions ensures that if a user leaves a search field blank, the formula assigns a value of 1 (TRUE) for that specific criterion, effectively ignoring it during the filtering process.

1
Format data as an Excel Table

Select your dataset and press Ctrl+T to convert it into a Table. Name the table 'Data' in the Table Design tab.

2
Set up your criteria cells

Designate specific cells (e.g., B4 to F4) above or next to your data where users can type their filter criteria.

3
Apply the dynamic formula

In the cell where you want the filtered results to appear, enter the formula: =LET(Filtered,FILTER(Data,IF(ISBLANK(B4),1,Data[Application]=B4)*IF(ISBLANK(C4),1,Data[Type]=C4)*IF(ISBLANK(D4),1,Data[Series]=D4)*IF(ISBLANK(E4),1,Data[Size]=E4)*IF(ISBLANK(F4),1,Data[Geometry]=F4)),IF(COUNTA(B4:F4),Filtered,"Select at least one criterion")).

4
Customize the column references

Replace 'Data[Application]', 'Data[Type]', etc., with the actual column names from your table, and update the criteria cell references (B4, C4, etc.) to match your layout.

Use the FILTER and ISBLANK Functions for Dynamic Optional Filtering
Dynamic Array Output: The filtered results will automatically spill into the adjacent cells. Ensure there is enough empty space below and to the right of the formula cell to avoid a #SPILL! error.

Easily Filter and Analyze Data with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array functions like FILTER, LET, and ISBLANK, allowing you to build robust, interactive query pages effortlessly.

  1. 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the data to be filtered.
  2. 2. Input the dynamic formula: Select the target cell and type your FILTER and ISBLANK combination formula just as you would in Excel.
  3. 3. View spilled results instantly: Press Enter to see the filtered data automatically spill into the neighboring cells based on your optional criteria.
Full compatibility with Microsoft Excel formulas and .xlsx formats.Supports advanced dynamic array functions for complex data filtering.Free, lightweight, and easy to navigate with a familiar tabbed interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #CALC! error with my FILTER formula?

The #CALC! error typically occurs when the FILTER function returns an empty array (no records match your criteria). You can fix this by adding the optional [if_empty] argument at the end of your FILTER function, like FILTER(Data, criteria, "No results found").

Can I use wildcard characters like an asterisk (*) with the FILTER function?

The FILTER function does not natively support wildcards like * or ?. Instead, you should use search functions combined with ISNUMBER, such as ISNUMBER(SEARCH("keyword", Data[Column])), to achieve wildcard-like partial matching.

How do I make the filter case-sensitive?

By default, criteria matching in the FILTER function is case-insensitive. To perform a case-sensitive filter, use the EXACT function within your criteria logic, like EXACT(Data[Column], "SearchTerm").

Why does my FILTER formula return a #SPILL! error?

#SPILL! means there is not enough empty space on the worksheet to display all the filtered results. Clear any text or data in the cells below and to the right of your formula to allow the array to expand.