Function Problems

How to Remove Zero Results from an Excel Formula

Bushra Parveen Sep 27, 2026 869 views

Question details

The user wants to subtract two arrays of data and completely filter out any zero results from the spilled array, preventing blanks or zeros from appearing in the output.

How to Remove Zero Results from an Excel Formula

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using dynamic arrays to calculate subtraction and instantly filtering the output so that only non-zero results are displayed.
Observed behavior: When applying standard array subtraction, the spilled results include unwanted blank cells or zero values instead of a clean, condensed list.

Before you start

Ensure you are using a version of Excel that supports Dynamic Arrays, such as Excel 365 or Excel 2021, as older versions do not include the FILTER function.

Solution 1Recommended

Use the FILTER Function to Exclude Zeroes

By wrapping your calculation inside a FILTER function, you can dynamically evaluate the results and exclude any outputs that equal zero.

The FILTER function is designed to extract data based on boolean conditions. By setting your calculation as both the array to be filtered and the logical test criteria, it will instantly remove any calculated zeros from your final spilled output.

Select the target cell

Click on the empty cell where you want the results of your calculation to begin spilling (for example, cell D1).

Enter the FILTER formula

Type the formula =FILTER(A1:A4-B1:B4, (A1:A4-B1:B4)<>0). This tells Excel to subtract column B from column A, and then check those exact results to ensure they do not equal zero.

Press Enter to execute

Hit Enter on your keyboard. The formula will calculate the differences and dynamically spill down the column, omitting any zero values.

Use the FILTER Function to Exclude Zeroes

Applying Multiple Conditions: If you need to apply additional conditions, multiply them inside the include argument. For example: =FILTER(A1:A4-B1:B4, ((A1:A4-B1:B4)<>0) * (A1:A4>5)).

Advanced Spreadsheet Tool

Use WPS Spreadsheet to Filter Formula Results Effortlessly

WPS Office features a robust, free Spreadsheet application that natively supports advanced dynamic array formulas. You can use functions like FILTER to instantly analyze data and remove unwanted zero results just as you would in other major spreadsheet software.

1. Open WPS Spreadsheet: Launch WPS Office on your computer and open your workbook.
2. Locate the target cell: Select the cell where you want the final, filtered calculations to appear.
3. Input the array formula: Type =FILTER(A1:A4-B1:B4, (A1:A4-B1:B4)<>0) into the formula bar.
4. View the filtered results: Press Enter. WPS Spreadsheet will calculate the differences and dynamically spill the array, automatically stripping out any zero values.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx file formats.Natively supports modern dynamic array functions including FILTER, SORT, and UNIQUE.Incredibly lightweight and operates smoothly across Windows, Mac, and Linux systems.Features a familiar, easy-to-navigate user interface requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER formula return a #CALC! error?

The #CALC! error typically appears when the FILTER function finds no matching results—for instance, if every single subtraction calculation results in zero. You can prevent this error by adding the optional [if_empty] argument at the end of your formula, like this: =FILTER(A1:A4-B1:B4, (A1:A4-B1:B4)<>0, "No matches").

Is the FILTER function available in older versions of Excel?

No, the FILTER function relies on the dynamic array engine introduced in Excel 365 and Excel 2021. If you are using Excel 2019 or older, you will need to rely on complex INDEX and AGGREGATE array formulas or VBA scripting to remove zero values.

How can I filter out empty blank cells instead of zeroes?

To filter out blank cells, you need to check for empty strings instead of numerical zeroes. Change the logic in your include argument to not equal double quotes, such as =FILTER(A1:A10, A1:A10<>"").

Excel & Data Problems