• Excel Book Excerpt

Excel Conditional Format a Chart

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Conditional Format a Chart

Problem: I want the chart column to be green for ratings of 90 or above, yellow for 70 to 90 and red for less than 70. Can I do conditional formatting in a chart?

LE10001100.jpg

Figure 1065 Color the columns based on their value.

Strategy: Charts don’t support conditional formatting (yet). However, you can use formulas to separate your data into three series, one series for red, one series for yellow, and one series for green. Only one series will be filled for each category. The other series will be #N/A.

The formulas below break the value in column B into one of three series in D, E, or F. Each value in B goes to exactly one cell in D:F.

LE10001101.jpg 

Figure 1066 Formulas break the data into three series.

The formulas used to create the table above are shown below.

LE10001102.jpg

Figure 1067 IF statements decide which color to use.

When you create the chart, create a stacked column chart. You will have to select each series and use Format, Shape Fill to choose the correct color.

Additional Details: An oversight in Excel 2007 made it impossible to control the color of the negative columns when you chose Invert If Negative. You could use a similar method to format negative columns.

LE10001103.jpg

Figure 1068 In Excel 2007, you had to use this method to show negative values in red.

In Excel 2010, you can use a regular column chart. Format the series. On the Fill category, choose Invert if Negative. you can choose Green for the first color and red for the second color.

LE10001104.jpg

Figure 1069 This is new (back) in Excel 2010.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy