Combining Chart Types in Excel

October 16, 2007

The key to making a number of innovative charts in Excel is to change the chart type of one or more of the chart series.

The most common use of a combination chart is to show two series that are a different order of magnitude. To show Revenue in Millions and profit percentage on the same chart, follow these steps:

• Create a chart with both revenue and profit. You won’t be able to see profit.
• Choose Series Profit dropdown
• Move to the Secondary Y-axis in the Format Series dialog. The problem is that now the columns are directly on top of each other, making it impossible to see the smaller numbers.
• Change the chart type of the profit series from column to a line.

A completely different use for combining chart types is to draw in custom gridlines.

• Create a chart showing some data
• Type a new data range with X=Custom gridline locations and Y=0. Add this series to the chart. It completely ruins the first chart. That’s OK.
• Change the series to an XY chart.
• Make the markers be transparent
• Add an Error Bar extending to the right. This becomes the Gridline!

Another interesting use of combined series is to create a chart that appears to stack four charts. For instructions on how to create this chart, visit Jon Peltier's site