Use the Excel FILTER Formula With Optional Blank Criteria
Question details
Create a FILTER formula that ignores one or more criteria when their filter cells are blank.

- Product
- Microsoft Excel FILTER function
- Device & OS
- not provided
- Scenario
- The user wants to filter records by multiple optional criteria cells while showing all matching records for any criterion left empty.
- Observed behavior
- A blank optional filter should accept every value in its corresponding source column, while nonblank filters should limit the returned records.
Make sure your source ranges have the same first and last row, such as A2:D100, B2:B100, and C2:C100. Enter the formula in an empty area so the dynamic-array results have room to spill.
Use IF to make each Excel FILTER criterion optional
Use this formula when blank filter cells should be ignored and filled filter cells should apply their selected values.
Each IF expression returns the full source column when its criterion cell is blank. Comparing the column with itself produces TRUE for every row, so that condition does not restrict FILTER results.
The multiplication operator (*) combines the criteria as AND conditions, meaning every nonblank criterion must match.
Place the records to return in A2:D100. Use G2 as the optional filter for column B and H2 as the optional filter for column C.
Select an empty output cell, such as J2, and enter =FILTER(A2:D100,(B2:B100=IF(G2="",B2:B100,G2))*(C2:C100=IF(H2="",C2:C100,H2)),"No results").
Type a value in G2 and/or H2 to filter matching rows. Clear G2 to ignore column B, clear H2 to ignore column C, or clear both cells to return all records.

Use LET for a clearer multi-criteria FILTER formula
Use this version when you have several optional filters and want to define an all-rows condition once.
Create optional FILTER criteria in WPS Spreadsheet
WPS Spreadsheet lets you build dynamic FILTER formulas that return all rows for blank criteria and narrow results as users choose filter values. Enter the formula once in an open output area, then update the criterion cells to refresh the results.
- 1. Open the worksheet in WPS Spreadsheet: In WPS Office, open the workbook and select the worksheet containing the source table and criteria cells.
- 2. Reserve a spill range: Click an empty output cell, such as J2, and make sure the cells where results will expand are not occupied.
- 3. Enter the FILTER formula: Type =FILTER(A2:D100,(B2:B100=IF(G2="",B2:B100,G2))*(C2:C100=IF(H2="",C2:C100,H2)),"No results") and press Enter.
- 4. Test blank and selected criteria: Clear G2 or H2 to ignore that condition, then enter values in either cell to return only matching records.

Frequently Asked Questions
Why does a blank criterion normally return no rows in FILTER?
A direct test such as B2:B100=G2 searches for blank cells when G2 is blank. Wrap the test in IF(G2="",B2:B100,G2) so a blank G2 makes the comparison B2:B100=B2:B100, which accepts every row.
How do I add a third optional FILTER condition?
Add another multiplied condition using the same pattern. For example, to filter column D with I2, add *(D2:D100=IF(I2="",D2:D100,I2)) to the include argument.
Can optional FILTER criteria use OR instead of AND?
Yes. Use addition (+) between conditions for OR logic, but ensure each condition still handles blank criteria. Use multiplication (*) when all filled criteria must match.
What does the FILTER formula return when all criteria cells are blank?
It returns every row in A2:D100 because each blank criterion is converted into an always-true comparison for its source column.
Why is my FILTER result showing a spill error?
Clear cells blocking the output range and enter the formula in a location with enough empty cells below and to the right. Dynamic-array results cannot expand over existing content.




