In yesterday’s post, Add a Target Line, I showed how to add a simple horizontal line target indicator to a chart. What if your target isn’t constant. I’m not talking about your boss’ moving target, but a case illustrated by the following dummy data. Our company makes widgets, which are more popular in spring and summer, as well as at the year end holidays. Most of the year our target is 20 units, but from April through August the target is 25, and in December we aim for 30 units.
We could simply create a line chart to show both the target and actual values. However, the target looks too much like the actuals, and we’d like them to look more different, so they can be distinguished at a glance.
The two lines are even more difficult to distinguish when the chart is shrunk to typical dashboard chart dimensions.
One way to distinguish the two series is to right click on the target series, choose Chart Type, and select an area type. Note that this moves the January and December data points to the edges of the chart.
A lighter target series fill color helps emphasize the actual values.
We can even dispense with the colors and switch to grays for improved photocopying results, or actually choose any color scheme that isn’t overpowering.
Another approach is to convert the target series to a column chart.
We can reduce the gap width to zero, and choose a lighter color, to de-emphasize the target.
The gray scheme from above works well for the column chart variant too.
The chart can be improved further by removing axis lines, applying a gap width of 5 to the columns, and pulling the month labels inside the bottoms of the bars to expand the vertical scale. (Excel doesn’t allow axis tick marks without the axis line, so I added an XY series with no line or markers but with short error bars.)
We can shrink the charts even further (the following are narrower but slightly taller). The dual line chart is no better than in the larger sizes.
The area and both column versions of the target series work well.
– – – –