How to Fix Excel FILTER Function Returning Zeros Instead of Blanks
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.

- 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.
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.
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.
Click on the top-left cell where your existing FILTER formula is written and where the array spills from.
Wrap your existing formula inside an IF function. Structure it as follows: =IF(FILTER(range, criteria)=0, "", FILTER(range, criteria)).
Press Enter to execute the updated formula. The spilled array will instantly update to show blank spaces wherever the source cells are empty.
Apply Custom Number Formatting
Change the visual formatting of the cells to hide all zero values without altering the underlying FILTER formula.
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. Open a new spreadsheet: Launch WPS Office and open your workbook in the WPS Spreadsheet application.
- 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. Execute the formula: Press Enter to instantly generate a clean, zero-free spilled array.

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.




