Excel Combination Charts (Tables of Contents)
Combination Chart in Excel
Combination charts are very much part of all the end reports when people are comparing two different numbers or data points which are related to each other. For an example, we usually compare Sales vs COGS, Sales vs Profit etc…
Like this, we usually compare two things. Presenting the same data in a single chart with two different kinds of charts are called combination charts in Excel.
Combination charts are not inbuilt in Excel 2007 and earlier versions. Combo Charts is an in-built tool from excel 2013 onwards. You can see the combination chart under the INSERT tab.
Observe the icon of the chart carefully. It has two different charts in it. One is column chart and another one is a line chart. So a combination of two different charts is called combination charts in Excel. These charts are very helpful to compare two things at a time.
Note: Due to non-availability of combination chart in excel I am using other technique to build combination chart so that all the excel version users can practice with me.
How to Create a Combination Charts in Excel?
Excel Combination Charts is very simple and easy to create. Let us now see how to create Combination Charts in Excel with the help of some examples.
Excel Combo Chart – Example #1
In order to create an excel combination chart, I have a few years’ sales, cost, and profit data in an excel sheet.
Step 1: Select the entire data range and insert a simple column chart.
Step 2: Now your chart should look like this.
Step 3: Right click on the chart and select Select Data.
Step 4: Firstly, remove Year from the below window.
Step 5: Now click on EDIT on the right-hand side i.e. horizontal axis labels.
Step 6: Select Year list here. Click on OK to complete the process.
Step 7: Now your chart should look like this.
Step 8: Select the bar of COGS. Right-click and select Change Series Chart Type.
Step 9: Now you will see Change Chart Type dialogue box. Here select the option COMBO. (Excel must have selected this option by default).
Step 10: In the same window change the chart type of both COGS & Profit to Line chart as shown in the below image. Click on OK.
Step 11: It will give us the below combination chart.
You can change the line color and column bar colors according to your wish. I have changed according to my preferences and combo charts looking like this in Excel.
Interpretation of the Chart
Now you must be thinking what the use of the combination chart is. Below are some of the important observations of the above chart.
- Sales have fluctuated throughout the last 10 years.
- It has reached the peak sale in the year 2012 i.e. 35 million.
- In terms of profit margin, the year 2008 has given a profit percentage of 66.50% which highest in terms of percentage.
- From the year 2013 to 2016 revenue is constant but due to variance in COGS profit fluctuated. In the year 2013 COGS is more and profit comes down drastically.
Excel Combo Charts – Example #2
In the previous example, we have seen the combination of sales, COGS, and profit margin. In this example, I will show you the sale of leather jackets in the last 12 months with the temperate data.
Data includes the sales of leather jackets against the average temperature data for the last 12 months. We need to plot a graph see when are the highest sales happened and find the reasons.
Step 1: Dataset should look like this.
Step 2: Select the data and insert new column chart.
Step 3: Now your chart should like this.
Step 4: Both No. of Jackets sold and Average Temperature are data plotted as clustered chart only. Now don’t know where the temperature bar is. So select the column bar right click and select Change Series Chart Type.
Step 5: Under Combo, change the Avg Temperature chart type to Line chart and also make this as secondary axis and then Click on Ok to complete the process.
Step 6: We have our combination chart ready.
If you notice we have Vertical axis labels here. Once is representing No. of Jackets Sold and other one is Temperature data. Since we have selected the Temperature chart type as a secondary axis it has created a new vertical axis.
Interpretation of the Chart
It is clear that as the temperature decreases the sales of jackets increases. Especially in the months Nov, Dec, Jan, and Feb the sales of Jackets increased at a good rate. The reason behind this is in those four months the temperature is 16, 14, 15, and 12 respectively.
You must be thinking in Feb month even though the temperature is low as compared to other 3 months, jacket sale is very less. The reason behind this is most of the people are already ready with the jackets by the time-temperature reduces to the lowest of all.
Things to Remember
- Combination chart can interpret two different datasets in Excel.
- Combo Chart shows how one data set is impacting the other data set in Excel.
- In case of huge number differentiation always select any one of the data set as a secondary axis. Otherwise, it is difficult to see the differences.
This has been a guide to Combination Charts in Excel. Here we discuss how to create Combination Charts in excel along with excel examples and downloadable excel template. You may also look at these useful charts in excel –