Microsoft Office Tutorials and References
In Depth Information
You’re probably familiar with Excel’s Conditional Formatting feature, which
enables you to modify cell formatting based on the value contained in the cell.
Unfortunately, Excel does not provide an analogous feature for charts. If you would
like to display different colors in a chart based on values, you can use the technique
Figure 8-25 shows a column chart that appears to display the data in column B, and
the columns are colored based on the values. For example, data that is less than or
equal to 0 displays in yellow. Data that is between 0–33 displays in purple, and so on.
Figure 8-25: The color of the bars depends on the value.
Actually, this chart consists of four series (each using a different fill color), and
uses the data in columns C:F. The cells in these columns contain formulas that
reference the data in column B, and use the values in row 1 to determine whether
the cell should contain the data value or display an empty string.
The formula in cell C2, for example, which follows this paragraph, examines the
value in cell B2. If it’s less than the value in C1 (0), the value from column B is
displayed. Otherwise, the formula returns an empty string.
The formulas in column D:F are a bit more complex because they need to
determine whether the value in column B falls between two values. The formula in D3,
for example is