Build a Bar-Line Combination Chart

When you have two series in a chart, and you want to show them using two different chart types, you have a broad array of choices. If you have target and actual data for a set of product attributes, for example, you can make a column-line combination chart readily.

Suppose your data looks like this (ignore the “height” column for now):

Make a column chart with all of the data:

Right click the Actual series, choose Chart Type from the pop up menu, and select a Line Chart subtype:

That was pretty easy.

What if you want to use horizontal bars? Perhaps the attribute labels are too long to make good category labels along the horizontal axis. Long ago I wrote a tutorial on Bar-Line Combination Charts on my web site, but that’s a complicated example. Here’s a new, simpler version of that tutorial.

Let’s try the same approach as above. Here’s a chart with both series as bars:

Right click on the Actual series, choose Chart Type, select the line with markers subtype. That’s no good. The bars increase in value from left to right, while the line values increase bottom to top.

So we need a new approach. Make a bar chart with the Target data.

We want the attributes listed in the opposite order, so format the vertical axis, check Categories in Reverse Order and Value Axis Crosses at Maximum.

Copy the Actual and Height columns of data, select the chart, and use Paste Special to add the data as a new series, series in columns, series names in first row, and categories in first column.

Right click on the Actual series, choose Chart Type, then select the Lines with Markers version of the XY chart type. Since Actual is used for the X values of the XY series, both series show Target and Actual increasing from left to right.

Here is how to compute the values in the Height column. There are five categories (attributes) along the left hand axis, and the XY series markers fall in the middle of each category.Y values for the XY series range from zero at the bottom to 5 at the top of the axis. The first point is for Attribute 1, and is plotted at a height of 4.5. The next one, for Attribute 2, is at 3.5, an so forth, as shown in this chart.

You have to keep both horizontal axes in the chart, and you have to make sure they stay synchronized when the data changes. But you can hide the top horizontal axis: format it so it uses no line and shows no tick labels.

Well, that wasn’t so hard, either.

Related Posts:

• Adding an Arbitrary Target
• Conditional Stacked Clustered Column Chart with Targets
• Multiple Horizontal Bullet Graphs in Excel
• Add a Target Line
• Add Individual Target Lines To Each Cluster in a Column Chart
• How to Make Horizontal Bullet Graphs in Excel