Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Chart Utility

 

Excel Dashboards

 

Books at Amazon.com

 

Combining Different Chart Types into a Single Excel Chart.


 

See also Chart Axes and Axis Tricks and Dummy Series.


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.

 


Roll Your Own Combination Chart.

Combination Chart

Excel offers a very small number of combination charts from the Custom Charts tab of the Chart Wizard: Column-Area and a few Line-Column varieties. But these are limited, with only a few series permitted, and you have to arrange the series in the right order on your worksheet.

Top of Page


Bar-Line Combination Chart.

It's easy to combine a column chart and a line chart. But how do you combine a line chart and a bar chart, all on the same chart? The trick is to combine bar chart and XY scatter chart, then clean up the axes.

Top of Page


Stock Charts with Added Series.

A common problem people have is adding an extra series to a stock chart. It would be very helpful to add a market index, for example, or another moving value to a stock chart to see how the stock price moves with respect to another factor. This can be done, but you need to follow a few extra steps.

Top of Page


Column Combination Chart for Monthly Temperatures.

I was perusing the Sunday paper recently, and came across a chart which summarized the past month's temperatures. It's an informative chart, which compares each day's minimum and maximum temperature with the normal range and the historical minimum and maximum temperatures for each date. I've seen similar charts many times, including this same design in the local paper, but this time the chart called out to me, and I had to build it myself. This page has links to three versions of this chart.

Top of Page


Area Combination Chart for Monthly Temperatures.

In Column Combination Chart for Monthly Temperatures, I showed how to create a stacked column chart which summarized the past month's temperatures. It's an informative chart, and compares each day's minimum and maximum temperature with the normal range and the historical minimum and maximum temperatures for each date. Jack Jordan sent along a similar graphic based on area chart series, and asked if I could reproduce it in Excel. This is how I made the area version of the chart.

Top of Page


Excel Box and Whisker Diagrams (Box Plots).

Box and Whisker charts (Box Plots) are commonly used in the display of statistical analyses. Unfortunately, Microsoft Excel does not have a built in Box and Whisker chart type. You can create your own custom Box and Whisker charts, using stacked bar or column charts and error bars, in combination with line or XY scatter chart series to show additional data. The procedures in these tutorials have been updated to show how to add additional series (means of other populations, perhaps, or sets of target values). This page also links to a utility which can be used to generate Box and Whisker charts directly from population data. The Box Plot utility has recently been upgraded to provide more professional output, to correct treatment of outliers in horizontally oriented charts, to run tenfold faster, and to fix a few small bugs. The utility was previously updated to provide additional chart styles, and to correct problems experienced by some non-US users.

Peltier Tech Box and Whisker Chart Utility for Excel

I have developed the Peltier Tech Box and Whisker Chart Utility for Excel that allows a user to construct box and whisker charts directly from the raw Excel data. The utility builds the intermediate summary table and then creates and formats the box plot from this table. This utility is designed to work in Excel for Windows versions 2000 through 2010. The Box and Whisker Chart dialog is shown below:

Read about the Peltier Tech Box and Whisker Chart Utility for Excel.

Top of Page


Combine Clustered Columns with Clustered Lines.

When you create a combination chart with clustered columns and lines, the lines do not cluster the way the columns do. The markers for the line series all line up over the middle of the cluster of columns they represent. But wouldn't it be a better chart if the Red markers of the line series lined up with the red columns, and the blue markers with the blue columns?

Top of Page


Clustered and Stacked Column and Bar Charts.

Excel offers clustered column charts and stacked column charts among its standard options. How do you combine a stacked column chart with a clustered column chart?

Through careful arrangement of the data in your worksheet, you can make a stacked column chart that looks like a clustered-stacked column chart. In Clustered and Stacked Column and Bar Charts. I show how it is done with illustrated step-by-step instructions.

Peltier Tech Cluster Stack Chart Utility

In this page I have described a technique for constructing clustered and stacked column charts in Excel. The protocol required to create clustered-stacked charts is rather tedious, and the data layout is complicated, so I have developed the Peltier Tech Cluster Stack Chart Utility that allows the user to construct clustered-stacked column charts directly from the data. The utility inserts a new worksheet, adds a table with the appropriate data arrangement, and then creates and formats a clustered-stacked column chart from this table.

The utility allows the user to set up a simpler data range, and through a dialog select the clustering and stacking configuration.

The utility is designed to work in Excel for Windows versions 2000 through 2010.

Read about the Peltier Tech Cluster Stack Chart Utility.

Top of Page


Advanced Gantt Charts in Microsoft Excel.

