You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
(This post was originally written and published by Katherine Fifer, an intern on the Excel 2007 team. We've updated it for Excel 2010 and added a sample worksheet to help you learn.)
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.
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.
Chart Showing Both Units Sold and Total Transactions
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.
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 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).
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.
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 on the Total Transactions data column in the chart. Don't click on the legend text "Total Transactions." Rather, click on 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, you will see the following button in the Type Group section: Change Chart Type
3. Click on the Change Chart Type button
4. This will bring up the Change Chart Type dialog box. Select the new chart type you'd like to use. In this case, select the Line chart and hit OK.
Change Chart Type Dialog
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 type from the Change Chart Type dialog box.
Chart Plotting Two Data Sets with Bar and Line Chart
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 (do this exactly as described in the "Changing One of the Data Sets to a Line Chart" step above).
2. Navigate to the Layout tab on the main menu.
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 Layout tab
5. Click the Secondary Axis button in the Series Options section, and then click Close.
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 Show 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. Right-click the Total Transactions axis label and select Format Axis.
2. Click the Number tab at the left-hand side of the of the Format Axis dialog box.
3. Click Currency from the Category list. If you don't like a decimal place and subsequent zeros, remove the two right-most zeros and the decimal place from the Format Code box.
4. Click Add, and then click Close.
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 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 "Units Sold."
5. Do the same for the Secondary Vertical Axis Title
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 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 Consisting of Two Chart Types
By repeating the steps outlined in this post with additional data sets, you can create even more complex (yet readable) charts.
More info on Excel charts:
Our eight best tutorials on Excel chartsCreate a chart from start to finishFree training: Take the next steps in growing your Excel skills (Lesson 2) Go beyond the basic chart type Can't find the Chart Wizard? No worriesUse sparklines to show data trendsLine or scatter chart?Format column sparkline charts using the date axis and cell merging
There are very few situations where this is a good idea (unless you are deliberately trying to mislead the users of your graphic). Have a look at junkcharts.typepad.com/.../illusion_of_suc.html and junkcharts.typepad.com/.../the_crossover_l.html for some examples and comments.
Hadley, you missed the point of using different graph types to disambiguate data belonging to different axis. Financial charts oftentimes use two axis, e.g., one for stock price and another for trade volume. And even using the same measure on two axis can be useful when comparing trends. Your logic appears to be flawed.
thanks for this one --- this is exactly what I did recently and whilst doing so I was thinking I must be wrong, surely there is a simpler way --- would it be possible to make this type of graph an option among the many excellent ones already there? eg second axis graph
Regarding the question about providing some predefined combo charts among the existing chart types, this is something we are considering for a future release. However, could you elaborate on the types of combo charts that you most commonly use?
I believe Excel 2007 will not allow you to do this with certain types of charts, (eg. bubble chart and line ). Am I mistaken?
That is correct, you cannot combine certain chart types such as bubble and line or 2D and 3D charts. This is a limitation that also existed in Excel 2003.
When building combination charts as Katherine has described, my work often requires that I move away from a zero X-axis to two proportional Y-axes. To do so, I currently use VBA to calculate and set the values. The ability to tie worksheet cells directly to the primary and secondary maximum, minimum, and major chart axes values would eliminate the need for coding. For future releases, would the Excel team consider adding the ability to tie worksheet cells to chart axes values?
Being able to tie worksheet cells to chart axis values is something we are definitely considering for a future release of Excel.
As a follow-up to Colin's reply, I appreciate the candid feedback. Unfortunately, members from the Excel product team need to stay neutral to avoid setting any kind of expectations. We do our best to gather constant feedback from our customers and assess the relative importance of the requests. This helps us prioritize the work. However, until those decisions are made and we get closer to release, we cannot disclose what is in or out of the release. In the past, we have had angry customers who built solutions which relied on features that got cut during the product development cycle.
Hadley may have missed the point that it can be useful as a tool for some, though he makes a very good point about how these charts can be misleading. You must be very careful and understand what you're seeing. For example, take the same data as in the example shown above and change the right hand side y-axis so that the 1,000 is where the 8,000 is. What you would have is a red line way above the blue bars. Alternatively, you could change the scale so that the 8,000 is where the 1,000 is. Then you'd have a flat line running through all the bars. The conclusion: Nothing. Very easy to be mislead.
As a follow up to my previous comment, you can do the same thing with stock prices and trade volumes. So I say good point Hadley and others beware of these types of charts.
As a follow up to my previous comment, you can do the same thing with stock prices and trade volumes. So I say good point Hadley, and others should beware of these types of charts.
great, it is very useful in my presentation, weekly