Printer Friendly Version

 Laura Nunn BellaOnline's Desktop Publishing / Ebooks Editor

Combination Charts in Excel

When you are looking at related data containing data elements are widely disbursed, you will need to define a secondary axis to properly depict the relationship between the two. For example, you may want to make a comparison between the number of races won and career earnings by top Nascar Drivers. Since the number of races is a small number (less then 100) and the career earnings is a large number (more than 1 million) ¡V a bar chart is unable to create a single axis to display both numbers in a meaningful way.

You can use a combination chart by creating a secondary axis and using a line style for the smaller number. Let's give it a try.

Input the data as shown below:

Create a Basic Chart
„Ï Select the entire data range
„Ï Press F11

Observe that the basic chart appears to have only one data series, Earnings. The reality is that the number of races won is represented on the chart but is flatlined on the bottom axis because the number of winnings is so small in relationship to the amount of career winnings.

Because the scale for these two data elements are widely disbursed, you need to define a secondary axis to properly depict the relationship between the two.

Define a secondary axis for the Number of Races Won
„Ï Click on the Chart Objects drop down
„Ï Select Series ¡§Wins¡¨
„Ï Right click on one of the selection squares representing the Series (you will see them along the category access at the bottom). Making this selection is tricky so watch the tool tip to make sure you are selecting the Series instead of the plot area or category Axis
„Ï Click on Format Data Series
„Ï Click on the Axis Tab
„Ï Select Secondary Axis
„Ï Click on OK

Observe Data Markers are combined and therefore, not meaningful

Redefine chart type for the "Wins" series
„Ï Right click on the "Wins" series
„Ï Click on Chart Type
„Ï On the Standard Type tab, select Line Chart
„Ï Select Line with data marks at each data point
„Ï Click on OK

Observe the chart results. By using a secondary axis and combination chart, the relationship between the number of points won and career winnings is displayed in a meaningful chart. The chart should now look like this.

Now you have a chart that can be interpreted and from which some observations can be made.

Desktop Publishing / Ebooks Site @ BellaOnline