How to Filter Excel Data Using Optional Criteria in Any Order
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.

- 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.
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.
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.
Select your dataset and press Ctrl+T to convert it into a Table. Name the table 'Data' in the Table Design tab.
Designate specific cells (e.g., B4 to F4) above or next to your data where users can type their filter criteria.
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")).
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 Advanced Filter Feature for Complex Criteria
If you prefer a non-formula approach, Excel's Advanced Filter tool can handle complex optional criteria ranges.
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. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the data to be filtered.
- 2. Input the dynamic formula: Select the target cell and type your FILTER and ISBLANK combination formula just as you would in Excel.
- 3. View spilled results instantly: Press Enter to see the filtered data automatically spill into the neighboring cells based on your optional criteria.

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.




