Function Problems

How to Fix Excel FILTER Function Returning Zeros for Empty Cells

Kushani NimanthikaKushani Nimanthika Oct 1, 2026 878 views

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.

How to Stop Excel FILTER Function from Returning Zeros for Empty Cells
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the formula cell

Click on the top-left cell where your current FILTER function is entered to open the formula bar.

2
Add the exclusion criteria

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

3
Apply the new formula

Press Enter. The dynamic array will recalculate and spill the updated results, now completely excluding the blank rows.

Exclude Blank Rows with an Additional Condition
Best Practice: This is the most reliable method for mathematical and data accuracy, as it removes the blank data entirely rather than just masking the zeros.
Powerful Spreadsheet Alternative

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. 1. Open data in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet document containing the source data.
  2. 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. 3. Enter the FILTER function: In a new cell, type '=FILTER(' and select your structured table data as the array argument.
  4. 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.
Fully compatible with Microsoft Excel formulas, functions, and dynamic arrays.Easily create and manage dynamic tables to prevent spill errors and zero-value bugs.Lightweight, fast, and free to use for everyday data analysis and formatting.Familiar user interface makes migrating from Microsoft Excel completely seamless.
microsoft office alternative - wps office

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.