Microsoft Office Tutorials and References

In Depth Information

This section describes how to create a column chart in which the color of each column depends on the value

that it's displaying. Figure 17-7 shows such a chart. (It's more impressive when you see it in color.) The data

used to create the chart is in range A2:F14.

Figure 17-7:
The color of the column varies with the value.

A workbook with this example is available at this book's website. The filename is condi-

tional colors.xlsx.

This chart actually displays four data series, but some data is missing for each series. The data for the chart is

entered in column B. Formulas in columns C:F determine which series the number belongs to by referencing

the cut-off values in row 1. For example, the formula in cell C3 is

=IF(B3<=$C$1,B3,””)

If the value in column B is less than the value in cell C1, the value goes in this column. The formulas are set up

such that a value in column B goes into only one column in the row.

The formula in cell D3 is a bit more complex because it must determine whether cell C3 is greater than the

value in cell C1 and less than or equal to the value in cell D1:

=IF(AND($B3>C$1,$B3<=D$1),$B3,””)