Most Excel trainers say 'AVERAGE ignores blanks' like it’s gospel. They’re half-right—and that half is dangerous. I watched a finance analyst at Alibaba Hangzhou accidentally inflate Q1 revenue per user by 17% because she assumed AVERAGE(B2:B20) ignored all empty-looking cells. It didn’t. It ignored truly blank cells—but not cells with "" from formulas, not cells with a single space, and definitely not filtered rows. That mistake cost two days of reconciliation.
The Problem
You paste sales data into Excel. Some reps haven’t submitted numbers yet. Others entered zero. Some used =IF(ISBLANK(C5),"",C5) to hide zeros—and now those cells look empty but aren’t. You type =AVERAGE(D2:D12). You get $38,420. Your gut says that’s too high. You’re right.
| Rep Name | Q1 Sales ($) | Notes |
|---|---|---|
| Sarah Chen | 42,500 | Submitted |
| James Wu | Blank cell (truly empty) | |
| Priya Patel | 0 | No sales |
| Diego Morales | "" | Formula result: =IF(E6="","",E6) |
| Lena Kim | Single space (manually typed) | |
| Tariq Hassan | 39,100 | Submitted |
| Anya Petrova | #N/A | Error — not counted |
| Marcus Bell | 45,200 | Submitted |
| Yuki Tanaka | " " | Two spaces in quotes |
| Rafael Diaz | 36,750 | Submitted |
| Zara Liu | #DIV/0! | Error — not counted |
AVERAGE(D2:D12) returns $38,420. But only 6 of those 11 cells contain valid numbers. The rest? One true blank (James Wu), one zero (Priya), one formula-blank (Diego), one space (Lena), two quoted spaces (Yuki), and two errors. AVERAGE counted the zero—and ignored everything else. That’s why the average feels inflated. It’s counting a $0 sale as real data, while pretending other gaps don’t exist.
The Solution
You need precision—not assumption. Here’s how to verify and fix it in under 90 seconds:
- Select your range (e.g., D2:D12)
- Press Ctrl + G → Special → choose Blanks → click OK. Excel selects only truly empty cells (like James Wu’s). Note how Diego’s "" and Lena’s space aren’t selected.
- To calculate an average that excludes zeros and formula-blanks, use:
=AVERAGEIFS(D2:D12,D2:D12,"<>0",D2:D12,"<>"). This reads: “Average D2:D12 where values are not zero AND not empty.” - For filtered data (say, only active reps), replace AVERAGE with
=SUBTOTAL(101,D2:D12). The 101 tells Excel to use AVERAGE and ignore hidden rows.
After applying =AVERAGEIFS(D2:D12,D2:D12,"<>0",D2:D12,"<>"), the result drops to $40,887—because it now excludes Priya’s $0 and treats Diego’s "" and Lena’s space as non-numeric (they’re excluded automatically by AVERAGEIFS’ logic).
| Metric | Value | What’s Included |
|---|---|---|
| =AVERAGE(D2:D12) | $38,420 | Zeros, ignores blanks & errors |
| =AVERAGEIFS(...) | $40,887 | Excludes zeros AND non-numeric blanks |
| =SUBTOTAL(101,D2:D12) | $41,200 | Ignores filtered-out rows |
| =AGGREGATE(1,6,D2:D12) | $40,887 | Ignores errors, hidden rows, subtotals |
Going Further
Sometimes you want *more* control—not less. Say your team uses "N/A" text instead of blanks. AVERAGE won’t touch it, but AVERAGEIF will:
=AVERAGEIF(D2:D12,"<>N/A",D2:D12) — excludes cells containing literal "N/A"
Or if you’ve got leading/trailing spaces messing up counts, clean them first with =TRIM(D2) in a helper column, then average that column.
Here’s the counterintuitive tip: AVERAGE never sees spaces as blanks—even if they’re invisible. A cell with =REPT(" ",1) looks empty but counts as text. Use =LEN(D2)=0 to test for true emptiness. That formula returns TRUE only for genuinely blank cells—not spaces, not "", not zeros.
Need to flag problematic cells fast? Select D2:D12 → press Alt + H + F + D (Home → Find & Select → Go To Special → Blanks) → then type 'X and press Ctrl + Enter. Every truly blank cell gets an 'X'. Now you’ll spot the difference between blank, zero, space, and "" at a glance.
When NOT to Use This
Don’t reach for AVERAGEIFS if your goal is statistical rigor across sparse datasets. If 70% of your rows are blank or zero, averaging the remaining 30% inflates representativeness. Talk to your BI team—you might need weighted averages or imputation.
Avoid SUBTOTAL(101,...) on unfiltered data—it behaves identically to AVERAGE, adding unnecessary complexity.
Never use =AVERAGE(A1:A1000) on raw CRM exports. Those files love injecting non-breaking spaces ( ) or Unicode zero-width characters. Paste data into Notepad first, then back into Excel—or wrap in =CLEAN(TRIM(A1)).
And skip AVERAGE entirely if any cell contains #VALUE!. It’ll return #VALUE! — unlike AVERAGEIFS, which quietly skips it.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Select only blank cells in range | Ctrl + G → Special → Blanks | Alt+H+F+D also works directly |
| Open Function Arguments dialog | Shift + F3 | Great for building AVERAGEIFS step-by-step |
| Toggle formula view | Ctrl + ` (grave accent) | See "" vs. actual blanks instantly |
| Fill down formula | Ctrl + D | After typing AVERAGEIFS in D2, select D2:D12, then Ctrl+D |
| Quickly delete spaces in selected cells | Alt + H + F + S | Home → Find & Select → Replace → leave 'Find what' blank, 'Replace with' blank → Options → check 'Match entire cell contents' |
