Formula Errors

How to Fix Excel FILTER Function Returning Zeros Instead of Blanks

Emma BrownEmma Brown Oct 29, 2026 871 views

Question details

The user needs to prevent the FILTER function from displaying zeros when pulling data from empty source cells, intending to display true blank cells in the spilled array instead.

How to Fix Excel FILTER Function Returning Zeros Instead of Blanks
Product
Microsoft Excel
Device & OS
not provided
Scenario
Filtering a dataset where some cells in the source range are empty, causing the resulting spilled array to populate those empty spots with '0'.
Observed behavior
The FILTER formula outputs a '0' for any blank cells in the referenced source range rather than leaving the output cell blank.
Before you start

Verify that the zeros appearing in your filtered results are caused by empty source cells, rather than actual zero values intentionally entered into your original dataset.

Solution 1Recommended

Wrap the FILTER Formula in an IF Function

Use an IF statement to evaluate the output of the FILTER function. If the output is zero, instruct Excel to return an empty string instead.

This is the most direct approach to intercepting the zero values generated by Excel's calculation engine when evaluating empty source cells.

1
Select the target cell

Click on the top-left cell where your existing FILTER formula is written and where the array spills from.

2
Modify the formula

Wrap your existing formula inside an IF function. Structure it as follows: =IF(FILTER(range, criteria)=0, "", FILTER(range, criteria)).

3
Apply the changes

Press Enter to execute the updated formula. The spilled array will instantly update to show blank spaces wherever the source cells are empty.

Hiding Legitimate Zeros: This formula logic treats all zeros as blanks. Consequently, it will also hide actual zero values present in your source data. If your data contains meaningful zeros, consider alternative methods like custom number formatting.
Advanced Data Filtering

Easily Filter and Manage Data with WPS Office

WPS Spreadsheet fully supports advanced array functions like FILTER, IF, and custom number formatting. Use WPS Office to manage, filter, and format your datasets with zero hassle.

  1. 1. Open a new spreadsheet: Launch WPS Office and open your workbook in the WPS Spreadsheet application.
  2. 2. Input the IF and FILTER combination: Select the cell for your spilled array and type =IF(FILTER(A1:B10, A1:A10="Criteria")="","",FILTER(A1:B10, A1:A10="Criteria")).
  3. 3. Execute the formula: Press Enter to instantly generate a clean, zero-free spilled array.
Fully compatible with Microsoft Excel formulas and array functions.Easily handle empty cells and complex data filtering natively.Lightweight software with a familiar, user-friendly interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the Excel FILTER function return zeros for blank cells?

Excel's calculation engine evaluates referenced empty cells as numeric zeros by default. When the FILTER function pulls data from an empty cell, it passes that evaluated zero to the output rather than a blank string.

Can I use the LET function to make this formula shorter?

Yes. If you are using Microsoft 365 or a recent version of WPS Office, you can use the LET function to define the FILTER formula as a variable. Use =LET(f, FILTER(range, criteria), IF(f="","",f)) to avoid calculating the same FILTER formula twice.

How can I hide zeros but keep legitimate zero values in my FILTER results?

Instead of checking if the output equals 0, you can append an empty string to your FILTER function output like this: =FILTER(range, criteria)&"". Be aware that this converts all resulting numbers into text format, which may impact subsequent mathematical calculations.