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

 

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 Chart

Fortunately, 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.

 

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Average Low

15.0

16.5

24.9

34.7

45.1

53.9

60.1

58.6

50.5

40.5

31.3

20.1

Average Range

15.7

16.5

17.5

19.3

20.8

20.6

19.2

18.7

19.2

19.0

16.1

14.6

Average High

30.7

33.0

42.4

54.0

65.9

74.5

79.3

77.3

69.7

59.5

47.4

34.7

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.

Floating Column Chart

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.

    Floating Bar Chart
Other Floating Column and Bar Charts

Knowing 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:

Floating Column Chart Tracking Blood Sugar

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.

A Gantt Chart is a Floating Bar Chart

Excel Waterfall Charts (Bridge 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.

A Waterfall Chart is a Floating Column Chart

Excel Box and Whisker Diagrams (Box Plots), commonly used in statistical analysis, are floating bar or column charts:

Vertical Excel Box Plot (finished) - - Horizontal Excel Box Plot (finished)

 

 

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