Average Cells Excluding Zeros in Excel

Add to Favorites

Exclude zeros while averaging cells in Excel. This method removes all zeros from the equation.


Easy Method

Method 2 (Used for Excel 2003 and Older)


Easy Method

This method works for Excel 2007 and later versions, which probably includes you.


A1:A5 change this to the range that you want to average and that's it.

"<>0" is the part that tells the function to ignore cells that have zeros in them.




Method 2 (Used for Excel 2003 and Older)

If you have Excel 2003 or earlier, you must use this version of the formula.


Array Formula - this is an array formula so you must enter it using Ctrl + Shift + Enter.

A1:A5 is the range that you want to average; make sure to change it in both parts of the formula to work with your data.




If you don't enter this formula correctly, you will see 2.4 as a result.


Excel 2007 introduced a lot of helpful functions, including the AVERAGEIF() function. Things like this will make your life much easier so, if you can, upgrade to at least Excel 2007, or the latest version. The ribbon menu will be a pain to learn but functions like this make it worth it.

Make sure to download the sample file for this tutorial to work with this example in Excel.

Excel Function: AVERAGE(), AVERAGEIF(), IF()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Format Cells as Text in Excel
Macro: This free Excel macro formats a selection of cells as Text in Excel. This macro applies th...
Format Cells as Time in Excel
Macro: This free Excel macro formats a selection of cells in the Time format in Excel. This Time ...
How to Add Formatting to Cells and Data in Excel Styles, Fonts, Colors, & More
Tutorial: In this tutorial I will cover how to use the various formatting tools in Excel. The Format...
Allow Only Certain People to Edit Specific Cells in Excel
Tutorial: How to allow only certain people to edit certain cells or ranges in Excel. This is a sec...
Quickly Clear all Formatting in Excel
Tutorial: How to remove all formatting from cells at once in Excel. This includes removing any and...
Average the Visible Rows in a Filtered List in Excel
Tutorial: Average the results from a filtered list in Excel. This method averages only the visible ...