Excel FILTER Function Ignores Blank Criteria: Fix

The Excel FILTER function returns an empty result or a #CALC! error when you try to filter for blank cells. This happens because the FILTER function treats blank criteria as no criteria at all and returns all rows from the source array. You need a specific workaround to make FILTER recognize and return only blank rows. This article explains why the problem occurs and provides two reliable methods to fix it.

Why FILTER Ignores Blank Criteria

The FILTER function uses the syntax FILTER(array, include, [if_empty]). The include argument must be an array of TRUE and FALSE values. When you pass a reference to a blank cell as the include argument, FILTER interprets that blank as a missing value and defaults to returning the entire array. The function does not convert a blank cell into a FALSE condition. This behavior is by design in Excel 365 and Excel 2021.

For example, if cell A1 is blank and you write =FILTER(B2:B100, A2:A100=A1, "No data"), FILTER compares each cell in A2:A100 to a blank cell. The comparison returns TRUE for every row because Excel treats a blank as equal to another blank in logical tests. The result is all rows from B2:B100, not just the blank rows.

The same issue occurs when you use a cell reference that contains a formula returning an empty string "". FILTER sees the empty string as a valid value and still returns all rows. The workaround requires using functions that explicitly return TRUE only for cells that are truly empty or contain no data.

Two Methods to Make FILTER Return Blank Rows

Both methods below solve the problem by converting the blank condition into a TRUE/FALSE array that FILTER can evaluate correctly. Choose the method that matches your data type.

Method 1: Use ISBLANK in the Include Argument

The ISBLANK function returns TRUE when a cell is completely empty and FALSE when it contains any value, including spaces or formulas. This method works for cells that are truly blank with no formulas.

1. Identify the column to check for blanks
   Suppose your data is in range A2:C100 and you want to filter rows where column B is blank. The source array for FILTER is A2:C100.
2. Write the ISBLANK condition
   In a new cell, enter =ISBLANK(B2:B100). This returns an array of TRUE for blank cells and FALSE for non-blank cells.
3. Combine with FILTER
   Enter the formula =FILTER(A2:C100, ISBLANK(B2:B100), "No blank rows found"). The ISBLANK function provides the include array. FILTER now returns only rows where column B is truly empty.
4. Test with a known blank row
   Verify that the result includes only rows where column B has no data. If you see rows with spaces or formula results, use Method 2 instead.

Method 2: Use a Helper Column with IF and ISBLANK

When your data contains formulas that return empty strings or cells with spaces that appear blank, ISBLANK alone returns FALSE. A helper column converts the blank condition into a numeric 1 or 0 that FILTER can evaluate.

1. Add a helper column next to your data
   Insert a new column to the right of your data range. For example, if your data is in columns A through C, insert column D.
2. Write the helper formula in the first row
   In cell D2, enter =IF(ISBLANK(B2), 1, 0). This returns 1 when B2 is blank and 0 when it is not. Copy the formula down to D100.
3. Use FILTER with the helper column condition
   Enter =FILTER(A2:C100, D2:D100=1, "No blank rows"). The condition D2:D100=1 creates a TRUE/FALSE array that FILTER uses to return only rows where column B is blank.
4. Hide the helper column if needed
   Right-click column D and select Hide. The FILTER result remains dynamic and updates when the source data changes.

If FILTER Still Returns All Rows or an Error

FILTER returns #CALC! error

The #CALC! error appears when FILTER finds no rows matching the criteria. This means every cell in the checked column contains a value, even if some appear blank. Use the LEN function to detect cells with spaces: =FILTER(A2:C100, LEN(TRIM(B2:B100))=0, "No blank rows"). TRIM removes extra spaces, and LEN returns 0 for cells that are truly empty or contain only spaces.

FILTER returns all rows despite ISBLANK

This happens when the cells you think are blank contain a formula that returns an empty string. ISBLANK returns FALSE for those cells. Replace ISBLANK with B2:B100="" in the include argument. The formula becomes =FILTER(A2:C100, B2:B100="", "No blank rows"). This checks for cells that are either truly empty or contain a formula returning an empty string.

FILTER returns wrong rows when using criteria from another cell

If you reference a cell that contains a formula returning blank, FILTER evaluates that blank as equal to every cell. Instead of referencing the cell, use the ISBLANK or LEN approach directly in the include argument. Do not use a cell reference for the criteria; hardcode the condition in the formula.

FILTER with ISBLANK vs FILTER with LEN=0: Key Differences

Item	FILTER with ISBLANK	FILTER with LEN=0
Detects truly empty cells	Yes	Yes
Detects cells with spaces	No	Yes, when combined with TRIM
Detects formula returning “”	No	Yes
Requires helper column	No	No
Best use case	Data entry without formulas	Data with formulas or imported text

You can now make the FILTER function return only blank rows by using ISBLANK for truly empty cells or LEN with TRIM for cells that appear blank but contain spaces or formula results. Try the TRIM and LEN combination first if your data comes from external sources or contains formulas. For a more advanced setup, combine FILTER with the BYROW function to check multiple columns for blanks in a single formula.