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.
– – 
– – 
Alvaro Ledesma says
Jon, What I think is missing is your example from previous post: targets as columns with border / no fill. Either version of that formating (target wider than actual or the actual filling-up targets) deserve to be included in this post.
Regards
Alvaro
Jon Peltier says
Alvaro – For time series data I’d rather keep my series of interest (in this case, actual values) in a line/XY chart format.
However, if in the previous example you set different goals for the categories in the chart, you can readily show them using the column/column formats.
PragmaticCynic says
Jon,
Generally I do the area/line approach but make the actual data into the area or column and the target as a line. People tend to think of targets as goals and goals as lines.
Jon Peltier says
With the line always appearing in front of the area or column, it makes sense to me that the line is the important data (the actuals), so I use the background data for the target, and use lighter colors to de-emphasize it.
As long as those reading your chart understand your style, I guess it’s more important to follow your style systematically.
AlexJ says
Agreeing with PragmaticCynic and disagreeing with Jon (sorry, Jon), my background in the process control industry leads me to follow their standard for operator interfaces: Target (setpoint) is a line, actual (process variable) is a bar. This provides a standard method for the operator to assess operational state for many control loops. (Come to think of it, the display is much like a vertical bullet graph!)
Translating that to financial forcasting approach by month over a full year, I organized my graph to provide:
1 – a moving monthly target for each month of the year
2 – a monthly bar represents actuals in past months of the year, but in future months from now to the end of the year, a stacked bar represents the actuals expected from backlog (orders in house) plus revenue from forecasted orders.
This approach provides a one-glance forecast of performance against plan to the end of the fiscal year.
(Let me know how I could upload an example to you. I’d even be willing to be the subject (victim?) of an Improve This Graph exercise.)Alex provided me with the following charts, which I have just now gotten around to adding.
Jay says
Jon,
While reading your valuable insight into the target-value graphing, I wish to know what is the best way to “animate” these graphs? How do I show this graph in the presentation (powerpoint) so that first I show the values and then plot the target, to reinforce my point and to focus on the discussions. Am I forced to use two different charts or is there a better way?
Thanks
Jay
Jon Peltier says
Jay – I rarely animate objects on slides in PowerPoint. I use PowerePoint as much as a static snapshot, or archive, as for official presentations.
I do know that PowerPoint has some capability to animate charts, by allowing you to select different timings for thee appearance of series in the chart. I did a quick Google search on the keywords “powerpoint animate chart” and several promising links came up.
derek says
I just tried it out on ancient Powerpoint 95, and Animation Settings lets you start a slide with one frame, “build” a first image over that, then another over that. If you align several charts on top of one another, I see no reason you couldn’t have quite a show, clicking to “advance” the action as you speak, bringing new features of the chart in.
Of course, even without animation settings, you can do this simply by having near-identical looking slides in sequence. Animation probably just makes developing a slide easier, not having to copy each ad hoc change in ,e.g., title font across the whole sequence.
Jon Peltier says
Derek –
Those “poor man’s” animations are certainly possible. I do know that I’ve seen examples in which Excel charts or perhaps MS Graph charts are ungrouped by PowerPoint in order that certain chart elements can be displayed in a prescribed sequence. I should figure out how and post it some day.
John Gardner says
AlexJ: The entry from the previous day was done as you suggest. I wonder why Jon switched from line=target to line=performance?
Jon Peltier says
John – The two cases are different. In the earlier post the chart was comparing several discrete categories against a single target, say, words per minute vs. job applicants, or monthly sales objective vs. sales staff.
In this post I was looking at a more continuous independent variable, specifically monthly sales target vs. month of the year. I used a lighter fill for the target, and a darker line for actual values, intending the actual values would thus have greater emphasis.
JoLeeAnn says
I am using a bar/line graph:
Line being target
2+ value points equal my bars
I am trying to get the target line to go from the left Y axis to the far right side of graph.
Current results are that the line goes from mid-point to mid-point of the X axis points.
Current fix is to draw a line from one end to the other. (I don’t want to draw a line).
Please help
Jon Peltier says
Hi JoLeeAnn –
Try the techniques here:
https://peltiertech.com/Excel/Charts/AddLine.html