|
|
Peltier Tech
Excel Dashboards
Books at Amazon.com
|
Floating Column Charts.
Some kinds of data look very nice and are easily understood in the form of a "floating" column or bar, in which the column floats in the chart, spanning a region from a minimum value to a maximum value. The secret of a floating column chart is to make a stacked column chart with two series, the first representing the lower extent of the floating column, the second representing the difference between the upper and lower ends of the column. By formatting the lower series to be invisible, the upper series floats in your chart. Excel comes with a built-in custom chart type called "Floating Bars," shown below. It produces a horizontally-aligned bar chart with two series, minimum and range, the way we will do. But numerical data often is best displayed vertically, and there is no "Floating Columns" corollary. Further, the Floating Bars chart is a chart with features only a salesman could love, like 3-D effects and a disturbing background gradient fill.
Off Topic: "built-in custom"!? Is the Excel chart stylist moonlighting as the Microsoft Oxymoron Editor? Building a Floating Column ChartFortunately, in Excel you can make just about any kind of chart that you want, if you are willing to do a little work. In my example, I am going to plot average monthly temperatures for Worcester, MA. The following data came from the National Climatic Data Center, by way of the Washington Post. The average monthly high and low temperatures came from the internet, and I used simple worksheet formulas to compute the average monthly ranges. Our two series are the average minimum temperatures and the average ranges.
National Climatic Data Center report NCDC TD 9641 Clim 81, 1961-1990 The first step in creating a floating column chart is to create a stacked column chart. Select the first three rows of data in the table, run the Chart Wizard, and make a stacked column chart.
Now double click on the "Average Low" series (shown here in red), to open the Format Series dialog. Click on the Patterns tab, and select None for Border and Area. This series becomes invisible, but still supports the columns that denote the temperature range.
The process is the same for a floating bar chart. First create a stacked bar chart, then make the first series invisible so the second series floats in place.
Other Floating Column and Bar ChartsKnowing the basic mechanics of floating column and bar charts allows you to create a wide range of charts. One correspondent used a floating column chart to track his blood sugar levels:
Gantt charts, widely used in program management, are basically nothing more than floating bar charts, although you can combine them with scatter chart series to provide milestone markers. See Gantt Charts in Microsoft Excel, Advanced Gantt Chart Techniques, and Links to Gantt Chart resources.
Waterfall charts are floating column charts that trace how an initial value is increased and decreased by a series of intermediate values, leading to a final value.
Box and Whisker Charts, commonly used in statistical analysis, are floating bar or column charts: |
Peltier Technical Services, Inc.Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2010. All rights reserved. |
