Combining Different Chart Types into a Single Excel Chart.

Introduction to Combination Charts

Excel offers a wide range of chart types: Line Charts, Column Charts, Area Charts, Bar Charts, Scatter Charts, and Pie Charts, to name but a few. You can even mix different types on a single chart by assigning different chart types to different series on the chart. These mixtures are called Combination Charts, and Excel provides a small number of these on the Custom Types tab of the Chart Type dialog box.

You can create your own combination charts with a wider variety of combinations, by applying the Chart Type menu command to selected series in your chart, not on the chart as a whole. This gives you much more flexibility over the types and formats you can use in your charts. Roll Your Own Combination Chart, shown below, describes this is in detail. The other links below show imaginative charts and effects you can obtain using combination charts.

A Real-Life Combination Chart

The following is an example of a combo chart I made recently at work. I was trying to show some things to my boss and our customers. The chart compares the performance of two models of our product, with two serial numbers of each model. Clearly the light and dark blue parts of Model 1 don't match up to the light and dark orange parts of Model 2. Model 2 pretty much exceeds the target of 1.0 (all of this data is heavily normalized to prevent anyone learning any trade secrets from the example), while Model 1 falls short. The two samples from the Model 1 parts, which received "Special" treatment did not display a noticeable difference compared to the standard samples.

This combo chart consists of five stacked bar series showing the actual performance data (Special, AB1234, etc.). The serial numbers along the vertical axis are linked to a dummy series along the axis, shown in my Vertical Category Axis example. The labels can be independently formatted, unlike standard axis labels; coloring the labels to match the series (and the legend entries) speeds comprehension by those busy executive types. Fortunately for us lazy types, Rob Bovey's XY Chart Labeler, a free add-in available for download at http://www.appspro.com, not only puts text labels from worksheet cells onto plotted points, it also applies the cells' formatting to the chart labels.

The vertical red target line and the horizontal black line separating Model 1 from Model 2 are real-life examples of Adding a Horizontal or Vertical Line to a Column or Line Chart. The Model 1 and 2 labels are data labels on two markerless points at X=1.5, above and below the points they describe. The vertical line was done with an error bar, the horizontal line by connecting two hidden points. Excel's stacked bar charts don't allow data labels beyond the bars like this; you could move them manually, but I find it easier to add an XY series (with no markers and no lines) along the ends of the bars, to position the labels where I want them.

Not bad, five series to chart the actual data, and four more to help display it in better context. Also note the vertical gridlines: they are plainly visible, but being light in color and dashed instead of solid, they do not distract you from the data itself. I've borrowed this format from John Walkenbach. A nice white background keeps it crisp. Where are those fancy fill patterns and gradients? Haven't I demonstrated the K.I.S.S. principal yet?? Keep It Simple Stupid (or Keep It Simple for the boSs). Do the extra work to make the chart simple to understand, and leave the fancy colors to the marketing folks.