How to average for cells with values only (exclude 0 or blank cells) in Excel?
Average function is quite useful for our daily works in Excel. This article will talk about how to calculate the average for cells with values only (excluding blank cells) in Excel easily.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Average with Ignoring/excluding blank cells with formula
The Average function will ignore the truly blank cells automatically. However, if you have disabled the option of Show a zero in cells that have zero value in Excel Options dialog box (clicking File > Options > Advanced > Display options for this worksheet), some blank cells may have zero values in Excel. In this case, the Average function will not exclude these blank cells with zero values. See below screen shot:
Actually we can exclude the cells with zeros to solve this problem. For example, you want to average the Range A1:D13 with ignoring/excluding blank cells and zeros, you can apply the formula =AVERAGEIF(A1:D13,"<>0") as below screenshot shown:
Microsoft Excel can automatically average selected cells and show calculated results in the Status bar. If we can select only nonblank cells in a range, we can get the average of this range excluding blank cells. Kutools for Excel's Select Nonblank Cells utility can help you get it done at once.
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
Select the range where you want to average excluding blank cells, and click the Kutools > Select > Select Un blank Cells.
Now only cells with values are selected in the specified range, and you will get the average excluding blank cells in the Status bar as below screenshot shown: