(This post was originally written and published by Katherine Fifer, an intern on the Excel 2007 team. We’ve updated it for Excel for Mac 2011.)
Many of us need to create charts that compare different types of data. To quickly and clearly display data of different types, it’s helpful to plot varying data sets with either different chart types or on different axes.
Suppose I own a sign company, and I’d like to analyze the number of signs I’ve sold over the last few months and the total revenue generated. I’m hoping to identify trouble spots, such as high sign sales that result in low revenue, which may in turn indicate that the signs are being sold too cheaply.
I could make two different charts – one plotting signs sold per month, and another plotting total transaction revenue by month. However, comparing two charts won’t help me analyze very quickly. I’d rather create a single chart that incorporates both data sets such as the one shown below.
Chart Showing Both Signs Sold and Total Transactions
And the best news is, you’ll be able to create a chart like this by the end of this blog! I’ll walk you through the two major steps — combining different chart types and adding a secondary axis. Then I’ll show you how to add 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, as well as a sample workbook that you can use to walk through the steps yourself:
Sample Data Showing Signs Sold and Total Transactions
Select Your Data Set
Now I’ll create just one chart. 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).
1. Select the data that you would like to use for your chart.
2. Click the Charts tab, then click the Column button. For this scenario, select one of the 2-D column types.
Now we have a column chart with two data sets (Signs Sold and Total Transactions), both charted using the same chart type.
Chart Tracking Two Different Data Sets
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 on the legend text “Total Transactions.” Rather, single-click one of the red bars in the chart. You should now see the data set highlighted as follows:
Selecting a Data Set on a Chart
2. Once you have selected the Total Transactions column in the chart, return to the Chart tab, and click the Line chart button. For this scenario, click the 2-D Line chart.
Voila, you’ve created a chart with two chart types (column and line)!
In fact, you can combine far more than two chart types by repeating the above process with additional data sets, and selecting a different chart for each data series.
Chart Plotting Two Data Sets with Bar and Line Chart
Add a Secondary Axis to Make Charts Easier to Read
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 Signs 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 (do this exactly as described in the “Changing One of the Data Sets to a Line Chart” step above).
2. Navigate to the Chart Layout tab on the ribbon.
3. In the Current Selection section on the far left, make sure the dropdown selection reads “Series Total Transactions.”
4. Click Format Selection (see the image below) to display the Format Data Series dialog box.
Formatting options on the Chart Layout tab
5. Select Secondary Axis in the Axis section, and then click OK.
Format Data Series Dialog
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.
Make Your Chart Legend More Visible
If you move the chart legend to the bottom of your combined chart, it will separate it from the Total Transactions axis and so better highlight it.
1. Click the Chart Layout tab, and then click Legend.
2. Choose Legend at Bottom.
Formatting Options for Chart Legend
Legend Appearing at the Bottom of the Chart
Change the Secondary Axis Label
We want the Total Transactions axis to display dollar signs since this is the value we are tracking.
1. Ctrl+click the Total Transactions axis label (on the right) and select Format Axis.
2. Click the Number tab at the left-hand side of the of the Format Axis dialog box.
3. Uncheck Linked to Source, and then click Currency from the Category list.
4. If you don’t like a decimal place and subsequent zeros, remove the two right-most zeros and the decimal place using the currency format options.
4. Click OK.
Format Axis Menu
Add Axes Titles
To know precisely what you’re tracking, you can add axes titles (labels) to the chart.
1. Go to the Chart Layout tab, and choose Axis Titles.
2. On the list that appears, choose Primary Vertical Axis Title.
3. Then choose whichever option you’d like- in this case, I’ll choose Rotated Title
4. Type in the title you’d like. For this scenario, use the title “Signs Sold.”
5. Do the same for the Secondary Vertical Axis Title (“Total Transactions”).
Adding an Axis Title
Add a Chart Title
Finally, we want to add a descriptive title so it’s clear to everyone what the chart is depicting.
1. Go to the Chart Layout tab, and click the Chart Title button.
2. Select a location for the chart title.
Chart Title Creation
And that’s all there is to it! Now you have professional looking chart that clearly displays data for both chart types
A Completed Chart with Two Chart Types
By repeating the steps outlined in this post with additional data sets, you can create even more complex (yet readable) charts.