Simple Excel Conditional Chart Example 2.

In my Simple Conditional Chart Example, I show how to make a conditional column chart in which the height of the entire column is based on the value represented by that column. This example shows how to change the color of different levels of the column. The section of each bar between 0 and 2 is blue, between 2 and 4 is green, and on through yellow, orange, and red. The trick is to use worksheet formulas to split the values into increments of 2, and to plot these increments as stacked columns.

The example uses the following data in A1:G9.

  |  A       B       C        D        E        F        G
--+-----------------------------------------------------------
1 |                  0        2        4        6        8	
2 |                0 to 2   2 to 4   4 to 6   6 to 8   8 to 10	
3 | 6.85     A       2        2        2        0.85     0
4 | 8.58     B       2        2        2        2        0.58
5 | 7.53     C       2        2        2        1.53     0
6 | 4.53     D       2        2        0.53     0        0
7 | 2.27     E       2        0.27     0        0        0
8 | 1.14     F       1.14     0        0        0        0
9 | 0.96     G       0.96     0        0        0        0

Column A has the actual values, while column B has the category (X axis) labels. Row 1 has my cutoff levels for the formulas, starting at zero, and incrementing by twos. Row 2 has the series names. A1:B2 are blank.

The hard work is being done by the formulas in C3:G9. Cell C3 contains this formula:

=IF($A3<=C$1,0,MIN(2,$A3-C$1))

If the value in Column A is less than the cutoff, the cell contains zero (the colored column will not show in the chart); if the value is greater than the next higher cutoff, the cell contains 2, so the entire segment of the colored column is visible; and if the value is in between, the cell will contain the amount over the cutoff, and part of the colored column will appear, up to the total value in column A.

The placement of the dollar signs assures the correct behavior when the formula is applied to the entire range. The formula is applied to the entire range in one of two ways:

Enter the formula in C3, then drag the small black square at the bottom right of the selected cell to fill the formula down and to the right, or
Select the entire range C3:G9 with C3 as the active cell, enter the formula, and hold Ctrl while pressing Enter to apply the formula to the entire selected range.

Select the range B2:G9, and Start the Chart Wizard. In step 1 choose the stacked column chart type, and in step 2 make sure the Series in Rows option is selected. Format the colors the way you want, and you're done.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. 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

My MVP Profile