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.

Jon, thanks, your recipe for that combo chart wasn’t too hard to follow.

For displaying such target-actual data, your Overapping Bar Chart seems like a more intuitive design, plus is easier to create. Just put the target data in back with the wider bars, of course, to provide the bullet chart lite effect.

Jon,

once again, thanks for being there !

and once again, I feel sooo dumb…… here’s my problem:

I have 3 columns: Office, Revenue (full figures) and headcount.

I was trying to follow your instructions to create the horizontal bars chart, showing the max revenue at the top.

then I wanted to create a line with the headcounts, but you’ve lost me with the Height column.

Any suggestions?

Thanks ind advance !

Martín

Martin -

How many bars in a series? Call it

NThe secondary (XY chart) value axis, the vertical one at the right of the chart, should scale from zero toN. The values should go from 0.5 toN– 0.5. Make sure these values are sorted in the same sense as in my example above.Jon,

there are 12 series.

what I did was to add another column “Height”, and estimate where the dot should go, on the same scale. it somehow worked, but the problem remains as I cannot show the headcount info.

this is not simplified enough for someone whos just learning what it is to understand

Anonymous – Where do you get stuck?

I tried both of the solutions and followed step by step, however, the line is on a horizontal way but not vertical direction as showed in your graph. There is no way to change it. Any suggestions? ( I am using Excel 2007).

Thanks you very much!

Thanks this is Briliiant…was trying to work it out for so long.

Thank you this was very helpful. Any idea how to get the data labels on the “Actual” line in the finished product to display the x-values (ie the “actual values”)? When I turn on data labels I can only get it to display the y-values (the “height values”).

Thanks in advance.

Scott -

There are choices when adding data labels that allow you to tailor the content of the labels. But, you have to skip past the lame options on the Chart Tools > Layout Ribbon dropdown, click More Data Label Options at the bottom of the dropdown, and choose from the wider selections in the dialog.

Jon, any known problems for this Bar-Line combo chart in Excel 2010. I follow instructions pretty well and but Excel will not plot second column of data on a second x axis I. Only gives me a vertical axis. I assume its operator error (me) but at this point its worth asking about a software issue.

Charles -

This works fine in 2007/2010. In fact, you don’t even need the secondary horizontal axis, because unlike Excel 2003, the later versions know how to use the primary axis for the secondary series. But if you need the secondary axis, go to the Chart Tools > Layout tab, click Axes > Secondary Horizontal Axis > Show Default Axis (or whatever option you need).

Jon,

Thanks so much this explanation was a life saver.

Is it possible to do a combination stacked bar line graph where the lines are displayed as a step chart?

I tried using your tutuorial below, but could not figure out how to make it work –> lines not stepped or bars too narrow

http://peltiertech.com/Excel/ChartsHowTo/StepChart.html

Andy -

How do you want the steps to look? Like this?

Jon,

Thanks for the quick reply. Sorry, but I mistakenly said bar chart when I meant column chart.

Your line graph is showing what I need for the line graph (steps).

Thanks again for your help,

Andy

Andy -

I should have included more information.

The bar chart with the step chart overlay is a combination chart with the bar series on the primary axis and an XY series on the secondary axis.

The column chart with the step chart overlay is a combination chart with the column series and XY series both on the primary axis.

Jon,

The last post (tip to use separate data source with XY Scatter for steps) helped me figure this out.

Here is a draft version of the end result.

Thanks so much,

Andy

I am trying to combine chart types on a single graph. I’d like my primary chart/axis to be a scatter plot (many data points – i.e. groundwater level measurements taken every 15 minutes) and the secondary chart/axis to be a column graph showing a limited amount of data (i.e. daily precipitation). I can easily plot this as two scatter plots on the same graph with a primary and secondary axis, but I can’t get the secondary data to plot as a column graph. Help?

Jennifer. Assuming you are using Excel 2010 or later. Below is the URL for the solution from Microsoft. I think you are halfway there. Is sounds like you have both series plotted on your chart already. What you need to do is highlight Series 2, which will then launch the Design tab on Chart Tools. On the far left of the tab, you will see the icon for ‘Change Chart Type’. Use that to function to change Series 2 to a column chart. By having only Series 2 highlighted, you are only changing the chart type for that series. Hope it helps. cg

http://office.microsoft.com/en-us/excel-help/add-or-remove-a-secondary-axis-in-a-chart-HP010342149.aspx

Jon

Does this only work if the numbers of both series are in the same order of magnitude? I have followed your steps exactly (multiple times) however I am attempting to overlay real numbers (XY) over percentages (Bar) . When I paste special, the X axis changes from 0 – 20% to 0 – 20,000% and I obviously loose sight of the original series.

I have tried playing with the axis etc to no avail. Does that make sense?

thanks

Conor -

Plot the percentages on the primary axis and the large numbers on the secondary.

the older version of excel was better for charts requiring bar and line representations for two data series where the units being measured by the two series differ (e.g., series 1 is in % & series 2 is a count). is there a way to prompt excel to offer a second axis? (the examples above do not seem to address this …)

Gary -

The new versions of Excel are the same as the old in this respect. You need to format a series so it appears on the secondary axis, and Excel will draw the secondary vertical axis on the chart. Then on the ribbon you can find the controls to add the secondary horizontal axis, if necessary.

Gary, this link has helped me with similar questions. Although a little simplistic, it should do the trick if I correctly understand your question.

http://office.microsoft.com/en-us/excel-help/add-or-remove-a-secondary-axis-in-a-chart-HP001234165.aspx

well, apparently simplistic works for me. thanks.

Hi am fairly new to chart, and I am trying create a simple chart that shows month over month volumes of a particular product. For example I want to show apples, oranges, and pears i bought in Jan, and Feb and did I increase or decrease my volume.

Diva -

To plot the volume, put them into a worksheet, select the data, and insert a chart (left). To plot month over month change in volume, calculate the changes in another range, select this calculated data, and insert a chart (right).

Read carefully: “select the Lines with Markers version of the

XY chart type”The XY chart type

isthe Scatter chart type.