Sagufta Tarannum
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.
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
- Next, select cell D5 and type the formula:
=IF(B5+C5=0,” “,B5+C5)
- 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
- Excel IFERROR Function to Return Blank Instead of 0
- How to Hide Rows with Zero Values in Excel Using Macro (3 Ways)
- Hide Chart Series with No Data in Excel (4 Easy Methods)
- How to Hide Zero Values in Excel Pivot Table (3 Easy Methods)
- How to Perform If Zero Leave Blank Formula in Excel (3 Methods)
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)
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.
Steps:
- In the beginning, select cell D5.
- Next, type the formula:
=FILTER(C5:C8, C5:C8<>0)- After that, hit Enter.
- 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.
