For example, suppose we have a year, revenue, and product sales in the percentage dataset. We want to show how the same chart’s revenues and sales have changed. In such cases, we can plot the year on X-axis. In contrast, revenue and product sales in the percentage on the two Y-axis in combination charts in Excel can help present and compare two different data sets related to each other in a single chart. 

What is a Combination (Combo) Chart in Excel?

The combination charts in Excel let you represent two different data tables related to each other. So, for example, there would be a primary X-axis and Y-axis and an additional secondary Y-axis to help provide a broader understanding of one chart.

  • The combination charts in Excel provide a comparative analysis of the two graphs of different categories and the mixed type of data, enabling the user to view and highlight higher and lower values within the charts.A combo chart in Excel is the best feature that enables a user to summarize large data sets with multiple data tables and systematically illustrate the numbers in one chart.The combination charts in Excel are also called 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

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 arriving at a combination chart in Excel.

  • First, we must select the whole table to be plotted on the chart. As shown in the screenshot below, we must select the data table, go to the “Insert” tab in the ribbon, and select “Combo Charts,” as shown in the red rectangle and right arrow. Now, we must select the “Clustered Column” Excel chart in the options provided in the “Combo Charts” dropdown. Once the clustered chart is selected, the combo chart will be ready for display and illustration. If the chart needs to be changed to a different chart, we can right-click on the graphs and select “Change Chart Type,” as shown in the screenshot below. In the “Change Chart Type” window, we must select the data table parameters plotted on the secondary Y-axis by clicking the box with a tick mark. For the current example, profit and expenses (in dollar value) are plotted on the primary Y-axis, and the headcounts are plotted on the secondary Y-axis.The screenshot below shows that the profit and expense parameters are changed to stacked bar charts, and headcount is selected as a line chart on the secondary Y-axis. Once the selections are made, we must click “OK.” Click on the “Chart title” and rename the chart to the required title to change the name, as shown below. To change the background for the charts, click on the chart so that the “Design” and “Format” tabs in the ribbon will appear, as shown in the red rectangle. Now, choose the required style for the chart from the “Design” tab. Below is the completed chart for this example:-

For the current example, profit and expenses (in dollar value) are plotted on the primary Y-axis, and the headcounts are plotted on the secondary Y-axis.The screenshot below shows that the profit and expense parameters are changed to stacked bar charts, and headcount is selected as a line chart on the secondary Y-axis.

Below is the completed chart for this example:-

Step 1:- First, we must select the data table prepared, then go to the “Insert” tab in the ribbon, click on “Combo,” and then select the “Clustered Column – Line.”

Once the clustered chart is selected, the combo chart will 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 for-profit and a line graph for margin. Now, we must choose the line graph. But we noticed that the margin data in the chart is not visible. Thereby, we must go to the “Format” tab in the ribbon and click on the dropdown as shown in the red arrow towards the left, then select “Series Margin.”

Now, the line graph (orange graph) is selected.

Step 3:- Now, we must 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. read more and select “Format Data Series.”

Step 4:- In the “Format Data Series” window, we must 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. read more. It 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.read more.

Step 5:- Next, we must go to the “Series Options” dropdown and select “Secondary Vertical (Value) Axis” to adjust the scale in the secondary Y-axis.

Step 6:- Now, choose the “Axis Options.”

Step 7:- Change the maximum bounds and major units as per the requirement. For example, 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 equally distant measure of 25%.

Below is the final combination chart in Excel. For example, 2.

Pros of Combination Charts in Excel

  • It can have two or more displays in one chart, providing  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.read more showcases how we can illustrate the charts on both the Y-axis and talk to each other and their influences on each other based on the X-axis significantly.It could represent using different charts to display primary and secondary Y-axis data. For example, line and bar charts, bar and Gantt charts in excelGantt Charts In ExcelGantt chart is a type of project manager chart that shows the start and completion time of a project, as well as the time it takes to complete each step. The representation in this chart is shown in bars on the horizontal axis.read more, etc.Best used for critical comparison purposes, which could help further analysis and reporting.The combination charts also show how we can portray the data table containing a wide range in both Y-axes in the same chart.It can also accommodate different data tables Data TablesA data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section.read more and does not necessarily 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 making it 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 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 article is a guide to Combination Charts in Excel. We discuss creating combo charts in Excel, practical examples, and a downloadable template. You may learn more about Excel from the following articles: –

  • Gantt Chart ExampleStacked Column Chart in ExcelWaterfall Chart in ExcelColumn Chart in excel