Function Problems

Use the Excel FILTER Formula With Optional Blank Criteria

Jason MillerJason Miller Nov 12, 2026 869 views

Question details

Create a FILTER formula that ignores one or more criteria when their filter cells are blank.

Use the Excel FILTER Formula With Optional Blank Criteria
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.
Before you start

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.

Solution 1Recommended

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.

1
Set the source data and criteria cells

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.

2
Enter the optional-criteria FILTER formula

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

3
Change or clear the filter values

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 IF to make each Excel FILTER criterion optional
Formula behavior: The text "No results" appears only when the selected nonblank criteria do not match any records.
Work with dynamic array formulas in WPS Spreadsheet

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. 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. 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. 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. 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.
Use FILTER with optional criteria cells for flexible record searchesDynamic arrays spill matching rows automatically into the worksheetOpen and edit Microsoft Excel .xlsx files with formula-compatible spreadsheet toolsUse a familiar spreadsheet interface for formulas, tables, and cell references
microsoft office alternative - wps office

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.