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.


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

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 Create a Custom Formula in Excel 


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”).


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.


Download Practice Workbook

Download the practice workbook from here.


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.


Related Articles


<< Go Back to How to Create Excel Formulas | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sagufta Tarannum
Sagufta Tarannum

Sagufta Tarannum, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, contributes significantly as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep interest in research and innovation, she actively engages with Excel. In her role, Sagufta not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, underscoring her unwavering commitment to consistently delivering exceptional content. Her interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo