Whilst preparing for Australia's first Office DevCon, I revisited a few old scatter chart samples. After playing with these for a while, I decided that I really like scatter charts. If you remove the point markers, and join the series points with a plain line, you end up with a line chart over which you have a high degree of control. It's like having your own etch-a-sketch machine in Excel - you can draw anything with a scatter chart.
I started the DevCon presentation with this simple step-by-step example on how to build a chart with wariable width columns. You can get the whole sample here, but it may be easier to follow the explanation in this blog post. First, draw a simple scatter chart, any series will do, but I picked a straight line.
Then I clicked on a couple of points and dragged them around until I had something that started to look like a bar chart. Note that you cannot drag a point in Excel 2007. If you have that version, you will need to change the series values to achieve the same effect.
After a while, it becomes easier to just edit the point values in the range that contains the data series. The chart is starting to look like a variable width column chart - actually a step chart
Then format the chart series, remove the point markers and add vertical error bars of -100%. This results in a pile of boxes that looks very much like a column chart.
Now we just need to enter some formulas in the worksheet that translate the values we wish to chart into variables that build the scatter chart series. The sample also shows how to build dynamic ranges so that any data we add will automatically be shown on the chart.
Box Plots
Perhaps not a very useful chart. This can show several entities that have a range of values over two dimensions on the one chart. Here is how to build this using a scatter chart.
Box-Whisker Diagrams
Examples of box-whisker charts have been around for a while. Even I had a sample on my old web site a few years ago.
http://peltiertech.com/Excel/Charts/BoxWhiskerV.html
http://www.mis.coventry.ac.uk/~nhunt/boxplot.htm
http://www.duncanwil.co.uk/boxplot.html
Most of these use column charts and combinations of up-down bars and high-low lines. Another way of drawing a box-whisker diagram is with a scatter chart. The box-whisker diagram is shown together with the underlying data in this chart. It can also be shown without the data. The box-whisker diagram shows outliers as a disconnected line, the maximum and minimum extent of the data without outliers, and the first and third quartiles. It is used to get a quick indication of the spread of data.
Pyramid Charts
We have all seen food pyramids. Here is my version. This is a chart where each tier in the pyramid has an area proportional to the value it represents. It is drawn using a different scatter chart series for each tier of the pyramid. You will need to look at the formulas carefully and use a little trigonometry to work out how it is built.
Waterfall Charts
I probably had the first sample of an Excel Waterfall (or cascade) chart on the web. It was done a while ago, and only worked for positive values. Some better examples have been built since then.
http://peltiertech.com/Excel/Charts/Waterfall.html
http://www.tushar-mehta.com/excel/charts/waterfall/
http://www.sccs.swarthmore.edu/users/06/adem/engin/excel/waterfall_chart/index.php
All these samples use floating column charts to create waterfalls, as did my own early example. Here is a waterfall chart that has been constructed using a scatter chart. Like all the samples in this post, it uses no VBA.
I hope the next version of Excel will have some new chart types, meanwhile - you can draw anything with a scatter chart.