What Is Combo Chart In Excel?
A Combo chart in Excel combines two or more chart types in a single chart, making it easier to understand data. It is a combination chart that helps graphically represent and compare different data sets based on a common field.
An Excel combo chart is practical when the data points vary widely across massive data series or when we have to work with mixed data types.
For example, the table below shows a company’s yearly sales report along with the profits earned in each month.
The Excel combo chart can help us understand data variations in the above table graphically.
Here we can see the Excel combo chart secondary axis along with the primary axes. So, the X-axis denotes the common field, and the two Y-axes represent the two different data series.
Thus, we can interpret from the above Excel combo chart that the company’s profit increases with respect to the months with more days.
The two chart types used in the above illustration are Column and Line. Similarly, depending on our Excel combo chart data series, we can choose the best-suitable chart types.
For instance, while working with quarterly sales data, we can select Stacked Column and Line charts.
Table of contents
- An Excel combo chart enables users to use two or more chart types to represent data sets of mixed data types in one chart.
- The combination chart helps compare different data series based on a common factor.
- We can access the option from the Chart area of the Insert tab. And once we have the combo chart plotted on our worksheet, we can use the Format Chart Area and Format Data Series panes to change our chart format.
- Combination charts can help us graphically present a consolidated data table more usefully.
How To Create Combo Chart In Excel?
The steps used to create an Excel combo chart are as follows:
- First, choose the required data series we wish to show and analyze in a combination chart.
- Next, choose the Combo Chart type from the Charts group under the Insert tab.
- Then, select the required Excel combo chart template. Instead of the available styles, we can also create a customized combination chart using the Create Custom Combo Chart
- While working with customized combo charts, we can set the required chart types and axes for our data series. On the other hand, if we choose the available Excel combo chart template, it will automatically create a graph.
Let us understand the steps to create a combo chart in Excel with an example.
Consider the below table showing the weekly revenue margin and various defect values in percentages. Now, we need to create a combo chart for the given data.
The steps used to create a combination chart for the given Excel combo chart data are as follows:
Step 1: First, we should choose the entire table as we need an Excel combo chart for all the data points.
So, select Insert > Combo Chart > Create Custom Combo Chart.
Step 2: As soon as we click, the Insert Chart window appears. Here, set the required graph types for each data series.
For our example, let us set the first series as a Line chart and the rest as Stacked Column (available under the Column chart option).
Step 3: Next, check the Secondary Axis box against each data series, except for the first series, Revenue Margin in %, for enabling the Excel combo chart secondary axis.
Remember, choosing the secondary axis will allow us to show the different defect types in percentage for each week in a Stacked Column chart.
Step 4: Click OK in the Insert Chart window to view the required combo chart.
Now, we need to make a few aesthetic changes to make the graph look complete and professional.
Step 5: So, click on the chart title and set an appropriate heading.
Step 6: Finally, click on the chart area and select the Chart Elements (‘+’ icon).
Then, check the box against Axis Titles to name each axis in the graph. Now, we can set the axis titles just like how we updated the chart title in the previous step.
Once we update the axis titles, our final Excel combo chart will be:
Clearly, we will see the legends below the graph, which will help us analyze and understand the trend easily.
Also, when we click on the chart area, the Design and Format tabs get enabled in the ribbon.
We can choose our required formatting options from the two tabs to get the desired combination chart.
Similarly, we can create excel combo charts in excel with ease.
Let us see a few more examples to understand how to use an Excel combo chart in the best way possible.
Example #1: Dynamic Consolidate with Links to Source Data
In this Excel combo chart example, we will first use the Consolidate option in the Data tab to dynamically consolidate data in different worksheets with links to the source data sets. Then, we will use the combo chart to show the data graphically.
Suppose the sales data for a company’s branch offices across three US states are available in Branch Office 1, Branch Office 2, Branch Office 3, and Branch Office 4 worksheets. Using Consolidated Data, we have to create a combination chart for the sales figures of the company’s branch offices in a worksheet.
The steps used to create an Excel combo chart using Consolidate Data are as follows:
Step 1: Create a template in the worksheet Consolidated Data to display the final result in the required format.
Step 2: Choose cell B2 and select Data > Consolidate.
Step 3: In the Consolidate window, choose the Function as Sum. In the Reference box, enter the cell reference to the required cell range from Branch Office 1 worksheet. Then, click on Add to add the reference to the All references list.
Step 4: Add the required cell references from the remaining worksheets, one after the other.
Once we click OK, the table in the Consolidated Data worksheet will appear as follows:
Step 5: Click on cell B2, go to Data > Consolidate, and check the box against Create links to source data in the Consolidate window.
Step 6: Click OK to get the below output in the Consolidated Data worksheet.
When we expand the groups, we will see the source data from Branch Office 1, Branch Office 2, Branch Office 3, and Branch Office 4 worksheets for branch offices in each state.
Please Note: The advantage of this method is that if we make changes in the source data worksheets, the changes will automatically get reflected in the Consolidated Data worksheet.
We can now create an combo chart for the above consolidated Sales-Profit Margin table.
Step 1: Select the entire table and choose Combo > Create Custom Combo Chart from the Insert tab.
Step 2: Choose the required chart types for the data series, and check the Secondary Axis box against the series Profit Margin in %.
Step 3: Click OK to get the below combo chart.
Also, update the Chart Title and add the Axis Titles.
Step 4: Double-click on the data point to change its color in the Format Data Series pane. For example, let us change the bar color from Blue to Green.
Click on Fill > Color to update the color.
The final Excel combo chart will be:
Please Note: If we make any changes in the source data, the graph will get updated automatically.
For example, let us change the profit margin for Florida to 10% in the Branch Office 1 worksheet. With these steps, we will get the updated Excel combo chart in the Consolidated Data sheet.
Therefore, we can create excel combo chart using dynamic consolidate with links to source data.
Example #2: Consolidate Data to Blank Worksheet
In the previous Excel combo chart example, we created a template in the target worksheet to display the consolidated data.
However, here we will see how to perform the same task but using a blank target worksheet instead of a worksheet with a template.
Suppose we have the marketing costs and profit margins for training domains in three worksheets, Ohio, Texas, and Utah. Also, we have an empty worksheet, Final Data. Let us learn the steps used to consolidate data to blank worksheet.
The steps used to consolidate data are as follows:
Step 1: To begin with, choose cell A1 in the Final Data worksheet and select Data > Consolidate.
Step 2: Next, choose SUM as the Function and select the entire table and column headings as the source data from the Ohio worksheet. Then, click Add.
Step 3: Now, add the cell references of the tables in the Utah and Texas worksheets.
Step 4: Also, check the boxes against the Top row, Left column, and Create links to source data.
While we get the required consolidated data, we will have the table with the required column headings.
Step 5: Click OK to view the final table in the blank worksheet, Final Data.
We have obtained the final table in the blank worksheet.
Now, let us learn the steps used to create combination chart for this table.
Step 1: Choose the entire table in the Final Data worksheet and click on Insert > Combo chart > Create Custom Combo Chart.
Step 2: Choose the required chart types for the data series and check the Secondary Axis box for the data series Profit Margin in %.
Step 3: Click OK to get the Excel combo chart for the consolidated table in the Final Data worksheet.
After applying the required formats, the final combo chart will be:
Similarly, we can create excel combo chart with consolidate data in a blank worksheet.
Important Things To Note
- An Excel combo chart is a hybrid chart that lets users interpret different data sets.
- We can choose a different chart type for each data series. For example, Excel offers options such as Column, Line, Bar, Area, and Pie.
- If the data set is massive, we should ensure to adjust the scale and choose the correct data series as a secondary axis to make the graph interpretation more straightforward.
- We should choose the correct data range when using combo charts for consolidated data tables while preparing the table. Otherwise, the final graph can be incorrect.
Frequently Asked Questions
We can do a combo chart in Excel by selecting the data sets we wish to represent graphically for analyzing the fluctuations. Then, choose Insert > Combo charts in the Charts group. Finally, we can format it using the Design and Format tabs to make it more presentable.
We can create a custom combination combo chart in Excel by choosing Insert > Combo > Create Custom Combo Chart option for the selected data series.
There will be no combo charts in Excel if we use Office suite for Mac or an Excel version before 2016.
We can create combo chart in Excel with multiple series using the below steps.
Consider the table below with annual profit-loss data. As we can see, it has multiple data series, Profits in $ and Losses in $.
The steps used to create Excel combo chart with multiple series are as follows:
Step 1: Select the entire table and choose the Combo > Create Custom Combo Chart option under the Insert tab.
Step 2: Choose the required chart types for the two data series and check the Secondary Axis box against the second data series, i.e., Losses in $.
Step 3: Click OK to view the combo chart.
Step 4: Update the Chart Title and add Axis Titles to complete the chart. The final combo chart for multiple series will appear as follows:
Likewise, we can create excel combo chart with multiple entries.
This article must be helpful to understand the Excel Combo Chart, with its formula and examples. You can download the template here to use it instantly.
This has been a guide to Combo Chart in Excel. Here we discuss steps to create combo chart with examples along with a downloadable excel template. You can learn more from the following articles –