MrExcel.com - Your One Stop for Excel Tips & Solutions

Conditional Format a Chart

July 27, 2023 - by

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?

The chart has 7 columns. If the column is above 90, it is green. If it is between 70 and 90, it is yellow. If it is below 70, it is red. Two "constant lines" are drawn in at 70 and 90.
Figure 1220. 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.

8 columns are used to make the chart. Column A is Line. Column B is rating. The chart is made from columns C:H. Column C repeats the Line from A. Column G is called Dash 1 and is 70 all the way down. Column H is called Dash 2 and is 90 all the way down. Column D has a heading of Red. The formula is =IF(B2<G2,B2,NA()). This causes any values less than 70 to appear in the column, but #N/A errors elsewhere. Similar formulas show the numbers for Yellow in E and Green in F.
Figure 1221. Formulas break the data into three series.

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

The previous figure with all formulas showing. The formula for Yellow is =IF(AND(B2>=G2,B2<H2),B2,NA()). The formula for Green is =IF(B2>=H2,B2,NA()). The Dash 1 column in G has a 70 in G2 and then =G2 copied down.
Figure 1222. 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.

The Fill panel has a solid fill. Choose Invert if Negative. Show positive in Green, negative in Red.
Figure 1223. This is new (back) in Excel 2010.

This article is an excerpt from Power Excel With MrExcel

Title photo by David Pisnoy on Unsplash