The KPI Chart That Shows Zero for Missing Data
Monthly_KPI.xlsx has month labels in A2:A13 typed as text ('Jan-25' etc.), Revenue in B2:B13 in whole dollars (values 1.2M–3.4M), and Margin % in C2:C13 as decimals 0.18–0.32. Two cells in B (May, June) were left empty and two (September, October) contain 'n/a' typed as text. You insert a Clustered Column chart for Revenue and add Margin % as a second series. Symptoms: May and June have no columns at all, September and October show zero-height columns, the margin series is invisible along the baseline, the horizontal axis shows 1,2,3…13 instead of month names, and when you pasted in a fourteenth month (Feb-26) the chart did not grow. A director will see this chart in a deck and the numbers must not be misread.
1. Distinguish the empty cells from the 'n/a' cells in terms of how the chart renders them, and state the correct way to encode missing data.
Read the worked answer
Three cell states behave three different ways, and only two of them are under your control.
Truly empty cells (May, June). These are governed by Chart Design > Select Data > Hidden and Empty Cells. 'Show empty cells as Gaps' draws no column; 'Zero' plots a zero. The default is Gaps, which is why no columns appear - the category still exists on the axis, but nothing is drawn. That is usually correct for 'not reported'.
Text such as 'n/a' (September, October). This is neither empty nor numeric, and a chart plots non-numeric entries as 0. The result is a zero-height column that reads as genuine zero revenue. This is the genuinely dangerous case, because the chart is now telling a false story.
Correct encoding. Replace 'n/a' with the formula =NA(). #N/A is skipped by charts - no column is drawn - while AVERAGE, SUM and COUNT also ignore it. Never use "" (an empty string): a formula returning "" is a text value and plots as zero, the exact trap you are trying to escape.
Add a one-line note beneath the chart fixing the convention: 'gap = not reported; 0 = reported zero'. Without that, a gap and a zero are indistinguishable to the reader. Separately, confirm whether May and June are genuinely missing or were entered as a space character - =COUNTBLANK and =ISNUMBER will tell you.
2. Explain why the Margin series is invisible and how to make it readable, plus why the axis reads 1–13.
Read the worked answer
Margins. A value of 0.25 on an axis scaled 0–3,400,000 is a flat line sitting on the baseline - mathematically correct, visually useless. Two changes:
- Right-click the Margin series > Change Series Chart Type > Line (or Line with Markers). A percentage trend is read as a line, not as bars competing with dollar columns.
- Format Data Series > Secondary Axis. Then format the right-hand axis as Percentage with fixed bounds 0%–40% rather than letting Excel choose 0–0.35, and label it 'Margin %'. Add both axis titles: 'Revenue ($)' left, 'Margin %' right.
Pass on the caveat: a secondary axis is a presentation device that places two unrelated scales side by side, so where the lines cross and which series looks 'higher' are arbitrary and must not be interpreted. If the director's question is comparison, split it into a revenue column chart with a small margin chart beneath, sharing the same category axis.
The 1–13 axis. Either the Horizontal (Category) Axis Labels box is empty - Excel then numbers the points - or column A holds real dates and the axis switched to a time-scale. Fix: Select Data > Edit the Revenue series > set Axis Labels to A2:A13; if the cells are true dates, set Axis Options > Axis Type > Text axis to force one label per point. Then reduce the font to 9pt and rotate if they crowd.
3. Why did the chart ignore the fourteenth month, and what is the most maintainable fix? How do you prove it works?
Read the worked answer
Charts do not watch the sheet; they store explicit range references. This chart reads $A$2:$C$13, so a row pasted at row 14 is outside the definition and invisible - no refresh, no warning.
Options, most robust first:
- Make the source a Table. Select A1:C13, Ctrl+T, name it KPI, rebuild the chart from the table. Excel then extends the plotted series and the category labels automatically when rows are added directly beneath the table. This is the durable fix and survives whoever maintains the file next.
- Insert inside the range. Right-click row 13 > Insert, then type the new month there. Excel expands ranges when rows are inserted within them; pasting below the last row never does.
- Dynamic named ranges built with OFFSET/COUNTA, referenced from the series formula. It works but is fragile - a blank cell inside the data truncates the range, and the names are invisible to most users.
Proof. Add a temporary row with an absurdly distinct value (say 9,999,999) and confirm the chart changes shape immediately; then delete it. After rebuilding, re-check that the 'n/a' handling still produces gaps (the =NA() cells), that the secondary axis is still assigned to Margin, that the axis labels still show month names rather than 1–14, and that the chart title and number formats survived. A rebuild resets formatting more often than people expect, so verify rather than assume.