A margin line that sits flat on the floor
A 12-month dashboard plots Revenue in $000s (values from 41 to 58) as columns and Gross Margin % (values 0.021 to 0.038) as a line on the same axis. The line renders as a flat stroke along the bottom, and a manager concludes 'margin is stable'. A newly added 13th month in row 14 does not appear at all, because the series range is pinned to $B$2:$B$13. The value axis starts at 40,000 rather than zero, so the 41,000 month looks near-invisible next to the 58,000 month. The workbook is circulated to the board.
1. Why is the margin series unreadable, and how do you fix it without creating a new problem?
Read the worked answer
Both series share one value axis. At a scale of 40,000–58,000, a value of 0.03 is effectively zero, so the line is drawn on the axis - not a data problem, a scale problem.
Fix: right-click the line > Format Data Series > Secondary Axis (or Change Chart Type > Combo: Revenue as clustered column on the primary axis, Margin % as line on the secondary). Then set the secondary axis to 0–5% and add data labels so the reader never has to read two scales.
Two things to check before publishing:
- Confirm the percentages are stored as 0.021, not 2.1. If they are 2.1, a percentage axis will label them 210%.
- A dual axis lets two unrelated scales be positioned so the lines appear to converge or diverge. Label both axes with units ('Revenue $000s' left, 'Margin %' right) and state in the caption that the line is a rate, not a volume.
Without the secondary axis, the honest alternative is two separate charts; a flat line on a shared axis is worse than no chart.
2. The 13th month doesn't appear. Give two fixes and say which you would standardise on.
Read the worked answer
A chart series pointed at a fixed range ($B$2:$B$13) never grows; adding row 14 changes nothing until the range is edited or a dynamic source is used.
Fix A - Excel Table: select the source, Ctrl+T, then build/rebuild the chart from the table. Tables auto-expand on typing in the row below, and the chart follows on recalculation. Also check the category-axis range ($A$2:$A$13) - both must grow.
Fix B - dynamic defined name: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1), then type the name into the series formula. Works, but OFFSET is volatile and the COUNTA-1 arithmetic breaks if the column ever contains blanks or a note below the data.
Standardise on the Table. It has no formula to break, survives row insertion, and every chart on the dashboard takes its source from the same table. Verify by typing a test month in row 14 and confirming 13 points appear.
3. Is starting the value axis at 40,000 acceptable? Argue the judgement call for a board audience.
Read the worked answer
For a column chart, no. Columns encode magnitude by area/length, and truncating the baseline breaks that encoding: 41,000 and 58,000 differ by 41%, but a 40,000-based axis makes the first column look like a sliver and the second like a full bar - visually closer to a 0-versus-100% difference.
Options, in order:
- Set the column axis minimum to 0. Differences stay proportional.
- If month-to-month detail genuinely matters, switch that series to a line chart, where position rather than area encodes the value and a non-zero baseline is conventional - but print the axis endpoints clearly and keep the zero-based column chart elsewhere in the pack.
- Plot variance against target or against the 12-month average, which puts the message in the chart instead of in the axis.
Whatever is chosen, it must be consistent across the dashboard and disclosed: a footnote such as 'axis starts at 40,000' is the minimum. For a board, the default should be zero-based, with the truncated version available only as a clearly labelled secondary view.