The Dashboard That Lost December
Finance's dashboard has one clustered column chart of monthly Revenue with a line series for Margin % on a secondary axis. The series are hard-wired to Data!$B$2:$B$13 and Data!$C$2:$C$13. December's figures were added in row 14 and do not appear. Three months have no sales yet, so column B is blank for them - the Margin % line drops to 0% across those months and the secondary axis rescales so the line sits on the floor. Someone then deleted two columns on Data to tidy it up, and the Revenue series now plots start-date values. The dashboard is opened on a laptop and on a conference-room projector, where the axis scaling looks different again.
1. Why is December missing, and what is the most maintainable way to make the chart pick up new rows automatically without volatile formulas?
Read the worked answer
Why. A chart series stores the range as literal text: =SERIES(Data!$B$1,Data!$A$2:$A$13,Data!$B$2:$B$13,1). Adding data in row 14 changes nothing, because row 14 was never inside the stored range. OFFSET-based dynamic names do react, but they are volatile and recalculate on every change - heavy on a large workbook.
Most maintainable fix.
- Select the source and Insert > Table (name it, e.g.
tblData). A chart sourced from a table expands when rows are added inside or directly beneath it. - Repoint each series to the table's structured references, for example Name =
tblData[[#Headers],[Month]], Values =tblData[Revenue], via Chart Design > Select Data > Edit. The series formula then reads=SERIES(,tblData[Month],tblData[Revenue],1). - If a table is unacceptable, leave ~12 blank formatted rows in the source range as a buffer and re-point the series to include them; blanks then plot as gaps and fill themselves as data arrives.
Check. Add a test row at the bottom and confirm the new month appears without touching the chart; then delete the test row.
2. The Margin % line drops to 0% for months with no sales. Explain what Excel is actually plotting and give the fix, including how to stop the projector from rescaling it.
Read the worked answer
What is happening. Two different situations get confused:
- Truly empty cells are governed by Chart Design > Select Data > Hidden and Empty Cells > "Show empty cells as: Gaps". That setting breaks the line.
- A formula returning
""is not empty - it is a zero-length text value, and a line chart plots it as 0. No setting fixes it; the formula must returnNA(), which line charts do not plot, so the line shows a gap.
Fix. Change the Margin formula to =IF([@Revenue]=0,NA(),[@Margin]), then verify the axis still treats the series as numeric. Hide the resulting #N/A from printouts with conditional formatting that sets the font to white, rather than with IFERROR (which reintroduces the zero).
Stabilise the axis. Right-click the secondary axis > Format Axis > set Minimum and Maximum to fixed values (e.g. 0 and 0.4) instead of Auto. Auto-scaling recalculates from the visible points, which is why the laptop and the projector disagree. Do the same for the primary axis if the data range is known. Then confirm both axes' number formats show % and thousands separators consistently.
3. After two columns were deleted on Data, the Revenue series plots start dates. Explain the mechanism and the procedure to restore every series safely.
Read the worked answer
Mechanism. A series formula holds literal cell addresses as text. It is not a cell formula, so it does not get the automatic reference rewriting that deleting a column triggers for cells like =B5. Deleting columns shifts the data left while the chart's stored addresses stay put, so $B$2:$B$13 now points at whatever occupies column B - here, the start dates. Nothing warns you; the chart simply plots the wrong numbers.
Repair procedure.
- Chart Design > Select Data. For each series, Edit and inspect both the Series name and the Series values box; the highlighted ranges on the sheet show immediately whether they are right.
- Re-point the values to the correct column, then set the category axis labels (the month column) in the right-hand list - a broken category range is often why labels look shuffled too.
- Re-check the secondary axis assignment for Margin %, since re-pointing can reset it.
Prevent recurrence. Rebuild the series against a table's structured references (as in the first task) so column deletion moves the data and the reference together. Also prefer clearing unwanted cells over deleting rows/columns in any sheet that feeds a chart, and keep a test: if the total of the plotted values does not match the source total, a series is misaddressed.