Excel FILTER Function Returns Empty Array Unexpectedly: Fix

You wrote a FILTER formula expecting a list of matching records, but the result is the #CALC! error or a blank cell. This happens when the FILTER function cannot find any row that meets your logical test. The function is designed to return an empty array when no rows match, but that behavior can be confusing when you believe matches exist. This article explains the exact reasons FILTER returns an empty array and shows you how to fix each cause.

Why FILTER Returns an Empty Array Instead of Data

The FILTER function syntax is FILTER(array, include, [if_empty]). The include argument must be a Boolean array — an array of TRUE and FALSE values — that has the same number of rows as the array argument. When every value in the include array is FALSE, FILTER has no rows to return and produces an empty array. In Excel 365 and Excel 2021, this empty array displays as the #CALC! error. In older versions that support dynamic arrays, it may show as a blank cell or spill error.

The most common root causes for an all-FALSE include array are:

• Incorrect logical test: Using a single equal sign instead of double equal, or using the wrong comparison operator.
• Data type mismatch: Comparing numbers to text strings, or dates to text that looks like dates.
• Hidden or extra characters: Leading spaces, trailing spaces, or non-printable characters in the source data.
• Spilled range issues: The include argument references a range that does not align with the array argument.

Steps to Identify and Fix the Empty Array Problem

1. Verify the include argument range size
   Select the cell with the FILTER formula. Press F2 to enter edit mode. Highlight the include argument (the second argument inside FILTER). Press F9 to evaluate that part of the formula. If the result is {FALSE;FALSE;FALSE;...}, then all rows failed the test. Press Escape to exit edit mode without saving the change.
2. Test the logical condition on a single cell
   In a blank column, enter a formula that replicates the logical test for the first data row. For example, if your FILTER uses A2:A100="Product X", enter =A2="Product X" in cell Z2. Drag down to see which rows return TRUE. If none return TRUE, the condition itself is wrong or the data does not contain that value.
3. Check for leading and trailing spaces
   In a helper column, use =TRIM(A2) and compare the result to the original cell. If they differ, the original cell contains extra spaces. Use =TRIM() inside the FILTER formula: FILTER(array, TRIM(A2:A100)="Product X").
4. Convert text numbers to actual numbers
   If you are comparing a numeric column, ensure the source cells are numbers, not text. Use the VALUE function: FILTER(array, VALUE(B2:B100)>100). Alternatively, select the column, go to Data > Text to Columns, and click Finish to convert text to numbers.
5. Use the if_empty argument to see a friendly message
   Add a third argument to FILTER: FILTER(array, include, "No matching records"). This replaces the #CALC! error with your custom text, confirming that the formula works but found no matches.
6. Check for hidden rows or filters
   If your source data has AutoFilter applied, FILTER still evaluates all rows regardless of visibility. But if you manually hid rows, FILTER sees them. Remove any filters on the source range to ensure you see all data.

If FILTER Still Returns an Empty Array After the Main Fix

FILTER returns #CALC! even though the include argument shows TRUE values

This usually happens when the include argument references a spilled range that is not fully calculated. For example, if include is D2# (a spilled range), and the spill formula has not yet spilled or has an error, FILTER sees an empty include array. Recalculate the workbook by pressing Ctrl+Alt+F9. If the spill formula returns an error, fix that error first.

FILTER works in one workbook but not in another

The FILTER function requires Excel 365 or Excel 2021. If you open the workbook in Excel 2019 or earlier, the formula returns a #NAME? error or an empty array. Check the Excel version: go to File > Account > About Excel. The version must be 2016 or later with a Microsoft 365 subscription, or Excel 2021 standalone.

FILTER returns empty when the source data is in a Table

Excel Tables use structured references like Table1[Column1]. If the Table column name contains a space, you must enclose it in brackets: Table1[Column Name]. A missing bracket causes the include argument to evaluate to all FALSE. Verify the structured reference syntax by typing =Table1[ and letting IntelliSense suggest column names.

FILTER with Multiple Conditions: Expected Behavior vs Empty Array

Condition Type	Correct Syntax	Why It Returns Empty
AND (both must be true)	FILTER(array, (range1="A")(range2="B"))	If either range has no matching value, the product is 0 (FALSE) for all rows
OR (either can be true)	FILTER(array, (range1="A")+(range2="B"))	If neither range has any matching value, the sum is 0 for all rows
Comparison with cell reference	FILTER(array, range1=$E$1)	If cell E1 is empty or contains a different data type, no rows match

When combining conditions with multiplication (AND) or addition (OR), enclose each condition in parentheses. Without parentheses, Excel may interpret the operators incorrectly and produce an all-FALSE array.

You can now diagnose and resolve empty array results from FILTER by checking the logical test, data types, and formula syntax. Start by evaluating the include argument with F9, then test the condition on a single column. Add the if_empty argument to distinguish between a broken formula and a genuine lack of matching data. For advanced troubleshooting, use the Evaluate Formula tool on the Formulas tab to step through each calculation.