Combination chart or most commonly known as combo chart in excel is a combination of two or more than two different charts in excel, to make such types of combo charts we can use the option of creating a combo chart from the insert menu in the chart tab also in order to combine two charts we must have two different data sets but one common field to combine.
What is a Combination (Combo) Chart in Excel?
Combination charts in Excel let you represent two different data tables that are related to each other, in which there would be a primary x-axis and y-axis and an additional secondary y-axis to help provide broader understanding in one chart.
- The combination charts in excel give a comparative analysis of the two graphs of different categories and also for the mixed type of data, thereby enabling the user to view and highlight higher and lower values within the charts.
- Combo Chart in excel is the best feature that enables a user to summarize large data sets with multiple data tables and illustrate the numbers in a systematic manner in one chart.
- The combination charts in Excel are also called as combo charts in the newer version of excel. Typically the new feature for combo charts in Excel is now available from 2013 and later versions.
How to Create a Combo Chart in Excel?
Below are the examples of the Combination Chart in Excel (Combo).
Combination Charts (Combo) in Excel Examples #1
Consider the below example wherein we have profit, expenses, and headcount to be plotted in the same chart by month. Thereby we would have profit and expenses on the primary y-axis, headcounts on the secondary y-axis, and months on the x-axis.
- Select the entire table to be plotted on the chart.
- As shown in the below screenshot, select the data table, then go to the insert tab in the ribbon and select combo charts as shown in the red rectangle and right arrow.
- Now select the clustered Column excel chart in the options provided in the combo charts dropdown.
- Once the clustered chart is selected, the combo chart would be ready for display and illustration.
- If the chart needs to be changed to a different chart, then right-click on the graphs and select “Change Chart Type,” as shown in the below screenshot.
- In the Change Chart Type window, select the data table parameters to be plotted on the secondary y-axis by clicking the box by a tick mark.
For the current example, profit and expenses (as they are in dollar value) are considered to be plotted in the primary y-axis, and the headcount will be plotted in the secondary y-axis.
As can be seen in the below screenshot, the profit and expense parameters are changed to stacked bar charts, and headcount is selected as a line chart in the secondary y-axis.
- Once the selections are made, click, OK.
- To change the name, click on the “Chart title” and rename the chart to the required title, as shown in the below screenshot.
- To change the background for the charts, click on the chart so that the “Design” and “Format” tab in the ribbon will appear, as shown in the red rectangle. Now choose a required style for the chart from the Design tab.
Below is the completed chart for this example:-
Combination Charts (Combo) in Excel Example #2
Consider the below table consisting of region-wise profit and margin data. This example explains an alternate approach to arrive at a combination chart in Excel.
Step 1:- First, select the data table prepared, then go to the insert tab in the ribbon, click on Combo, and then select clustered column – line.
Once the clustered chart is selected, the combo chart would be ready for display and illustration.
Step 2:- Once the clustered column line is selected, the below graph will appear with a bar graph for-profit and a line graph for margin. Now we select the line graph, but we notice that the margin data in the chart is not visible, thereby we go to the Format tab in the ribbon and then click on dropdown as shown in the red arrow towards the left, then select “Series Margin.”
Now the line graph (Orange graph) is selected.
Step 3:- Right-click on the selected line graph in excelLine Graph In ExcelLine Graphs/Charts in Excels are visuals to track trends or show changes over a given period & they are pretty helpful for forecasting data. They may include 1 line for a single data set or multiple lines to compare different data sets. and select “Format Data Series.”
Step 4:- In the Format Data Series window, go to “Series Option” and click on the Secondary axis radio buttonRadio ButtonIn Excel, radio buttons or options buttons record a user's input. They can be found in the developer's tab's insert section. . This will bring the margin data on the secondary y-axis.
Below is the screenshot after selecting the secondary axisSecondary AxisThe secondary axis is the other axis that is used to denote different data sets that cannot be displayed on a single axis. The primary axis, for example, depicts time, whereas the secondary axis displays production..
Step 5:- Next, go to Series Option dropdown and select Secondary vertical values in order to adjust the scale in the secondary y-axis.
Step 6:- Now choose the Axis Option.
Step 7:- Change the maximum bounds and major units as per the requirement. Here the maximum bound is selected to be 1, which would be 100%, and a major unit is changed to 0.25, which would have an equidistant measure on 25%.
Below is the final combination chart in Excel; for example 2.
Pros of Combination Charts in Excel
- It can have two or more different displays in one chart, which provides concise information for better presentation and understanding.
- The combo chart in ExcelThe Combo Chart In ExcelExcel Combo Charts combine different chart types to display different or the same set of data that is related to each other. Instead of the typical one Y-Axis, the Excel Combo Chart has two. showcases how the charts on both the y-axis talk to each other and their influences on each other on the basis of the x-axis can be illustrated in a significant manner.
- It could represent using different types of charts to display primary and secondary y-axis data, for example, line and bar charts, bar and Gantt charts in excel, etc.
- Best used for critical comparison purposes, which could help in further analysis and reporting.
- The combination charts also show how the data table containing a wide range can be portrayed in both y-axes in the same chart.
- Can also accommodate a different kind of data tables and not necessarily that they need to be correlated.
Cons of Combo Charts in Excel
- The combination chart in excel can sometimes be more complex and complicated as the number of graphs on the charts increase, thereby difficult to differentiate and understand.
- Too many graphs may make it difficult to interpret the parameters.
Things to Remember
- Choosing the right data tables with parameters for the chart is necessary in order to make sense.
- If the data table consists of large data, then make sure to distance the scale in axis, else it may get crowded.
This has been a guide to Combination Charts in Excel. Here we discuss how to create Combo Charts in Excel along with practical examples and a downloadable template. You may learn more about excel from the following articles –