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):

Bar-Line Combo Chart Data

Make a column chart with all of the data:

Column Chart with Bar-Line Data

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

Column-Line Combo Chart with Bar-Line Data

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:

Bar-Line Combo Chart - Step 1

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.

Bar-Line Combo Chart - Step 2

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

Bar-Line Combo Chart - Step 3

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.

Bar-Line Combo Chart - Step 4

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.

Bar-Line Combo Chart - Step 5

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.

Bar-Line Combo Chart - Step 6

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.

Bar-Line Combo Chart - Step 7

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.

Finished Bar-Line Combo Chart

Well, that wasn’t so hard, either.

Peltier Tech Chart Utility

Comments

  1. 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.

  2. 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

  3. 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 to N. The values should go from 0.5 to N – 0.5. Make sure these values are sorted in the same sense as in my example above.

  4. 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.

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

  6. Anonymous – Where do you get stuck?

  7. 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!

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

  9. 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.

  10. 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.

  11. 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.

  12. 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).

  13. 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

  14. Andy –
    How do you want the steps to look? Like this?
    Excel Bar Step Combination Chart

  15. 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

  16. 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.
    Excel Bar Step Combination Chart
    The column chart with the step chart overlay is a combination chart with the column series and XY series both on the primary axis.
    Excel Column Step Combination Chart

  17. 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.
    Andy's Combination Stacked Column - Step Chart

    Thanks so much,

    Andy

  18. 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?

  19. 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

  20. 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

  21. Conor –
    Plot the percentages on the primary axis and the large numbers on the secondary.

  22. 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 …)

  23. 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.

  24. 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

  25. well, apparently simplistic works for me. thanks.

  26. 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.

  27. 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).

    Monthly Volume

  28. Read carefully: “select the Lines with Markers version of the XY chart type

    The XY chart type is the Scatter chart type.

Subscribe without commenting

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.