Peltier Technical Services, Inc.
 

Custom Solutions | Commercial Utilities | Training | Charts and Tutorials | PTS Blog


Peltier Tech
Chart Utilities

PTS Waterfall Chart Utility
PTS Cluster-Stack Column Chart Utility
PTS Box and Whisker Chart Utility
PTS Marimekko Chart Utility
PTS Dot Plot Utility
PTS Cascade Chart Utility

 

 

Books at Amazon.com

 

Buy me a coffee

If this topic or the whole site has been helpful, please support further development by treating me to a cup of coffee.

 

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.

 

 

Page copy protected against web site content infringement by Copyscape

 

PTS Waterfall Chart Utility PTS Cluster-Stack Column Chart Utility PTS Box and Whisker Chart Utility PTS Marimekko Chart Utility PTS Dot Plot Utility PTS Cascade Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.


Peltier Technical Services, Inc.

Custom Solutions | Commercial Utilities | Training | Charts and Tutorials | PTS Blog

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