How to Fix Excel FILTER Function Returning Zeros for Empty Cells
Question details
The user needs to prevent the Excel FILTER function from outputting zeros when it encounters empty cells within a data range, and wants to manage growing data without disrupting the spill range.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a dynamic or growing data range that includes blank rows using the FILTER function.
- Observed behavior
- The FILTER function returns a 0 for empty cells instead of leaving them blank, and adding new data below the formula causes unexpected changes to the spill range.
Verify that you are not referencing entire columns (e.g., A:A) in your formula, as this forces Excel to process over a million rows, leading to blank results being returned as zeros and increasing the risk of #SPILL! errors.
Exclude Blank Rows with an Additional Condition
Modify your FILTER function criteria to explicitly exclude blank cells, which prevents empty rows from being processed and returned as zeros.
In Excel array formulas, you can use the multiplication operator (*) to apply multiple criteria. By adding a condition that checks if a cell is not empty (<>""), the FILTER function will completely ignore blank rows.
Click on the top-left cell where your current FILTER function is entered to open the formula bar.
Modify the 'include' argument of your formula by wrapping your original criteria in parentheses and multiplying it by the non-blank condition. For example: =FILTER(A2:C100, (B2:B100="Target") * (A2:A100<>""))
Press Enter. The dynamic array will recalculate and spill the updated results, now completely excluding the blank rows.

Format the Source Data as an Excel Table
Use an Excel Table instead of referencing a standard range. Tables automatically expand as you add data, so you don't need to select empty rows 'just in case' the data grows.
Append an Empty String to Mask Zeros
A quick formula modification to mask zeros by forcing the output to text format. This is best used when your filtered results contain purely text data.
Manage Dynamic Arrays Seamlessly in WPS Office
WPS Office Spreadsheet provides robust support for advanced dynamic array functions, including FILTER. You can manage growing data sets efficiently, create auto-expanding tables, and apply multi-condition filters without worrying about unexpected blank cell errors.
- 1. Open data in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet document containing the source data.
- 2. Format data as a Table: Select your source dataset and press Ctrl+T. This ensures your range will automatically expand when new data is added.
- 3. Enter the FILTER function: In a new cell, type '=FILTER(' and select your structured table data as the array argument.
- 4. Apply criteria and exclude blanks: Add your filtering conditions, multiply by a non-blank check like *(Table1[Column]<>""), and press Enter to generate clean, dynamic results.

Frequently Asked Questions
Why does Excel display a zero instead of a blank cell in array formulas?
By default, when an Excel formula references an empty cell, the calculation engine interprets the lack of a value as a zero. To display a true blank, you must explicitly instruct Excel to exclude the blanks from your criteria or format the output to hide zeros.
How do I fix a #SPILL! error when using the FILTER function?
A #SPILL! error occurs when the dynamic array cannot expand because cells below or to the right of the formula already contain data, text, or spaces. To resolve it, locate the highlighted spill area and clear any obstructing cells so the formula has enough room to display all results.
Can I filter out blanks based on multiple columns at once?
Yes. You can multiply multiple conditions together inside the FILTER function's 'include' argument. For example, using (A2:A100<>"") * (B2:B100<>"") ensures that a row is only included if both Column A and Column B are not empty.




