Often it is useful to create charts that compare different types of data. For example, you might want to compare a stock’s share price with its trading volume, or overall corporate revenue with the number of units sold.
To quickly and clearly display data of different types, it can be helpful to plot varying data sets either with different chart types or on different axes.
Turn data into insights.
Suppose I work at a manufacturing company, and I’d like to analyze the number of units we’ve sold over the last few months and the total revenue generated. I’m hoping to identify trouble spots, such as high unit sales that result in low revenue, which may in turn indicate that the units are being sold too cheaply.
I could make two different charts—one plotting units sold per month, and another plotting total transaction revenue by month. However, it will be challenging to analyze and compare the two charts separately. I’d much rather create a single chart that incorporates both data sets such as the one shown below.
Daunting though it may seem, it turns out that making this kind of chart isn’t very difficult at all. We’ll walk you through the two major steps—combining different chart types and adding a secondary axis. Then we’ll show you how to add some finishing touches to make your chart look polished and professional.
Combining different chart types
Create a data set
To ensure you can follow along, we’ve created a simple data set below:
Sample data showing units sold and total transactions.
Select your data set
The first step in building a chart with more than one chart type is to actually set up a chart with just a single chart type. For this scenario, I want a chart with columns and lines, but I’ll start with a regular column chart. (Note: It actually doesn’t matter which chart type you start with, but if you’re working with a multiple data sets, I’d pick the chart type which applies to the majority of your data – it’ll mean less work for you later).
- Select the data that you would like to use for your chart.
- Go to the Insert tab, and then choose a type of column to insert (as shown below). For the sake of this scenario, select one of the 2-D column types.
Selecting a chart type.
Now we have a column chart with two data sets (Units Sold and Total Transactions), both charted using the same chart type.
Changing one of the data sets to a line chart
The next big step is to change the chart type for the Total Transactions data set into a line chart.
1. Click the Total Transactions data column in the chart. Don’t click the legend text Total Transactions. Rather, click one of the orange bars in the chart. You should now see the data set highlighted:
2. Once you have selected the Total Transactions column in the chart, click Chart Design, and then click the Change Chart button.
3. In the Change Chart Type dialog box, select the Combo, change Total Transactions to Line and click OK.
Voila, you’ve created a chart with two chart types (column and line)!
Chart plotting two data sets with bar and line chart.
In fact, you can combine far more than two chart types by repeating the above process with additional data sets, and selecting a different type from the Change Chart Type dialog box.
Add a secondary axis
While quite cool, our chart is still difficult to analyze because the scale of the Total Transactions is much larger than the scale of the Units Sold, and so we can’t gain much insight from the virtually indistinguishable columns. To make the chart easier to read, the Total Transactions should be converted to a secondary axis, thus allowing for the data sets to be scaled differently. Here’s how you convert a data set to a secondary axis.
1. Click the Total Transactions line chart again and this time right-click and select Format Data Series. In the Format Data Series pane, click the Bars icon and then select Secondary axis.
Formatting options in the Format Data Series pane.
You’ve now successfully added a secondary axis to your chart! Your chart should look like this:
You have now created a chart that displays your data in way that allows for easy analysis. Now you can do fit-and-finish work to make your combined chart look more professional.
Change the secondary axis label
We want the Total Transactions axis to display dollar signs since this is the value we are tracking.
- Right-click the Total Transactions axis label and then select Format Axis.
- In the Format Axis pane, click the the Number arrow to display the options.
- From the Category list, select Currency. If you don’t like a decimal place, in the Decimal places box, change from 2 to 0.
Format axis menu.
Add axis titles
To know precisely what you’re tracking, you can add axis titles (labels) to the chart.
- Select the chart and then under Chart Design, click the Add Chart Element button and then click Axis Titles.
- On the list that appears, choose Primary Vertical.
- In the chart, type in the title you’d like. For this scenario, use the title Units Sold.
- Do the same for the Secondary Vertical.
Adding an axis title.
Add chart title
Finally, we want to add a descriptive title so it’s clear to everyone what the chart is depicting.
- Select the chart and then under Chart Design, click the Add Chart Element button and then click Chart Title.
- Select a location for the chart title and then in the chart, type in the chart title you’d like.
And that’s all there is to it! Now you have professional looking chart that clearly displays data for both chart types
By repeating the steps outlined in this post with additional data sets, you can create even more complex (yet readable) charts.
Here are some additional resources for Excel charts:
Start your free trial of Excel for Office 365.