The year chart that lies
A workbook holds 24 months in A1:C25. Column A holds labels typed as text - 'Jan-24', 'Feb-24' and so on; column B is units sold; column C is revenue in dollars. After selecting all three columns and inserting a line chart, the revenue line sits flattened against the bottom of the plot while the units line fills the chart. In the twelve months where a formula returns "" instead of a number, the units line drops vertically to zero and climbs back, producing cliffs that never happened. The horizontal axis spaces the labels evenly, so December and January sit almost on top of each other regardless of the real interval. Your manager wants one chart that shows both measures.
1. Diagnose the two visible faults and give a remedy for each, including anything you must add so the result is not misleading.
Read the worked answer
Fault 1 - blanks plotted as zero. The cells are not empty; they contain "" returned by a formula. 'Show empty cells as gaps' applies only to genuinely empty cells, so the line drops to zero. Change the formula to return NA() instead of "": the chart skips #N/A while the cell still looks blank to a reader. Filtering those rows out of the source range is the alternative.
Fault 2 - one axis for two magnitudes. Revenue in the tens of thousands flattens units in the hundreds. Select the revenue series, then Format Data Series > Secondary Axis, or use Change Chart Type > Combo with revenue as a line on the secondary axis.
Do not stop there. A secondary axis is a quiet editorial decision. Label both axes with their units, colour-code the legend to match, and title the chart with measure and period. Without those, two readers will draw two different conclusions from the same picture.
2. Why is the horizontal axis evenly spaced, and what has to change in the source data before a date axis will work?
Read the worked answer
Why. The label column was typed, so Excel stores text and treats A2:A25 as categories. Categories are spaced at equal intervals by definition, so the chart cannot show that some months are missing or that intervals differ - it simply lists them.
Fix the source first, the chart second.
- Enter real dates (1 Jan 2024, 1 Feb 2024, ...) and format them as mmm-yy so they still read like the old labels.
- If conversion is needed, Text to Columns with the Date column type is more predictable than DATEVALUE, which parses according to locale and will misread day/month order. Check the first converted value before filling down.
- Keep a copy of the original text labels if a heading or report title depends on exactly that wording.
Then the axis. Format Axis > Axis Type > Date axis, base unit Months. Real gaps now appear as gaps, and the spacing reflects time rather than row count.
3. Your manager insists on a single chart with two vertical axes. What are the risks, and what alternatives would you put on the table?
Read the worked answer
The risk. With two independent scales you can make the lines cross anywhere you like by adjusting one axis maximum. Any crossing, divergence or 'revenue overtook volume in June' reading is then an artefact of your scaling, not a fact about the business. Readers reliably over-interpret it, especially in a slide with no axis labels.
Alternatives worth proposing:
- Index both series to 100 at the first month. One axis, honest comparison of relative movement, and the question 'did revenue grow faster than volume?' is answered directly.
- Two stacked small charts sharing the same x-axis, so each keeps its own scale without inviting comparison of line positions.
- Columns for units, line for revenue on a single axis - only if the magnitudes are close enough for one scale to be legible.
If the dual axis is non-negotiable: label both axes with units, keep the column or bar series starting at zero, colour-match axis labels to their series, and state the choice in a caption. Choose the design from the question the chart must answer, not from which looks busiest.