How to Create Combination Charts in Excel – Step-by-Step Tutorial

Combination charts in Excel let you present and compare two different data-sets that are related to each other (in a single chart).

When you create a regular chart in Excel, it usually has only one X-axis and one Y-axis. But with combination charts, you can have two Y-axis, which allows you to have two different type of data points in the same chart.

For example, you may be interested in plotting the annual revenue numbers of a company, and at the same time, also be able to show how the profit margin has changed.

A combination chart (as shown below) is a good way of doing this in Excel.

Combination Charts in Excel - final result

You can see that a revenue number are in thousands and are way higher than the profit margin numbers (which is in %). By adding a secondary Y-axis, we can plot the profit margin numbers separately (and still be able to plot both in the same chart).

Creating a Combination Chart in Excel 2013/2016

Suppose I have the data set as shown below and I want to plot both the revenue and profit margin numbers in the same chart.

Combination Charts in Excel - Dataset

To create this combination chart, I first need to create a regular chart where I have all the above data plotted on it.

Below are the steps to create a regular chart using the above data (the snapshots are of Excel 2016):

  1. Select the Revenue and Profit Margin data (B1:C6 in this example).
  2. Click the Insert tab.Combination Charts in Excel - Insert Tab
  3. In the Charts group, click on the ‘Insert Column Chart' icon.Combination Charts in Excel - Insert Column or Bar Chart
  4. Click on Clustered Column chart. This will insert the chart in the worksheet area.Excel Combination Charts - click on clustered column chart
  5. With the Chart Selected, go to the Design tab and click on Select Data.Combo Charts in Excel - click on Select Data in Design tab
  6. In the ‘Select Data Source' dialog box, click on Edit option (below the ‘Horizontal (Category) Axis Labels').Combo Charts in Excel - click on Edit button
  7. Select years that you want to plot in the chart (A2:A6 in this example).
  8. Click OK.

The above steps would give you a chart that has revenue and profit margin plotted as a clustered column chart. If you can't see the Profit Margin bar in the chart, it's because the value of Profit Margin is very less as compared to the Revenue value (but it's there as we can see it's listed in the legend in orange color).

Regular clustered column Chart

Now to create a combination chart from this clustered column chart, follow the below steps:

  1. Right-click on any of the bars.
  2. Click on ‘Change Series Chart Type'.Change Series chart type to create a combo chart
  3. In the Change Chart Type dialog box, make sure Combo category is selected (which it should be by default).Combo is selected in the dialog box
  4. Click on the drop-down for Profit Margin.Click the profit margin drop down
  5. In the drop-down, click on ‘Line with Markers'.Combo chart - line with markers
  6. Check the Secondary Axis checkbox for Profit Margin.Plot line with markers on a secondary axis to make it a combo chart
  7. Click OK.

The above steps would give you the combination chart (also called combo chart) as shown below.

Combination Chart in Excel - final result

You can now further customize the chart (such as change the title, remove the grid lines, etc.)

Another good use case of creating combination charts is when you have to show actual vs target values in the same chart. You can create a simple clustered column chart, or create something more fancy by converting one bar into markers. Click here to read how to create an Actual vs Target combination charts in Excel.

Creating Combination Charts in Excel 2010

While the first part of creating a chart is the same in all versions of Excel, converting that chart into a combination chart is done a bit differently in Excel 2010.

Below are the steps to convert a regular clustered column chart into a combo chart in Excel 2010:

  1. Click on any of the Profit margin bars. This will select all bars for profit margin.
  2. Right-click and select Format Data Series.

Combination Charts in Excel - Select Format Data Series

  1. In the Format Data Series dialogue box, select Secondary Axis (in the Plot Series On group)
    • This will plot Profit Margin Data in a secondary Axis. You would be able to see a vertical axis on the right of the chart.

Combo Charts in Excel - Secondary Axis Selected

  1. Right-click and select Change Series Chart Type

Combination Charts in Excel - Select Change Series Chart Type Excel

  1. In the Change Chart Type dialogue box, select Line with Markers option.

Combination Charts in Excel - Select Different chart Excel

  1. That's It. You would have the chart. Format it the way you want.

You May Also Like the Following Excel Charting Tutorials:

Online Excel Tips Tutorials Training and Videos

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)