fbpx
Search
Close this search box.

Excel Basics – What are Combination Charts and How to Make One?

Share

Facebook
Twitter
LinkedIn

This post is part of SpreadCheats series, read the rest of posts and cheat excel to become productive.

A combination chart is when you combine two different charts to make one. A popular example of combination charts is a line & bar graph combination. See below illustration:

excel-combination-charts

Today we will learn how to create a simple combination chart.

  1. For our combination chart, we will use the following data of Sales & Profits
    combo-chart-data
  2. First we will make a simple bar chart for the sales data. Just select the Sales data table, go to insert > chart and specify type as “column chart” (this is the default selection btw). Once you are done, the chart looks like this:
    first-part-combo-chart
  3. Now, select the profits data, press ctrl+c to copy it to clip board, and select the sales chart you have created above, and press ctrl+v to paste this data in to the sales chart. Essentially we are adding one more series of data to the sales chart.
  4. Now the chart should look like below. Right click on the new series (profits) and select “chart type”.
    combination-charts-howto
  5. From the chart type dialog change the type of chart from “column” to “line” (or whatever other type you fancy)
  6. That is all, you have successfully created a combination chart in excel.

Download the Excel Combination Chart Tutorial workbook and learn by experimenting.

As you can probably guess by now, there are some restrictions on what you can mix though. Best way to learn and understand combination charts is by experimenting. Just select one of the data series and change the chart type.

Like this ? Read other SpreadCheats to make your day to day spreadsheeting a breeze.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

