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