How to Calculate Average Ignore Blank and Zero Cells in Excel

Sometimes we need to calculate the average for a list or a range of numbers, but blank cells and zero cells may be also included in the selected range, so if these invalid cells are included, we often get error when calculating the average.

This post will guide you on how to calculate average while ignoring blank and zero cells in Excel. By default, Excel’s AVERAGE function includes all cells in the specified range, including blank cells and cells that contain zero values. However, you can use formulas such as AVERAGEIF or AVERAGEIFS to exclude these cells from the calculation.

1. Video: Calculate Average Ignore Blank and Zero Cells in Excel

In this video, you’ll learn how to use the AVERAGEIF formula to calculate the average while ignoring blank or zero cells in Excel.

2. Average Cells in Excel

If you only want to average a range of cell in Excel, and you can use the AVERAGE function. Just type the following formula:

=AVERAGE(A1:C5)
How to Calculate Average Ignore Blank and Zero Cells in Excel 11.png

This formula will calculate the average of all the cells in the given range, including  blank cells and cells that contain zero values.

3. Calculate Average Ignore Blank and Zero Cells by Formula

Precondition:

Prepare below table. There are some zero cells and blank cells. We will ignore them when calculating the average.

How to Calculate Average Ignore Blank and Zero Cells in Excel1.png

This is the most convenient way to calculate the average for selected range without zero and blank cells. You can use the AVERAGEIF function with a criteria that excludes blank and zero cells. Just do the following steps:

Step 1: Select a blank cell where you want to display the last average result, for example E2.

Step2: Enter the following formula in the formula bar.

 =AVERAGEIF(A1:C5,"<>0").
How to Calculate Average Ignore Blank and Zero Cells in Excel2.png

Step 3: Press Enter to get the average. Verify that average is calculated properly.

How to Calculate Average Ignore Blank and Zero Cells in Excel3.png

This formula will exclude blank cells and cells that contain a zero value from the calculation.

4. Calculate Average Ignore Blank Only

Excel can calculate the average ignore the blank cells automatically. Zero numbers are still included. You can use the AVERAGEIF function with the criteria “<>” or AVERAGE function only to exclude blank cells.

Type one of the below formula in a blank cell where you want to display the average result:

=AVERAGE(A1:C5)

Or

=AVERAGEIF(A1:C5,"<>")
How to Calculate Average Ignore Blank and Zero Cells in Excel 10.png

You may think if we remove all zero values and left them as blank cells, then we can use AVERAGE function directly without other index or parameter. Well, if you remove them totally, you can apply AVERAGE function directly, but, if you just make these zero numbers invisible in the table, you may get improper value by formula.

See example below:

Step1: Click File (it is adjacent to Home) in the ribbon, then select Options to enter Excel Options window.

How to Calculate Average Ignore Blank and Zero Cells in Excel5.png

Step2: On Excel Options window, in the left panel, click Advanced, then drag the scrollbar to the middle, under Display options for this worksheet, uncheck ‘Show a zero in cells that have zero value’ option. Then click OK.

How to Calculate Average Ignore Blank and Zero Cells in Excel6.png

Step3: Then you will find that zero value is ‘removed’ from your table. Only blank cells exist now.

How to Calculate Average Ignore Blank and Zero Cells in Excel7.png

Step4: In E2 enter the formula =AVERAGE(A1:C5). Then press Enter to check the result.

How to Calculate Average Ignore Blank and Zero Cells in Excel8.png

See, we still get the average with zero included. So, you cannot apply AVERAGE function without zero cells by disable showing them in the table.

5. Ignore or Hide Blank Cells in Excel Pivot Table

You can also filter out the blank cells or rows in a pivot table by using the filter options.

ignore blank cells in pivot table1.png

You just need to click on the drop-down arrow next to the column or row header that you want to filter, and then uncheck the “(blank)” option in the filter menu.

ignore blank cells in pivot table2.png

This will hide all the cells or rows that have blank values in that field.

ignore blank cells in pivot table3.png

If you want to filter out multiple columns or rows, you can use the “Filter” or ” Insert Slicer” buttons on the ribbon to apply more advanced filters to your pivot table.

6. Conclusion

In your daily work, excluding blank and zero cells when calculating averages is an very useful task that can save you time and provide more accurate results by the above steps in my article.

Just enjoy it.