How to Make Excel FILTER Criteria Optional When Cells Are Blank
Question details
The user wants to create a dynamic FILTER formula that ignores optional criteria when input cells are left blank, while progressively narrowing down available data based on populated cells.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building an interactive search dashboard or reporting tool where users can enter multiple optional filters in any order without the formula breaking when fields are empty.
- Observed behavior
- Without a dynamic masking approach, the standard FILTER function fails to return proper results when criteria cells are blank, as it attempts to filter data strictly by empty values rather than ignoring the condition entirely.
Ensure you are using a version of your spreadsheet software that supports Dynamic Array formulas, such as Excel 365, Excel 2021, or the latest version of WPS Office.
Use LET Function and Boolean Masks to Handle Blank Criteria
This solution involves creating conditional Boolean masks. It evaluates each criteria cell individually: if a cell is blank, it returns TRUE to bypass the filter; otherwise, it applies the criteria logic.
By leveraging the LET function, you can declare your data arrays and filter criteria as clearly named variables. This keeps the formula organized and easy to troubleshoot.
Instead of directly comparing inside the FILTER function, we create a conditional mask using IF. By checking if the criteria is empty (e.g., criteria=""), we can output TRUE to ignore the blank input. Multiplying these individual masks together enforces 'AND' logic across all active filters.
Identify the data array you want to filter and the specific input cells where users will enter their criteria. For example, your data array might be A2:D100, and your criteria inputs might be in cells G1, G2, and G3.
Select the cell where you want the filtered results to start spilling. Type `=LET(` to begin defining the formula's variables, starting with your target data array (e.g., `data, A2:D100`).
Define a mask variable for each optional criterion. Use an IF statement to return TRUE if the input cell is blank. For example: `mask1, IF(G1="", TRUE, A2:A100=G1)`.
Add another variable to combine all your masks. To ensure results match ALL populated criteria (AND logic), multiply the masks together. Example: `combined_mask, mask1 * mask2 * mask3`.
Finish the LET function by using the combined mask inside the FILTER function. For example: `FILTER(data, combined_mask, "No results found")`. Close the parentheses and press Enter.

Build Dynamic Dashboards Easily with WPS Office
WPS Spreadsheet natively supports modern dynamic array functions, including FILTER and LET. This makes it incredibly easy to build sophisticated, interactive data dashboards with optional filtering criteria.
- 1. Launch WPS Spreadsheet: Open WPS Office on your device and load the workbook containing your dataset.
- 2. Enter the dynamic formula: Select the top-left cell of your intended output area and input your `=LET(..., FILTER(...))` formula to handle optional blanks.
- 3. Interact with your data: Test the filtering functionality by typing values into your criteria cells and deleting them. The spilled array will automatically adjust.

Frequently Asked Questions
Why does my FILTER formula return a #CALC! error when criteria are blank?
The #CALC! error usually appears because the FILTER function evaluates an empty array. If you do not explicitly tell Excel to ignore blank cells using an IF logic mask, it tries to find rows matching an exact blank value. If no such rows exist, it triggers the error.
Can I use OR logic instead of AND logic for multiple optional criteria?
Yes. In Boolean logic, multiplying masks (`mask1 * mask2`) applies AND logic, meaning all criteria must be met. To apply OR logic, where any met criteria includes the row, you add the masks together using a plus sign (`mask1 + mask2`).
Why does ISBLANK fail to ignore my empty criteria cell?
The ISBLANK function checks if a cell is completely empty. If your criteria cell contains a formula that evaluates to an empty string (""), or has a hidden apostrophe, ISBLANK sees it as non-empty and returns FALSE. Using `cell=""` safely accounts for true blanks and empty strings.




