Hi, I'm Matthew MacDonald the author of Excel 2007: The Missing Manual and I'm going to explain how to create a combination chart.
If you spend some time with Excel you've undoubtedly discovered its nifty charting features. Excel makes it easy enough to build basic charts but all Excel experts have a few charting tricks up their sleeves.
One of these tricks is a combination chart. Essentially a combination chart is a type of chart that fuses together two other different charts. A combination chart gives you the ability to compare different but related sets of data.
Here's a good example, imagine you have an Excel spreadsheet that tells you how many hours you've spent surfing the web in the last few months. In the same spreadsheet you also have a list of how much money you've spent on eBay over the same time period.
After you add these two sets of data you start to wonder if there's a link. In other words, do you spend more money when you spend more time online? This is exactly the sort of question you can answer with a combination chart.
To create a combination chart you start off just like you would with any other chart. You select the information you want to include in your chart, you head to the Insert tab and then you pick the chart type you want to use. In this example I'm fusing a standard line chart with markers so I can see each point of data.
When Excel creates a chart you'll notice a problem. This chart uses two sets of data, one that shows a number of hours and one that lists an amount of money. When Excel creates a chart it uses the same scale for both sets of data. This isn't good. The much larger money values take over and the comparatively small hour figures are scrunched up at the bottom of the chart. It's impossible to tell if there's a relationship between the two sets of data.
Here's a combination chart trick. You need to give each line its own scale. To do this first click to select one of the lines, it doesn't matter which one. And then right click it and choose "format data series." The option you want is right at the top in the series option section.
Right now both lines use the primary axis. That's a fancy way of saying that they share the same scale. However, every chart can have two axes, a primary and a secondary. By choosing secondary axis here I tell Excel to create a new scale and use that for the line I selected.
Once I click close I can see the result. The scale for the primary axis shows up on the left, in this example that's the scaled used for the number of surfing hours. The scale for the secondary axis shows up on the right. In this example that's the money spent. Each line uses the appropriate scale and the result is that I can clearly see both of them at once.
The effect is more or less the same as if I superimposed one chart with the hours information over top of a separate chart with the money information. Most importantly I can now see that there's a relationship, more hours on the web means more money spent.
There's one more trick I can use here. I can tell Excel to use a completely different chart type for one of these lines. To do that I right click it and choose "change series chart type." Obviously some chart types won't work as well as others for this trick. What I want is a chart that lines up from right to left with a line chart I'm already using. One good choice is a column chart.
Now all I need to complete my combination chart are two snazzy formats. Here's the final result, an attractive combination chart that clearly illustrates the danger of too much time online.