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

We can exclude the zero values if our Excel sheet contains them or contains calculations that result in zero values. Sometimes, we may prefer to hide zero values in a cell for personal reasons. Whereas, sometimes we may be working with a spreadsheet that requires us to hide zero values. There are several ways to do so. In this article, we are going to exclude zero values with the formula in Excel.


Download Practice Workbook

Download the practice workbook from here.


3 Easy Ways to Exclude Zero Values with Formula in Excel

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 successively. 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. In this method, we are going to use the IF function to do so. The IF function enables us to make logical comparisons between a value and our expectations.

Use Formula with IF Function to Exclude Zero Values in Excel

Steps:

  • Firstly, we can see that cells in Column D are the summation of the cells in Columns B and C.
  • Now, for example, after selecting cell D6 the Formula Bar is showing the below formula:
=B6+C6

Use Formula with IF Function to Exclude Zero Values in Excel

  • Next, select cell D5 and type the formula:
=IF(B5+C5=0,” β€œ,B5+C5)

Use Formula with IF Function to Exclude Zero Values in Excel

  • After that, press the Enter button.
  • Finally, to see the results in the following 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.

  • In turn, we can see that the zero values in the D6 and D8 cells are excluded successfully.

Read More: How to Use VLOOKUP to Return Blank Instead of 0 (7 Ways)


Similar Readings


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.

=AVERAGE(C5:C8)

Ignore Zero Values with AVERAGEIF Function

Here, the zero values of cells C6 and C8 are also included to calculate the average. Now, we can ignore them easily by using the AVERAGEIF function. The AVERAGEIF function in Excel computes the average of numbers in a range that meets specified criteria. Steps are given below:

Steps:

  • Firstly, select cell C9 and type the formula:
=AVERAGEIF(C5:C8,”<>0”)
  • Secondly, hit the Enter key.
  • In the end, we can see the result in cell C9 excluding the zero values.

Here, the AVERAGEIF function gives the average of the Orders (C5:C8) neglecting the zero values (β€œ<>0”).

Read More: How to Apply VLOOKUP to Return Blank Instead of 0 or NA


3. Avoid Zero Values Using Excel FILTER Function

Assuming, we have a dataset (B5:D8) that contains a heading named Month, Orders and Filtered Values. The title of our dataset is Applying Filter Function. The dataset contains zero values in cells C6 and C8. The FILTER function filters a set of data based on the specified criteria and extracts matching records in Excel. Now, we can use the Filter function to omit the zero values.

Avoid Zero Values Using Excel FILTER Function

Steps:

  • In the beginning, select cell D5.
  • Next, type the formula:
=FILTER(C5:C8, C5:C8<>0)
  • After that, hit Enter.

Avoid Zero Values Using Excel FILTER Function

  • Lastly, we can see that the zero values are omitted in column D.

Here, the Filter function avoids the zero values of cells C6 and C8.

Read More: How to Hide Zero Data Labels in Excel Chart (4 Easy Ways)


Conclusion

I hope the above methods will be helpful for you to exclude zero values with the formula in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.


Related Articles

Sagufta Tarannum

Sagufta Tarannum

Hi, I am Sagufta. I have completed graduation in Civil Engineering from BUET. I am from Bogura. Currently I am living in Mirpur. I am very interested about research and innovation in the field of Civil Engineering

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo