Excel Combination Charts (Tables of Contents)
Combination Chart in Excel
In Excel, we have the Combo Chart option, which can club two chart types, Column Clusters Chart and Line Chart, to get valuable output, but for this, we must have 2 data sets values which we can plot on it. First, create a Combo Chart, select the data we want to plot, and then choose Combo chart type from the Insert menu section. There we can find a Combo chart of Column – Line, Column Cluster – Line and with Area Chart type. Under the Recommended Chart option, we can even change the type axis for any of the data we have selected, and also we can change the type of Chart type as well.
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 on it. One is a 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.
How to Create a Combination of 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 the 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 the 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 about 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 its 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 profit fluctuated due to variance in COGS. 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: The dataset should look like this.
Step 2: Select the data and insert a 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 a clustered chart only. Now don’t know where the temperature bar is. To select the column bar, right-click and select Change Series Chart Type.
Step 5: Under Combo, change the Avg Temperature chart type to the Line chart and also make this a 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 the 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
- A 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 isn’t easy 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 a downloadable excel template. You may also look at these useful charts in excel –