Tutorial Details
Downloadable Files: Excel File
Getting Started in Excel
How to Enter, Manage, and Find Data in Excel
Introduction to Formatting in Excel
Introduction to Formulas and Functions in Excel
Creating Charts in Excel

Combine Multiple Chart Types in Excel to Make Powerful Charts

Add to Favorites
Author:

In this tutorial I am going to show you how to combine multiple chart types to create a single chart in Excel.

This will help you understand how to create more powerful charting tools for reporting and analyzing data.

To start, set up your chart with 2 data series:

Initially this doesnt look great as the values for the number of Orders are so small they are barely showing on the same axis as the Sales.

Adding an Axis to a Chart in Excel

The solution is to move the Orders data series onto a second axis, which will allow you to have 2 separate number formats and 2 scales on the same chart.

To do this, select the desired data series in the chart then click the Format Selection button from the Format tab.

Navigate to the Series Options tab:

You then select the Secondary Axis checkbox:

Now the chart looks like this:

Having 2 vertical axes allows 2 different scales which helps the chart display better.

Total Sales are in thousands of dollars and No. Orders are in the 100s yet they can still be displayed correctly on the same chart. However, as both data series are the same chart type, the Orders data series is overlapping the Sales data series, making the latter harder to read.

Add the Second Chart Type to a Chart in Excel

The chart type of the Orders data series needs to be changed. To do so, select it, and then click the Change Chart Type button from the Design tab.

For this example, I am going to change the Orders series from a column chart to a line chart.

As you can see, this chart looks a lot better now and is easier to read.

Now that the secondary axis is setup, you can format the chart as usual. Most formatting options work the exact same way. The only difference is that there are more parts of the chart to format. Particularly the Layout tab changes slightly for certain dropdowns.

The Layout options which adjust the axis such as Axis Titles will have dropdowns for the primary and secondary axis like so:

Adding Axis Titles might seem more complicated here but it works the same way. Just select the option from the dropdown for each of your axis.

At this point, we have a fully functioning and nice looking chart in Excel which is composed of two separate chart types for two different sets of data but all contained within the same chart.

This is the power of charting in Excel!

Don't forget to check-out our many other tutorials on charts in Excel.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File