Combo Chart in Excel (Table of Contents)
Excel Combination Chart
You might have visualized your data with some of the graphical techniques most of the time in your reports. As it is a nice way to do so and gives a quick analytical overview of the data. However, have you faced any such scenario’s where you have to use a combination of two charts (for example, bar chart and line chart) in a single graph to give a more crisp idea about the data? I bet you must have come with such scenario’s. Combining two or more charts in a graph is not an easy task, but we will make it so throughout this article and assure you that, you will be able to do it by your own with ease once you go through this article.
Definition of Combo Chart in Excel
Combo chart, as the word suggests is the combination of two graphs on the same chart to make it more understandable and visually more appealing. It allows you to represent two different datasets (which are related to each other) on the same chart. In the usual chart, we have two axis X-axis and Y-axis where on Y-axis you have series data and on X-axis you have categories. However, when you use a combination chart, you’ll have two series or in other words two Y-axis for the same X-axis or categories.
Example to Create Combo Chart in Excel
In the below example, we are going to explore how to create a combination of Bar graph and Line graph on the same chart.
Suppose we are running a manufacturing company and selling our products across the globe. Following are the Sales, Margin and Margin% values from January to August (Year 2019) for all the products we sold. See the image below.
We wanted to plot a graph of same in excel so our management can have a better understanding of how we are moving in the business around the globe in the past 8 months. Bar chart seems to be the nicest way to make comparisons.
Step 1 – Select all the data spread across column A to D (Along with headers). Navigate to “Insert” Tab and under Charts section, click on “Column or Bar Chart” icon.
Step 2 – Once you click on the Insert Column or Bar Chart icon, you will be able to see multiple graph options using which your data can be represented. Select the Clustered Column Chart option from the list.
4.8 (1,911 ratings)
View Course
Step 3 – Once you click on the Clustered Column Chart option, you’ll be able to see a chart like this in your working excel sheet.
You can directly insert combination chart as well, by using Recommended Charts option present under Chart section. However, we wanted this to be for all the users (Some Excel users using older version might not have the recommended charts option), we are showing it step by step.
Here, in the graph above you can see that the margin percentage is not clearly visible. Rightly so, because they have values really very small in comparison to that off Sales and Margin values. However, we wanted to make the Margin% visible to management as well. So that they will have a better understanding of movements happening. For this, we can add a Line Chart under this one and mention Margin% on the line chart. Follow the steps as below to create a combination of two charts:
Step 4 – Select series bars and right-click on any one of those. You’ll see a series of options. Out of those, select Change Series Chart Type… option.
Step 5 – As soon as you click on Change Series Chart Type… option, a Change Chart Type window will pop-up as shown in below screenshot.
Step 6 – In the Change Chart Type window, select Combo as a category (as we want to combine two charts). Most of the times, it will be selected by default for you by system, using its own intelligence.
Step 7 – Inside Combo, you’ll see different combination options (ideally four). Out of those select the second option which is Clustered Column – Line on Secondary Axis chart. This option of the chart enables the secondary axis visible on the right- hand side of the graph. See screenshot below.
Step 8 – Click the “OK “ button and you’ll be able to see the updated combined graph as below.
Now, the Margin% looks like having more relevance in that graph. Because it is now added as separate series values (remember the secondary Y-axis on the right-hand side of the graph). As the series values have been plotted on a separate axis, they are now not relevant to the values of primary series values (Y-axis). Therefore, they can now be seen more precisely and visually as well as technically more relevant.
Step 9 – Add the Chart Title as “Combination Chart” under the Chart Title section under combo graph.
You can use more customizations like changing the design of the graph, adding axis labels or changing series bars and series line colors, changing widths, etc. to make your graph visually more pleasant. All these options are present under the Design tab.
This is how we can create Combo Chart under Excel when we have different data for the same categories.
This is from this article. Let’s wrap the things up with some points to be remembered.
Things to Remember
- As it is a hybrid of two or more charts, it saves time as well as space to create two separate graphs. However, at the same time, it might confuse the user due to complexity which gets added due to the combination.
- The combination chart allows to plot two different series values across the same categories, which is more decent because one can compare two totally different values for the same categories in a single graph.
- Though it seems easier to generate in excel, it needs to be precise and non-confusing to the user. So that they can have better visualization for two different values for the same categories.
- You can use different combinations of charts other than Bar and Line charts to make a better combination. Ex. Gantt & Bar chart.
Recommended Articles
This is a guide to Combo Chart in Excel. Here we discuss How to create Combo Chart in excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
