A chart that hides outliers and exaggerates a dip
Sheet Monthly has A1:C13: month names Jan to Dec in column A, actual sales in column B, and forecast sales in column C. November actual is blank because the month has not closed, while the forecast exists for all twelve months. Rows for August and September are hidden by a filter because those two months were outliers. You select A1:C13 and insert a clustered column chart. August and September do not appear at all, November shows as a zero-height column, the vertical axis starts at 480,000 rather than zero, and the legend reads Series1 and Series2. The finance director says the chart makes a normal month look like a cliff and asks for a version suitable for the board pack.
1. Explain why two months vanished and November shows zero, and how to correct both.
Read the worked answer
By default, Excel charts do not plot data in hidden rows or columns, so the filtered-out August and September rows were excluded from the chart even though they were part of the selected range. November's blank actual cell was plotted as zero, which creates a false column height.
Corrections:
- For hidden rows: select the chart, use Chart Design > Select Data > Hidden and Empty Cells, then tick Show data in hidden rows and columns. Alternatively, clear the filter or copy the visible data to a separate charting range. Decide deliberately: if the outliers were hidden to keep them out of the chart, excluding them is intended; if they were hidden for another reason, the chart is now misleading.
- For the blank November cell: in the same Hidden and Empty Cells dialog, choose Show empty cells as Gaps. A gap is honest for a month that has not closed. Choosing Zero asserts that sales were zero, which is false; Connect Data Points with Line suits a line chart but not a column chart.
- Recheck the source range after any filter change, because the chart reads the range, not the screen.
Document the choice in the chart subtitle, for example Actuals to October; November not yet closed.
2. The axis starts at 480,000. Why is this misleading, and what do you change?
Read the worked answer
A column chart encodes value by the height of the bar measured from the axis. If the axis starts at 480,000, a fall from 520,000 to 500,000 looks like a collapse, because the visible bar height is only a fraction of the real difference. This exaggerates change and is a common way charts mislead, deliberately or not.
Fix:
- Select the vertical axis, Format Axis, and set Minimum to 0. This restores an honest baseline for a column or bar chart.
- If the differences are genuinely too small to see on a zero-based axis, change the chart type rather than truncating the axis: a line chart with a clearly labelled, non-zero axis can show trend detail without implying that bar length equals absolute magnitude.
- Label the units, for example Sales, EUR thousands, and keep the axis number format consistent with the worksheet.
- Add data labels if the exact figures matter to the board.
For a board pack, zero-based columns are usually the safer choice because directors read bar height as magnitude. If a truncated axis is unavoidable, state it explicitly on the chart.
3. How do you replace Series1 and Series2 with real names, and what else do you check before exporting for the board pack?
Read the worked answer
Two ways to fix the legend:
- The robust method: select the chart, Chart Design > Select Data, select Series1, click Edit, and set the Series name to =Monthly!$B$1 (the header cell Actual). Repeat for Series2 with =Monthly!$C$1 (Forecast). The legend then updates automatically if the header text changes.
- If the source is an Excel Table, inserting the chart from the table uses the header row as series names automatically.
Before exporting:
- Confirm the chart type suits the message: clustered columns are fine for comparing actual and forecast month by month; a line chart is better for trend; a pie chart is not appropriate because there are two series and twelve categories.
- Check that the chart is on one page: set the print area, use landscape, and use Page Break Preview to see where the chart splits. A chart cut across two pages is unusable in a board pack.
- Export with File > Export > Create PDF/XPS rather than printing to a physical printer. If the layout must be frozen exactly, copy the chart as a picture; otherwise it stays linked to the data and will update when November is entered.
- Check the title, axis labels, units and source note, and confirm that hidden rows or sheets are not silently omitted from the exported pages.