59 Responses to “Excel Basics – What are Combination Charts and How to Make One?”

  1. Nick says:

    Hi Chandoo,

    I am big big fan of your excel skills i try all your tips at home by downloading them but if u have them stored in dvd it would be of great help chandoo how can i become an expert in excel like you what do you recommend for me

  2. Cristobal Colon says:

    hey chandoo, love your site, i use it every week for help with my job.

    I'm trying to do the combo chart here but have 2 problems.

    1 - you don't describe how to the the axis data into the right hand side of the chart. Critical for comparing 2 sets of data with different scales (like volume and $). Is this possible?

    2 - I'm having problems copying the data series. Doesn't work for me at all, even when I download your sample spreadsheet. now I have Excel 2003, maybe this is a function of Excel 2007 only?

    Thanks for the feedback!

    Sr. Cristobal Colon

    • Chandoo says:

      @Cristobal: You are welcome

      1. Adding another axis without going for combination charts is simple. You can select the particular series you want to move to other axis and go to format series and change the axis. Once the new axis shows up, scaling etc. is same as the primary axis.

      2. Copy pasting data works in Excel 2003 as well. If this doesnt work, just use data dialog. Right click on chart, go to Select Data option and add more series. Once the series is added now change its chart type.

      Let me know if you still have problems

    • Rodolfo Ayala says:

      Estimado Cristobal Colon :
                                           Tu eres el de El Salvador, Santa Ana ?...
      Dime.
      Años tengo de querer contactarte.
      SALUDOS CORDIALES,
      Rodolfo Ayala.

  3. Suresh says:

    Dear Chandoo,
    I love your site very much as it was helping alot in my daily job. Could you please give me a tips about macro useage.

    Thanks
    Suresh

  4. [...] type to a bar chart. Also set the height series to be plotted on secondary axis. Learn more about combining 2 chart types and adding secondary axis in [...]

  5. Geeta says:

    Good Morning Chandoo

    I landed on your website just by accident, and I haven't stopped surfing.
    Eventhough I am at work and I should not be surfing but I am doing this because I cannot stop reading all the info you provide.

    However, I do have a question, ofcourse it is work related..
    I am suppose to create a dashboard and I don't know how.
    People are creating a speedometer etc. and I don't know what they are talking about. I am using Excel 2003 and I don't know if I can create a dashboard or not. Perhaps I can create something similar to a dashboard using MS Excel 2003..
    Is there any help you can provide?(Ofcourse you can).
    I would greatly appreciate it.

    Thank you, so much

    Geeta

  6. Chandoo says:

    @Geeta: Welcome to PHD. thanks for your comments.

    You can learn about making excel based dashboards by visiting this page: http://chandoo.org/wp/management-dashboards-excel/

    There are a great deal of tutorials and techniques linked from that page. Make sure you download the example worksheets and try them your self.

  7. [...] Select the third column, press ctrl+c (copy). Now select the chart and press ctrl+v (paste). Excel will add another column series to the chart. Just select it and change the series chart type to “line chart”. Learn more about combining 2 different chart types in excel combo charts. [...]

  8. [...] chart is a combo chart of line and scatter plots. The smaller balls are part of line chart (so is the maroon color [...]

  9. Sel says:

    Hi Chandoo, I know you've suggested not using the speedometer but it is what the managers really want! I'm trying to set it so that 0 is in the middle, negative figures on the left and positive figures on the right but am really struggling. Can you advise please?

  10. Thad says:

    This is exactly the tip I was looking for! I just created one of the most well layed out plots I've ever done. It's clear and simple yet presents an enormous amout of data. It could almost be called art (to an engineer at least). Thanks!!!

  11. Harshad says:

    Hi Chandoo..Your site is informative but it seems that we can find similar stuff on the internet..I mean exactly similar in terms of methodology so whenever you post on your site if you can mention original author that would be really great. Eg. Your lets post on escalations in financial templete....there are plenty of them but i think you got what i trying to say...

  12. Tara says:

    Hey, guys! Thank you so-o-o much for all this useful articles! It's great treasure for me to find this web-site!! I'm very happy! 🙂
    I use Excel every day, so you'll understand my feelings! :)))
    Thanks! Thanks! Thanks!

  13. Allan says:

    Good afternoon. I am still having trouble making this combination chart. I cannot seem to get the data to paste into the bar graph chart, or when I can, it will not separate on the opposite axis leaving the data intact on the graph. Any suggestions?

    Thank you for your help!

  14. Hui... says:

    @Allan
    If it doesn't go to the second axis, select the series
    Right Click, Format Data Series
    On the Series Options Tab, Select Plot Series on Secondary Axis

  15. Min says:

    Thanks, really useful will take a look at ur other tutorials when i am free 🙂

  16. Annie says:

    Thanks for a very useful site.
    I am having one particular issue with graphs and am hoping you can help!
    I have a bar graph, and I want to include a target as a line - i.e. a constant amount across the series. However, I want this line to touch both vertical axis. If I follow the instructions above, the line starts and ends in the middle of a column.
    I have resorted to drawing a line, but would prefer not to use this option!

    • David says:

      I have the same problem. Can anyone help please? I am using Excel 2003. Thanks.

    • Manny says:

      Problem with reaching both vertical axis is related to your Horizontal axis I have excel 2013. The issue is that you have to set the minimum and maximum boundaries on the horizontal axis so that your data touches both vertical y-axis. In my case, my first data point was on date 12/31/2015, and my last one was on 12/31/2020:

      -Right clicked on the horizontal date axis, and clicked format axis

      -On axis options, Select Date Axis..This is gonna change the horizontal dates from "12/31/2015" to "1/1/2015". We don't want that yet but thats okay
      - THIS IS THE IMPORTANT PART: Set the minimum at "12/31/2015" and the Maximum at "12/31/2020". This will automatically set the horizontal axis so that the data on the graph touches both the primary y-axis, and the secondary y-axis

      But of course, in my case, I don't want the dates to say "1/1/2015"...I want them to say "12/31/2015" or whatever date/information you input to create the graph originally

      -On the Axis options--->Axis type--->select "text axis" again.

      You should have gotten what you wanted.

  17. Sachin says:

    Hi, I am working on something which requires one set of data on one axis(X) and other set on other axis(Y). Now first I have to plot these two data sets as mentioned on one chart and then I require to these two line charts to come up with one resultant chart. Thanks in advance will really appreciate your assistance.

  18. kavi says:

    dear chandoo

    great work
    i have accidently landed on your site and feel this is one the best accidents in the web city
    will be coming back to this sites if i get stuck up while using your great suggestion

  19. [...] Select the third column, press ctrl+c (copy). Now select the chart and press ctrl+v (paste). Excel will add another column series to the chart. Just select it and change the series chart type to “line chart”. Learn more about combining 2 different chart types in excel combo charts. [...]

  20. Nydia says:

    Wow, I can't believe how easy that was.  Great instructions!  Thanks!

  21. jm says:

    thanks chandoo big help to us

  22. Becky says:

    Hello,
      Your site has been a huge help and a great resource to me, so once again I am enlisting your help in creating one bar graph, and each data/value has its own color.  -the data will look like this

    Series 1 - 200
    Series 2- 300
    Series 3 - 44
    Series 4 - 87
    Series 5 - 95
    Series 6 - 285
    Series 7 - 99
    Series 8 - 345
    Series 9 - 55
    Series 10-36
    Series 11 - 150
    Series 12- 250 

    Is this possible?

    Thank you.

    Becky 

  23. Navis says:

    Thanks! Very helpful!

  24. Maureen Staiano says:

    I have  a basic 1st quarter sales chart. It is a column chart.  I don't want total sales to be in each quarter, instead just have it at the end as it's own data series.  Is this possible?
    I have not been able to find a way to make it work 

  25. [...] add another series of data to your sheet and then right click on it and change the chart type… Get Full Tip 64. To reverse the order of items in a bar / column chart, just click on y-axis, press ctrl+1, and [...]

  26. DSarnowski says:

    Is it possible to make a combination chart using two pivot tables for the data? This was a huge help!

  27. iniSuha says:

    thanks Chandoo.
    Its really works! I just done do my report with your clear & simple tutorials 🙂

  28. Iuliana says:

    Hello Chandoo
    first of all you are really wonderful when it comes to sharing all this things with the world the most practical resource i have encountered.
    One question is it ok to have the secondary axis like this ...I am looking at your final chart with the 2 data series, what i notice is that 0 to 50 in the left side for the 1st data equals the 0 to 25 on the right side. As long as the unit we use is the same - in your example $ dont u think the 2nd should be some how independent in regards to the primary one.
    All the best,
    Iuliana
     
     
     
     
     

  29. Prasanjit Maity says:

    Hi,
    I have 2 sets of data for different years (2012 & 2013), with same row and column headings. For each data i have created a "100% stacked column" chart.
    Now i want to combine the charts, so that the stacked column of each heading is represented as a clustered column.
    When i try the above steps, the columns are separated.
    What i want is, 1st column of 2012 & 2013 to be joint and then 2st column of 2012 & 2013 to be joint and so on....
    Please Help.

  30. Ameer says:

    Intresting information, it was very usefull for me.
    Thanks a lot 🙂

  31. Kumar says:

    Hi I have a set of data from pivot table as showin below
    Row Labels Average of Lead Time Count of Title
    Robert 109.19 158
    Mary 84.81 434
    tony 82.00 72
    Patrick 67.16 61
    Roger 66.14 112
    Kumar 58.47 52
    Michael 51.33 122
    Rosy 50.93 72
    Leena 24.19 2
    Grand Total 77.87 1085

    I want to see the lead time in bar chart with data labels and the count is in brackets besides lead time data label ex: 109.19(158), 84.81(434) and so on. How can I achieve with bar charts?

    thanks

  32. […] Related: How to create combination charts in Excel? […]

  33. QP says:

    Very helpful post. But what if I want both series as bars, side by side, and NOT stacked?
    In Excel 2010 I have the two sets of bars in one barchart, I select one of the data series, and then click "secondary axis". But now it is a stacked bar chart with two axes!
    But I don't want stacked, I just want side with both left and right axis. But 2010 won't let me do this. I see this question goggling, but no real solution. Changing vertical barchart type or even gap width of the series does nothing for me. I can switch the second series to a scatter plot, and get something that looks OK. But how about two vertical bar sets, side by side, using both left and right axis? Any ideas (this was default behavior in Excel 2003)? Thank you!

  34. Namrata says:

    Neat trick. Loved it. Thanks for sharing

  35. KIRANKUMAR says:

    hi mr chandoo,
    i have one bar chart of votes procured by parties.
    i have another chart of stacked column of no. of seats bifurcated in red for loosing seat and security deposit, yellow for loosing seat but saving security deposit and green for winners seats.
    can we combine both the charts - one columnar bar for votes and other columnar bar of stacked bar having two axis?
    Awaiting your reply.

  36. Ananta says:

    Hi,
    I have 2 sets of data for different years (2012 & 2013), with same row and column headings. For each data i have created a “100% stacked column” chart.
    Now i want to combine the charts, so that the stacked column of each heading is represented as a clustered column.
    When i try the above steps, the columns are separated. Pls help

  37. JJ says:

    What is need is the chart that shows a single bar, paired with a stacked bar side it, like in the above:
    "4.Now the chart should look like below. Right click on the new series (profits) and select “chart type”."
    I can get a single bar and a stacked bar on the same chart OK.
    But what I cannot do is get them nicely clustered in pairs like 4 above.

  38. JJ says:

    Dont worry, I found out that I own the utility to do this already!!!!
    Perhaps I should have looked on the ribbon first!!!
    The utility is on chandoo here:
    http://chandoo.org/wp/2009/02/09/review-of-pts-clustered-stack-chart/

  39. Pam Pam says:

    Thank you - this article has given me the "chan doo" attitude that I need to finish my project!

  40. Aste Aste says:

    Hi Mr. Chandoo.

    I am struggling to create a graph that shows water level as line chart and rainfall as column graph. The data for these were collected differently. Water level data was collected up to every hour per day while rainfall data was only taken once per day. How do I bring these sets of data together on one graph? Data is from 2008 to 2013. please help.

  41. […] Download :Excel combination charts – tutorial & examples – what are […]

  42. Melissa says:

    Hi

    I need to create a chart that has both stacked and simple columns. i.e. a single value for 2014 and a stacked value for 2015. I'm using office 2010.

    Please help!

    Thanks Melissa

  43. PK says:

    Hi,

    I was wondering if you could help me with a piece of VBA that I wrote picking pieces from different websites (I have 0 exp. with VBA).
    My data has two columns: Column A is stock price and Column B is option position, there are 360 rows of data in both columns.
    Now I want to produce a Scatter chart with X axis having values (1,2,3....360) and Y axis has stock price (series 1) and option position (series 2). As you can see I want a delay between each plot after each x value and hence Im using VBA instead I would have directly done it using excel. Can you help please... below is what I has written so far... this works for columns but scatter plot does not work...

    +++++++++++++++++++++++++++++++
    Sub Doublecharts()
    Dim chart1 As ChartObject
    Dim chart2 As ChartObject

    Dim i As Integer
    Dim j As Integer

    Set chart1 = ActiveSheet.ChartObjects.Add(Left:=100, Top:=50, _
    Width:=400, Height:=300)

    Set chart2 = ActiveSheet.ChartObjects.Add(Left:=500, Top:=50, _
    Width:=400, Height:=300)

    chart1.Chart.ChartType = xlColumnClustered

    chart2.Chart.ChartType = xlXYScatterSmoothNoMarkers

    For j = 1 To 2
    For i = 2 To 6

    chart1.Chart.SetSourceData Source:=Sheets("sheet1").Range(Sheets("sheet1").Cells(2, 1), Sheets("sheet1").Cells(i, 2)), PlotBy:=xlColumns
    chart2.Chart.SetSourceData Source:=Sheets("sheet1").Range(Sheets("sheet1").Cells(2, 1), Sheets("sheet1").Cells(i, j)), PlotBy:=xlColumns

    Application.Wait (Now + (1 / (24 * 60 * 60#)))

    Next i
    Next j

    End Sub
    +++++++++++++++++++++++++++++++

  44. Danijela says:

    You are great!
    Although it looks easy, it's cool to see some1 explained that also. Thanks!

  45. Lauren says:

    Good evening Chadoo,

    Our company has hired 6 new interns. My boss wants me to create a burn chart to make sure we don't go over budgets costs by calculating pay and the hours they are putting in. Can you please show me how I could make one for multiple employees?

Leave a Reply