Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to average ignoring blank cells in Excel and Google Sheets
Topic:AVERAGE & central tendencyExcel & Google Sheets
=AVERAGE(B2:B6)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Month | Revenue |
| Jan | 500 |
| Feb | |
| Mar | 700 |
| Apr | |
| May | 600 |
=AVERAGE(B2:B6)→600
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Use =AVERAGE(B2:B6) — blank cells are automatically excluded, so no special handling is needed.
- 2If some blank-looking cells contain placeholder text (dashes, 'N/A', etc.), use =AVERAGEIF(B2:B6,"<>") to count only non-empty cells.
- 3For mixed ranges with zeros you want to keep, AVERAGE is sufficient — it includes zeros but not blanks.
- 4Press Enter to confirm the formula.
Tips
- AVERAGE ignores blanks and text by default — no special formula is usually required.
- Use AVERAGEIF(range,"<>") if non-numeric placeholders exist in the range.
- AVERAGEA counts blank cells as 0 and should be avoided when blanks mean 'no data'.
- Check for cells formatted as text containing numbers — AVERAGE may skip them. Use VALUE() to convert.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
AVERAGE is returning a higher count than expected — why?
Some visually blank cells may contain spaces or formulas returning empty strings, which AVERAGE skips. Cells with zero are included and lower the average. Audit with COUNTA.
How do I average only cells with actual numbers entered?
=AVERAGE(B2:B6) already does this. Only numeric cell values are included.
More on AVERAGE & central tendency
See all →How-to
How to calculate an average
Use the AVERAGE function to calculate the arithmetic mean of a range of numbers in Excel or Google Sheets.
How-toHow to average ignoring zeros
Use AVERAGEIF with a "<>0" criterion to calculate an average that excludes zero values from the calculation.
How-toHow to average with a condition
Use AVERAGEIF to compute the mean of values in one column where a corresponding column meets a specified criterion.
How-toHow to calculate a moving average
Compute a rolling average for each row using AVERAGE with a fixed-window OFFSET or INDEX formula anchored to each row.
How-toHow to find the median of a range
Use the MEDIAN function to find the middle value in a dataset — the value that half the data falls above and half below.
How-toHow to find the most common value
Use MODE for numbers or COUNTIF-based logic for text to find the value that appears most frequently in a dataset.