Function Problems

How to Use Excel FILTER Formula to Preserve True Zero Values

John Wilson Sep 25, 2026 872 views

Question details

The user needs to filter a dataset using the FILTER function without displaying blank cells as zeros, while ensuring actual zero values are preserved.

How to Use Excel FILTER Formula to Preserve True Zero Values

Product: Excel
Device & OS: not provided
Scenario: Using the FILTER function to extract data that contains both empty cells and genuine zero values.
Observed behavior: The standard FILTER function converts empty source cells into zeros in the output array, making it difficult to distinguish them from genuine zero values.

Before you start

Ensure you are using a spreadsheet version that supports Dynamic Array functions like FILTER and LET, such as Office 365, Excel 2021, or the latest version of WPS Office.

Solution 1Recommended

Use a Combination of LET, FILTER, and IF Functions

This method prevents empty cells from showing as zeros by storing the filtered data in a LET variable and evaluating it with an IF statement.

By wrapping the FILTER function inside a LET function, you assign the result to a variable. The IF function then checks if the returned cell is blank and outputs an empty string if true. If the cell contains data, including a true zero, it outputs the actual value.

Select the target cell

Click on the top-left cell where you want the dynamic array of filtered results to start.

Enter the formula

Type the formula: =LET(f,FILTER(Table1,Table1[Custom - Question pod group]=A3,""),IF(f="","",f)) into the formula bar. Be sure to replace 'Table1' and the criteria array with your specific dataset ranges.

Apply and check the results

Press Enter to apply the formula. The results will spill into the adjacent cells, accurately displaying true zeros while keeping empty cells completely blank.

Use a Combination of LET, FILTER, and IF Functions

Understanding the LET Function: Using LET(f, ...) assigns your FILTER formula to the variable 'f'. This makes the formula process faster and easier to read compared to writing the entire FILTER formula twice within a standard IF statement.

Advanced Spreadsheets in WPS Office

Filter and Process Data Seamlessly with WPS Office

WPS Office Spreadsheets provides complete support for advanced dynamic array functions, including FILTER, LET, and IF. You can efficiently manage complex data filtering tasks while ensuring 100% compatibility with Microsoft Excel files.

1. Download and Install: Download WPS Office for free from the official website and install it on your computer.
2. Open your dataset: Launch WPS Spreadsheets and open your .xlsx workbook containing the data you need to filter.
3. Apply the dynamic formula: Select your output cell and input the combined LET and FILTER formula to cleanly extract your data while preserving true zero values.

Fully supports dynamic array formulas like FILTER, LET, and IF.Seamlessly opens, edits, and saves Microsoft Excel (.xlsx) files with zero formatting loss.Lightweight application that runs smoothly on Windows, Mac, and Linux.Free alternative offering a familiar interface for quick transition.

microsoft office alternative - wps office

Frequently Asked Questions

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

By default, when Excel evaluates an empty cell referenced in a formula or an array output, it treats the empty value as a zero. Using an IF function to explicitly check for blanks overrides this default behavior.

Do I have to use the LET function for this to work?

No, but it is highly recommended for efficiency. Without LET, you would write the formula as =IF(FILTER(...)="","",FILTER(...)). The LET function prevents Excel from calculating the FILTER function twice, speeding up your workbook.

Are true zeros affected if I change the formatting of the cell?

Cell formatting changes how data is visually displayed, but the underlying value remains the same. The formula ensures the actual 0 value is passed through, so it will respond correctly to any custom number formatting you apply.

Excel & Data Problems