A reader of this blog asked how to add a series to a 3D column chart, to show reference values. Excel doesn’t allow combination charts if one of the chart types is 3D (fortunately!), so she added lines from the Insert Shapes group on the ribbon. Here is the result.
As a quick response I sent the following chart, which uses a hollow column on the secondary axis to indicate the baseline, and I wrote up the tutorial in Simple Baseline for Excel Column Chart.
In a comment to that post, a reader suggested a horizontal line, rather than a whole box, as the baseline marker. The following protocol describes how to accomplish this
Here’s the data for these two examples.
The first step is to create a clustered column chart.
The second step is to switch rows and columns in the source data orientation if necessary (it was).
Now we’ll clean up the chart a bit. Let’s remove the chart area border, remove the line for the vertical axis, use lighter gray lines for the gridlines and horizontal axis, and remove the tick marks from the horizontal axis.
Change the Baseline series to the XY (Scatter) type, which also moves it to the secondary axis.
Reassign the Baseline XY series to the primary axis.
Apply a consistent set of colors to the Q1 through Q4 series.
Add error bars to the Baseline series. The default is horizontal and vertical error bars of length 1 in the positive and negative directions.
Select and delete the vertical error bars.
Resize the horizontal error bars. Use trial and error, or if you’re good at math:
Error bar total width (2 error bars) = 4 column widths
Total category width = 4 column widths + gap width
Gap width = 150, which means 1.5 column widths
Single error bar width = 4/5.5/2 = 0.364
Format the error bars as desired.
Add data labels. If you select the whole chart and then use the Data Labels command on the ribbon, all series will be labeled in one step. This saves a lot of time over labeling one series at a time.
Change each set of labels from Value to Series Name, and change the Baseline label position to Below.
Finally, if desired, use a darker shade of the series colors for the labels. Change the Baseline marker style to none, and delete the legend
It’s a lot of steps, slightly more involved than the “hollow box” technique of the previous post, but it’s still quick and easy.
