How to Create a Combination Chart in Excel (4 Effective Examples)

Get FREE Advanced Excel Exercises with Solutions!

When you have more than one data series that you want to plot on the same chart, you can create a combination chart in Excel in order to deliver a visual representation of each of the data series. Using this format, you can easily choose the type of chart, best suited to the respective data series. There are some best practices to follow to create a combination chart in Excel. Let’s get started learning how to create a combination chart in Excel with 4 effective examples.


How to Create a Combination Chart in Excel: 4 Effective Examples

To describe the examples, here is a dataset showing the information of Sales Amount and Profit for the months January-June.

How to Create a Combination Chart in Excel


Now, let’s create a combination chart in Excel out of this dataset.

1. Create a Combination Chart with Clustered Column

In this first stage, we will learn to create a combination chart with the help of Clustered Column-Line type. Let’s follow the process below:

  • In the beginning, select cell range B4:D10.
  • Next, go to the Insert tab and select Insert Combo Chart from the Charts section.

Create a Combination Chart with Clustered Column

  • Then, choose the type Clustered Column-Line from the options.

  • Initially, the chart looks like this:

  • Now, double-click on the Profit line in the chart.
  • Following, make it Secondary Axis from the Format Data Series panel.

Create a Combination Chart with Clustered Column

  • After this, mark ticked the options Primary Vertical and Secondary Vertical in the Chart Elements.

Create a Combination Chart with Clustered Column

  • Finally, here is the combination chart based on the dataset.

Read More: How to Combine Two Graphs in Excel


2. Make Combo Chart with Automatic Secondary Axis

This process will benefit you by creating a combo chart with different axes automatically. Let’s see how it works.

  • Firstly, select cell range B4:D10.
  • Next, go to the Insert tab and select Insert Combo Chart from the Charts section.

Make Combo Chart with Automatic Secondary Axis

  • Then, click on the drop-down arrow and choose Clustered Column – Line on Secondary Axis.

Make Combo Chart with Automatic Secondary Axis

  • After that, format the chart by choosing a Style as shown below.

Make Combo Chart with Automatic Secondary Axis

  • Lastly, add and format Data Labels as Inside Base in order to provide further clarity and differentiate between the two series, as shown below.

  • That’s it, you have got your required combination chart.

Read More: How to Create Column and Line Chart Combo in Excel


3. Create a Customized Combination Chart in Excel

We can also create a combination chart by customization. Simply follow the process below.

  • First, select cell range B4:D10.
  • Then, click on the Insert Combo Chart option in the Insert tab.
  • Here, select the Create Custom Combo Chart.

Create a Customized Combination Chart in Excel

  • Now, select Custom Combination from the Custom section in the Insert Chart dialogue box.

Create a Customized Combination Chart in Excel

  • After this, define the Chart Type for each Series Name as shown below.

  • Following, mark Profit as the Secondary Axis.

Create a Customized Combination Chart in Excel

  • Finally, press OK and you will see the desired Combination Chart.

Read More: Create Clustered Stacked Column Combo Chart with Lines in Excel


4. Convert 2D Bar Chart into a Combo Chart

In this last example, we will convert a 2D Bar Chart into a Combination Chart. This may sound tricky but the process is really easy. Let’s check it out.

  • In the beginning, select cell range B4:D10.
  • Then, choose Insert Column or Bar Chart from the Chart group under the Insert tab.

Convert 2D Bar Chart into a Combo Chart

  • In this section, select 2D Clustered Column.

Convert 2D Bar Chart into a Combo Chart

  • So far, the chart looks like this.

  • As you can see, the second series Profit is not visible in the chart. Because it overlapped with the Sales Amount series.
  • To resolve this, right-click on the first series and choose Change Series Chart Type from the Context Menu.

Convert 2D Bar Chart into a Combo Chart

  • Following, define the Profit Chart Type as Line with Markers.
  • Along with it, make it a Secondary Axis by clicking the box as shown below.

  • That’s it, you have successfully converted the 2D Bar Chart to a Combination Chart.


Things to Remember

  • It is best to use combination charts with at most two data series only, or else they become too cluttered and it becomes difficult to differentiate between the different data series and pinpoint the axis, which is relevant to said data series.
  • A combination chart is useful when the different data series have vastly differing units.
  • Do not choose a line chart for a data series that actually needs an XY scatter plot.
  • If the data scales are vastly different, add data labels to the chart in order to ensure that clarity is conveyed.

Download Practice Workbook

Get this sample file for practicing by yourself.


Conclusion

Finally, we have come to the conclusion of our article. Here we discussed some processes of how to create a combination chart in Excel with 4 effective examples. Let us know your suggestions in the comment box.


Related Article


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Taryn Nefdt
Taryn Nefdt

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo