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.

3D Column Chart with Reference Lines

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.

Hollow Columns for Baseline

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.

Data for this simple example

The first step is to create a clustered column chart.

Create a clustered column chart

The second step is to switch rows and columns in the source data orientation if necessary (it was).

Switch rows and columns if necessary

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.

Clean up the formatting

Change the Baseline series to the XY (Scatter) type, which also moves it to the secondary axis.

Change Baseline series to XY type

Reassign the Baseline XY series to the primary axis.

Move Baseline XY series to primary axis

Apply a consistent set of colors to the Q1 through Q4 series.

Apply 'nice' formatting to series Q1 through Q4

Add error bars to the Baseline series. The default is horizontal and vertical error bars of length 1 in the positive and negative directions.

Add error bars to Baseline series

Select and delete the vertical error bars.

Delete Baseline series 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

Correctly size Baseline series horizontal error bars

Format the error bars as desired.

Format horizontal error bars

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.

Add data labels to all series in chart

Change each set of labels from Value to Series Name, and change the Baseline label position to Below.

Change all labels from Value to Series Name, move Baseline labels to Below position

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

Color label font to match series, change Baseline to no marker, delete 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.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 
PTS Chart Utilities: Waterfall, Box and Whisker, Cluster-Stack, Panel, Marimekko, Dot, Panel

The post Another Simple Baseline for Excel Column Chart appeared first on Peltier Tech Blog.