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.

**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 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)`

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.

**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**.

**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

- How to Use Multiple Excel Formulas in One Cell
- How to Apply Formula to Entire Column Without Dragging in Excel
- How to Apply a Formula to Multiple Sheets in Excel
- How to Make FOR Loop in Excel Using Formula
- How to Apply Formula to Entire Column Using Excel VBA
- How to Use Point and Click Method in Excel
- How to Apply Same Formula to Multiple Cells in Excel

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