How to handle missing values before building a dashboard

The "average units per store" card on your dashboard reads 45. The client's regional manager glances at it and says, flatly, "that's too high — half our new stores sold almost nothing last month." She's right, and the reason is that thirty of the stores have a blank in the units_sold column, and a blank quietly drops out of an average instead of counting as the zero it actually represents. Your 45 is the average of the stores that reported, not the average of the stores. Missing values don't announce themselves; they just move your numbers in a direction nobody chose.

A blank is a question, not a zero

The single most expensive assumption in dashboard work is that an empty cell means zero. Sometimes it does — the store sold nothing. Sometimes it means the opposite — the store didn't report, so the true number is unknown and could be large. And sometimes it means "not applicable," a store that wasn't open yet. Those three meanings demand three different treatments, and no tool can tell them apart for you. Deciding what a blank means, column by column, is the actual work; the mechanics of filling it are the easy part.

The example: same column, two honest answers

Here's units_sold for six stores:

Store   units_sold
A       60
B       40
C       (blank)
D       50
E       (blank)
F       30

=AVERAGE(B2:B7) returns 45 — it silently ignores the two blanks and averages only A, B, D, F. But if those blanks are stores that genuinely sold zero, the honest average is =SUM(B2:B7)/6 = 30. Same six stores, same formula bar, and a fifty-percent swing in the headline number depending entirely on what "blank" means. If instead the blanks are stores that failed to report, the correct move is neither 45 nor 30 — it's to exclude them and say so, because averaging a number you don't have is a guess wearing a decimal point. One column, and the difference between the answers is the whole meeting.

A decision process for missing values

1. Quantify first. Run =COUNTBLANK(range) on every column. Two percent missing and forty percent missing are different problems that deserve different care.
2. Ask what the blank means — per column, with the client if it's not obvious. Zero, unknown, or not-applicable? This one answer drives everything after it.
3. Choose a strategy to match the meaning. If blank means zero, fill with 0. If unknown, exclude the row from that metric and note the count. If not-applicable, keep it blank and make sure the chart doesn't fold it into a total.
4. Never impute silently. If you fill missing values with an average or a carried-forward figure, say so in writing — an invented number that looks real is worse than an honest gap.
5. Flag, don't delete. Add a was_missing marker column so the dashboard can show "based on 970 of 1,000 stores reporting" instead of hiding the gap behind a confident total.

Handle each column deliberately and your "average units per store" comes with a footnote the regional manager can trust: 30 across all stores, or 45 across the 34 that reported — either is defensible, as long as the card says which.

Show the gap; don't paper over it

The instinct under deadline is to fill every blank with something so the charts render cleanly, but a dashboard that hides its missing data is more dangerous than one that admits it, because it looks equally confident whether it's built on complete data or half of it. The professional move is the opposite of tidy: surface the coverage. "970 of 1,000 stores reporting" on a card tells the client exactly how much weight to put on the number, and it's the kind of honesty that gets you hired again. Missing values aren't a flaw to conceal — they're context the client needs to read their own business correctly.