Gantt charts are useful tools in program management, which help to show graphically when tasks must start and finish, and which tasks are underway at any given time. Gantt charts help in scheduling of the many tasks in a program, and in identifying potential resource issues in the schedule. A simple Gantt chart is merely a floating bar chart, that is, a stacked bar chart in which the first series is formatted to be invisible. The second series of bars are stacked on the first, but these bars appear to float in the middle of the chart, because the first series is formatted to be invisible.

This example shows horizontal task bars that are split to show the percent completed, milestone markers at the end of each task bar indicating whether the task has been finished, and one or more vertical lines indicating particular dates along the axis.

Top of Page


Add a Horizontal or Vertical Line to a Column or Line Chart.

How do you add a horizontal or vertical line to a column or line chart, to show a target value, or the series average? The method involves adding a new series, applying it to the secondary axes, and making the secondary axes disappear. A variation uses an error bar for the straight line, rather than the connecting line between two points.

Add a Horizontal Line to a Column or Line Chart: Series Method.

Add a Vertical Line to a Column or Line Chart: Series Method.

Add a Horizontal Line to a Column or Line Chart: Error Bar Method.

Add a Vertical Line to a Column or Line Chart: Error Bar Method.

Top of Page


Chart with Colored Quadrant Background.

I was recently asked, "I have an XY scatter chart that gets divided into 4 quadrants and each quadrant needs a different color. Any ideas?" I love a good challenge, so I came up with the procedure in this page.

Top of Page


Colored Vertical Band.

This page shows how to put a vertical band on a chart, highlighting a certain region. The technique can be used, for example, to highlight a certain time period in the background of a gantt chart.

Top of Page


XY Area Chart - Fill Under or Between XY Series.

Did you ever want to fill the area under an XY series? You tried an Area chart, but that didn't work; the X axis didn't scale properly, and you couldn't get the lines and markers you wanted. You can fill the area under an XY series by using a combination XY - Area chart, plus a little creative treatment of the X values of the Area series and of the scaling of the secondary X axis.

Top of Page


Arbitrary Axis Scale.

Don't you wish you had more control over Excel's chart axis ticks and labels? There isn't a lot of built in support for this, but over the years people have learned a few tricks to generate charts like this.

Top of Page


Arbitrary Gridlines.

Don't you wish you had more control over Excel's chart gridlines? Wouldn't you like Excel to draw gridlines where you want them? There isn't a lot of built in support for this, but over the years people have learned a few tricks to generate charts like this.

Top of Page


Vertical Category Axis.

How do you arrange your chart so the categories are displayed along the Y axis? The method involves adding a dummy series along the Y axis, applying data labels to its points for category labels, and making the original Y axis disappear.

You may also want to check out the page on Dot Plots.

Top of Page


Dot Plots.

Often when a series of values and corresponding labels is to be plotted, the chart of choice is a bar chart. This is a reasonably straightforward chart, easy to make and fairly clear in its presentation. A "Dot Plot" is a way to chart the same information that is cleaner and according to research more easily understood. The chart still displays labels along a vertical axis, while the data is charted as dots, spaced horizontally according to its value. This page also links to a utility which can be used to generate Dot Plots directly from worksheet data.

Top of Page


Simulated Probability Chart.

Microsoft Excel does not offer a built in capability to chart probability data, but the technique described here allows you to simulate a probability scale along a chart axis.

Top of Page


Reciprocal Axis Chart.

An Arrhenius equation gives the following relationship between some measure of reaction rate or chemical solubility and temperature:

K = A exp (-Q/RT)

where K is the rate or solubility, A is a constant, Q is an activation energy, R is the gas constant, and T is the absolute temperature. The equation above can be restated to:

log (K) = A' - Q/RT

A chart can be constructed with log(K) on the Y axis and reciprocal temperature (1/T) on the X axis; the slope of this line indicates the activation energy (actually -Q/R) and the intercept is the new constant A'. This page describes the construction of one variation of this type of chart.

Top of Page


Tertiary Y Axis.

I once had a page on this site that showed how to generate additional axes in a chart. With the technique you could go beyond primary and secondary axes, to tertiary and quaternary axes, and even more.

Given that charts with just primary and secondary axes can be confusing, a tertiary axis is just overkill. Multi-axis charts can be cluttered and confusing, even when using a custom color scheme to help identify each series with its corresponding axis.

I propose using Panel Charts as a substitute for charts with confusing multiple axes. Here are some tutorials and examples of panel charts:

Top of Page


Creating Exponential Notation Axis Labels.

The way most computer programs render numbers in scientific notation is not particularly attractive, for example 1.23E-04. Most of us learned true exponential notation in high school, and many publications require the use of this notation, rendered with a true superscripted exponent, for example 1.23x10-4. Here is a technique to add exponential notation axis labels to your chart.

Top of Page

 

 

Page copy protected against web site content infringement by Copyscape

 

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2014. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile