How to Exclude Zero Values with Formula in Excel (3 Easy Ways)

Last updated: Apr 19, 2024

Method 1 – Use Formula with IF Function to Exclude Zero Values in Excel

Let’s say, we have a dataset displayed in the (B4:D8) cells. Here, the dataset contains 3 columns named Order 1, Order 2, and Result. The dataset has a title called Using IF Function. In the column titled Result, we can see two zero values in cells D6 and D8. Now, we need to omit them.

Steps:

Note that cells in Column D are the sums of the cells in Columns B and C.
After selecting cell D6, the Formula Bar is showing the below formula:

=B6+C6

Select cell D5 and type this formula instead:

=IF(B5+C5=0,” “,B5+C5)

Press the Enter button.
To copy the results in the other cells, use the Fill Handle.

How Does the Formula Work?

B5+C5:

This formula sums up the values of B5 and C5 cells.

IF(B5+C5=0,” “,B5+C5):

Here, the IF function will return a void string (“ “) when the summation (B5+C5) is 0. On the other hand, it will return the non-zero result (B5+C5) if the summation is not 0.

We can see that the zero values in the D6 and D8 cells are excluded successfully.

Method 2 – Ignore Zero Values with AVERAGEIF Function

Suppose we have a dataset (B5:C9). It contains the numbers and Months of some Orders of a product. Here, the C9 cell shows the average values of the Orders. We can see in the Formula Bar that the AVERAGE function has been used to do so.

Steps:

Select cell C9 and type the formula:

=AVERAGEIF(C5:C8,”<>0”)

Hit the Enter key.
We can see the result in cell C9 now excludes the zero values.

Method 3 – Avoid Zero Values Using Excel FILTER Function

The FILTER function is available on Excel 365, mobile versions, Excel 2019 and newer on Windows, and Excel 2021 and newer on Mac.

Assuming, we have a dataset (B5:D8) that contains a heading named Month, Orders and Filtered Values. The dataset contains zero values in cells C6 and C8.