Function Problems

How to Make Excel FILTER Criteria Optional When Cells Are Blank

Camila MilosovichCamila Milosovich Nov 10, 2026 870 views

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.

How to Make Excel FILTER Criteria Optional When Cells Are Blank
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.
Before you start

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.

Solution 1Recommended

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.

1
Define data ranges and criteria cells

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.

2
Start the LET function

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`).

3
Build the conditional mask for each filter

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)`.

4
Combine the masks using Boolean logic

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`.

5
Apply the FILTER function to the combined mask

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.

Use LET Function and Boolean Masks to Handle Blank Criteria
Use ="" instead of ISBLANK: Using `criteria_cell=""` is recommended over `ISBLANK(criteria_cell)`. ISBLANK may return FALSE if the cell contains a formula that outputs an empty string, which could unexpectedly break your dynamic filter.
Advanced Filtering in WPS Spreadsheet

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. 1. Launch WPS Spreadsheet: Open WPS Office on your device and load the workbook containing your dataset.
  2. 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. 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.
Full compatibility with advanced Microsoft Excel functions like FILTER, LET, SORT, and UNIQUE.High-speed data processing designed to handle large arrays and complex Boolean logic without lag.Completely free to use, featuring a lightweight footprint and a familiar tabbed interface.
microsoft office alternative - wps office

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.