Author: Oscar Cronquist Article last updated on December 13, 2017

The AVERAGE function is designed to ignore blank cells but there are instances where it fails. The picture above seems to have blank cells in B3:B8, however, they are counted as zeros. Why?

The cells in B3:B8 are not truly empty, select B3:B8 and press CTRL + 1 to open "Format Cells" settings.

Here we see that it has been formatted with a custom cell formatting. Values containing 0 (zeros) are hidden, to show them press with left mouse button on General and then OK.

If you are interested in learning more about custom formatting codes, read this article: Excel’s TEXT function explained

This will show your zeros again on your worksheet.

If this didn't do it, go to your Excel Options and then press with left mouse button on "Advanced".

Excel 2016 Users press with left mouse button on "File" on the ribbon and then press with left mouse button on "Options".

Find "Show a zero in cells that have zero value", is it enabled?