Excel: Conditional Format a Chart

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

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?

  1. 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.

  2. Formulas break the data into three series.

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

  3. 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.

    If you need one color for positive and another color for negative, 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.

  4. This is new (back) in Excel 2010.