cellMatrix.net

Floating Bars with Min, Max, and Averages

A recent question to the Microsoft Excel Charting Newsgroup asks "How can I create a floating column chart that shows a minimum, maximum, and average value"? Although Excel 2003 does offer a floating column chart as a custom-type option, it is not as "clean" as desired because the bars are automatically set to a 3D option. The best alternative is to use a combination Stacked Bar / XY Scatter chart. The bars will show the minimum to maximum range and the XY series will show minimum, maximum, and average values for each item. An example of the finished product is below:

To build the chart, assume the following set of data:

Some additional data and calculations need to be added to prepare the chart source. In the screenshot below, the range A9:C13 will serve as the source of the bar chart. The original range A2:D6 will serve as the X axis points of the XY chart and the Y points are shown in the range F2:F6.

Start by creating the floating bar chart. To do so, refer to a process posted at the Peltier Technical Services site that illustrates how to make a series invisible so that a second series floats in place (titled: Floating Column Charts) as a guide.

In the example, note that the "Dummy1" series is exactly the same as the "Min" values. The "Dummy2" series is calculated by subtracting the "Min" value from the "Max"value. Select the range A9:C13 and create a stacked bar chart. The chart should look like the one below:

Double-click the "Dummy1" series. In the Format Data Series dialog box - Patterns tab, set the Border to None and the Area to None. The chart should now look like this:

Now that the floating bar chart is created, it's time to incorporate an XY Scatter chart which will show the minimum, maximum, and average values. Select the chart and in the Source Data - Series dialog box, add a third series called "Minimum" based on the range F3:F6. After doing so, the Source Data - Series dialog box should look like this:

Note the new third series has appeared as a set of yellow bars. These bars need to be converted to XY Scatter points. Click on the new third series (the yellow bars) and, in the Standard toolbar, select Chart -> Chart Type -> XY (Scatter) -> type = Scatter. After doing so, the chart looks like this:

In the Standard Toolbar, select Chart -> Source Data -> "Minimum" Series. Currently there are no X values selected for the XY points (see the screenshot below):

Select the Minimum values as the X Value range (see the screenshot below):

And the chart should look like this:

With the incorporation of the XY Scatter points, two new secondary axes have been added to the top and right sides of the chart. To properly align the new XY points with the bars, the maximum values for these new axes need to reflect the same maximum values of the primary category and value axes. Double-click on the Secondary Value (X) Axis (the axis values at the top of the chart) and in the Format Axis dialog box -> Scale tab - set the maximum to the same value as the maximum of the Value axis (in this example the maximum is 60).

At this point the minimum values are now aligned with the minimum values of the bars. The same process of adding XY scatter points has to be completed to set up the average and maximum values. After adding the average and maximum values as XY points, the chart looks like this:

All that's left is to do now is some clean-up. Double-click on the Secondary Value (X) Axis (the axis values at the top of the chart) and in the Format Axis dialog box -> Patterns tab set the Tick Mark Labels and Major Tick Mark Type options to None. Likewise, double-click on the Secondary Value (Y) Axis (the axis values on the right side of the chart) and in the Format Axis dialog box -> Patterns tab set the Tick Mark Labels and Major Tick Mark Type options to None. The chart now looks like this:

To eliminate the legend references to the "Dummy1" and "Dummy2" series, click first on the legend and second on the "Dummy1" legend entry. Handles should appear around the "Dummy1" legend entry if it has been selected correctly. An example of how the chart looks with the "Dummy1" legend entry selected is below:

To get rid of the "Dummy1" legend entry, hit the delete key. Follow the same process for the "Dummy2" reference.

Select the plot area. In the Standard Toolbar select Chart -> Chart Options -> Gridlines and deselect all of the gridline options. Select the Minimum, Average, and Maximum series and in the Format Data Series dialog box -> Data Labels tab select the X value.

Continue to format until finished.