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.
I like this one a lot better than the hollow box.
Yes, much better than previous solution.
Best regards from Belgium.
Is it possible to show only the part of the columns above or below the baseline (felt the columns below the baseline is kind of redundant)?
This is great Jon, but …
Pivot charts don’t allow XY Scatter. Do you have a technique for doing the same with a pivot chart?
Bob -
One more reason not to like pivot charts. Make a regular chart from your pivot table, and if it’s going to change configuration, use dynamic range names in the chart series to keep up with the changes.
That is what I have done Jon . But I do use Pivot charts as they are so easy, baselines are so useful, and so I was just hoping there was a technique.
“One more reason not to like pivot charts.”
I should have said, one more thing that frustrates me about pivot charts. Yes, they are easy, but also not so flexible. There is no way to keep the pivot-ness of the chart and add data from outside the chart.
You could try to include the baseline data in the pivot table, and add it as another series, but in my experience (and I’m sure in yours), refreshing a pivot chart with more than minimal formatting causes the formatting to revert to the default.
I use your hollow bar technique, but instead of making a border I use a gradient fill:
Stop 1 is Red, position 0%,transparent 0%,
Stop 2 is Red, pos 2%, trans 0%
Stop 3 color doesn’t matter, pos 3%, trans 100%
Stop 4 color doesn’t matter, pos 100%, trans 100%
Its easier to implement and creates a line almost exactly like your xy error bars, unless the ‘baselines’ are at wildly different heights on the chart in which case some of them look fatter than others.
Joey -
A simpler approach would have a totally transparent bar, corresponding to the transparent 3% to 100% of your gradient, with a thin red bar on top, corresponding to the red 2%. The red part would be the same width no matter the height of the bar supporting it, and there’s not a lot of trial and error coming up with the exact gradient to make it work. Plus the line thickness could be linked to a value in a cell, which you couldn’t do with the gradient.
Good point Jon. I quite like that idea. I’ll give it a try next time.