Peltier Technical Services, Inc.
- Custom Excel Solutions
Jon's Excel and Charting Pages
- Jon's Charts
- Tutorials
- Excel
- Site Index and Search
- RSS
|
Peltier Technical Services, Inc. Jon's Excel and Charting Pages |
|
A Simple Tornado Chart.A tornado chart is a bar chart commonly used to compare characteristics of two populations. A common use is to show the distribution of males and females among different age groups in the general population. Males would be shown in bars stretching to the left of the central line; females reaching to the right. Let's use the simple data in this table for our example tornado chart. Notice that cell A1 is blank, to help Excel find the category labels (first column) and series names (first row). Also, the first series has negative numbers, because they will go from zero to the left.
Select the entire range (or a single cell within the range) and use the Chart Wizard to draw a stacked bar chart. It looks something like this:
A little formatting makes this a nice chart. First, double click on the vertical axis, and click on the Scale tab. Select the Categories in Reverse Order and Value (Y) Axis Crosses at Maximum Category options. Then format the bar series and the plot area to taste. I uncharacteristically played around with some funky fill formats here. I also moved the legend to the top of the chart, and set the bar series gap width to zero (double click on the series, click on Options tab), so adjacent bars were touching. To show "positive" numbers on both halves of the horizontal axis, I used a custom number format for the axis labels (see Number Formats in Excel). I used a number format of 0;0 which instructs Excel to format positives and negatives as whole numbers without a minus sign.
That's an attractive and informative chart, but I had a request for something more elaborate. I had to insert dummy series Low, Middle, and High and include some labels in columns G and H of my data range. I also changed the negative numbers to positives in the Negative column.
Select A1:F7, and create a stacked 100% bar chart.
A lot of formatting makes this a nice chart. First, double click on the vertical axis, and click on the Scale tab. Select the Categories in Reverse Order and Value (Y) Axis Crosses at Maximum Category options.
Then format the bar series and the plot area to taste. The three dummy series are all invisible (no border and no area fill). The bar series have their gap width set to zero, as described above, and I applied some fill gradient patterns to the bars and to the plot area. The axes are also invisible (no line, no major or minor tick marks, and no tick labels). If axes were desired, I would have built one or more custom ones using my Arbitrary Axis technique, elsewhere on this web site.
Well, that looks pretty good, but it isn't very descriptive; we need some labels. First I added a chart title (Chart Options from the Chart menu). We can apply data labels to the bar series, then change the text one at a time to something more descriptive than the defaults, but that is tedious. Fortunately there are a couple of handy addins that do this for us automatically, and both are free. Check out Rob Bovey's Chart Labeler, at http://appspro.com, or John Walkenbach's Chart Tools, at http://j-walk.com. I used the category labels in A2:A7 as labels for series Middle. I applied the values for series Negative in C2:C7 as labels for series Low, using the Inside End label position. Next, I applied the values for series Positive in E2:E7 as labels for series High, using the Inside Base label position. I applied the blue labels in G2:G7 to the series Negative, using the Inside End label position. Finally I applied the orange labels in H2:H7 to the series Positive, using the Inside Base label position.
That's a handsome chart. I think it can be clear and informative, without distorting the message in the data. Thanks to Richard, who asked me for this type of chart. |
|
Peltier Technical Services, Inc. Jon's Excel and Charting Pages Peltier Technical Services, Inc., Copyright © 2008. All rights reserved. |
Waterfall Chart
Box and Whisker
