How to Average a Range in Excel and Ignore Zero (0)

Averaging a range of numbers in Excel is all about using the AVERAGE function. Simple enough.

However, if your range of numbers contains undesired zeros that you do not want to be included in the average – this is not as simple as calculating a simple average.

Fun fact, it is not even as difficult as you just thought 🧐

Extracting and importing data from external sources can often leave your data cluttered with unnecessary zeros. To learn how to ignore these zeros while you average a range out in Microsoft Excel, read this guide till the end.

Do not forget to grab your free practice workbook on your way down.

Calculating average in Excel ignoring zeros

You can calculate the average of any range in Excel making sure the zeros are ignored by using the AVERAGEIF function of Excel.

Here I have a range of numbers in Excel that has some zero values included too 🔢

range of cells with zeros

To calculate the average for these numbers by ignoring the zeros:

Step 1) Write the AVERAGEIF function as follows;

Click to copy
writing the AVERAGEIF function

Step 2) As the first argument, supply the range of numbers to be averaged out.

Click to copy
Range of numbers

Step 3) As the criteria argument, write the criteria as not equivalent to zero.

Click to copy
Criteria for average

The inequality sign (<>) looks like two “less than <” and “greater than >” symbols facing each other. Enclose the not equal to zero criteria in double quotation marks as “<>0”.

Kasper Langmann, co-founder of Spreadsheeto

This tells Excel to average only those cells from this range that are not equivalent to zero.

Step 4) Hit the Enter button to get the results.

Average range including zeros

Comes out as 6.2. This was easy, right? ✌

But hold on, how do we know if this is average and has accurately ignored the zeros of the range? Let’s double-check it for accuracy.

Step 5) Write the AVERAGE function

Click to copy

Step 6) Input the numbers of be averaged manually by leaving out the zeros of this range as follows.

Click to copy
Excel formulas open syntax

I have not included Cell references A2 and A7 in the range to be averaged.

Step 7) Press enter to see the results.

Excel Average formula to return average cells

Excel returns the average value as 6.2 again. Tells that you can rely on the AVERAGEIF function with any criteria of your choice 🎯

If you want to supply multiple criteria (other than only ignoring zeros) that you want followed, use AVERAGEIFS function instead.

Kasper Langmann, co-founder of Spreadsheeto


You must have used the SUMIF, COUNTIF, and other IF-based functions of Excel, but the AVERAGEIF function is rarely used.

The above tutorial aims to teach you how to effortlessly deal with the zeros in your dataset to obtain accurate averages. With this, why don’t you go through my other Excel guides that better explain the use of the IF function of Excel?