Average Only Non-Blank Cells in Excel (Formula)

Last Updated: March 24, 2024

- Written by Puneet

To average only non-blank cells in Excel, you need to use the AVERAGEIF function. And in the criteria, you need to use does not equal operator to refer to only non-blank cells. In this tutorial, we will look at this formula in depth.

Average Only Cells which are Not Blank

You can use the following steps:

  1. First, in cell C1, enter the AVERAGEIF function.
  2. After that, in the first argument (range) refer to the range A1:A9.
  3. Now, in the second argument, enter does not equal the operator using double quotation marks.
  4. In the end, enter the closing parentheses and hit enter to get the result.

When you use it, AVERAGEIF only considers cells that are non-blank and averages only those cells. You can see when I select the cells where we have value it shows the same average on the status bar.


A Problem You Might Face

There’s might be a situation where you have a blank cell in the range and that blank cell have an invisible space. In this case, your formula won’t consider that cell as a blank and calculate the average including it as well.

Well, you can use the below formula where we have used AVERAGEIF to specify two criteria, one is to include non-blank cells and the other is to ignore cells with a space.

=AVERAGEIFS(A1:A9,A1:A9,"<>",A1:A9,"<>"&" ")

Get the Excel File