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.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.