This is the third in my series of blogs on the key “tricks” that you need to do a wide variety of well-designed charts, using only Excel. Today’s tip is on creating reference lines using Excel’s option to “change a series chart type”. This one might be the simplest of all.
Reference lines or markers are useful to indicate targets, key thresholds or descriptive statistics like mean or median values. Two versions are presented here: a single reference line and a set of markers that may differ for each data point.To do this:
For the reference line, create a series in your data with the reference amount in each cell. Add one extra data point at the start and end of this series (this allows the line to cross all of the bars).
Insert a stacked bar chart. Control-click on the part of the bar representing the reference line data and select “Change Series Chart Type”. Change the reference series to a line chart. You can format the line however you like later. Format the x-axis by changing the minimum and maximum under “Bounds” to include only the real data points.
To do the target markers there is no need to extend the data. Create the target data line and then change the target data series to a line with markers. Control-click the line to “Format Data Series”, and under “Series Options” select “No Line”. Then under “Marker”, “Marker Options”, select the “Built In” option, and choose a the shape you’d like. I have used the dash option here, increasing it’s size and changing the default colour.
With this technique, along with changing bar widths and using the secondary axis, you can create many of the charts that are standard in Tableau and other dedicated data visualization software. For example, combining all three you can now create “bullet charts”. I go through that as an example during my data viz course: see the Training section on this site.
There are a few more tricks to round out your abilities in creating effective data visuals with Excel, particularly involving manipulation and control of chart axes and adding information to scatter plots. Putting all of these techniques together to create high quality charts designed to inform clearly and quickly and then situating these in reports, presentations and briefing notes in a way that maximize their impact will greatly improve your ability to communicate with data.