Conditional formatting on an Excel bar chart

Excel charts can’t use conditional formatting in the traditional sense, but there are various tricks you can use to imitate conditional formatting within your charts.

 

A classic example would be wanting to RAG (Red Amber Green) rate your bar chart. Perhaps you want values under 50% to be displayed with red bars, under 75% amber and above 75% green. If so, here are the steps you should follow:

 

Re-organising your data

 

 

You have some monthly assessment scores above and how a standard bar chart of these results may appear.

 

 

To prepare for the bar chart you’re about to create, you should convert the data into the format shown below (columns E to H), which includes a separate column for each coloured bar. If the result is under 50% it belongs in the Red bar’s columns, 50-75% go in the Amber column and above 75% in the Green column.

 

Formula in F4: =IF($C4<$F$1,$C4,””)

Formula in G4: =IF(AND($C4>=$F$1,$C4<$G$1),$C4,””)

Formula in H4: =IF($C4>=$G$1,$C4,””)

 

 

 

Creating and formatting the chart

 

If you select your data into a bar chart it will automatically create a separate bar for each of the Red, Amber and Green columns. Reverse the axis order so that you’re months are displayed in the correct order.

 

 

Click on the bars and change them into their relevant colours.

 

 

Data Label tricks

 

Firstly remove the Legend from the chart and then right-click on each bar and Add Data Labels on each of the three data series.

 

 

Change the format of the data labels to a custom percentage format of 0%;;; which gets rid of any of the 0% values.

 

 

Formatting the Data Series

 

Right-click on the data series and change the Gap Width to 0% (so that there is no gap between the bars) and the Series Overlap to 100% (so that each of the three data series overlap themselves).

 

 

The three bars for each month are now of equal size and overlapping each other. Two of them are a zero value and have no data label, the other has a value and the data label is visible on your chart.

 

You now have the illusion of a conditionally formatted bar chart, with values under 50% red, up to 75% amber and over 75% green.

 

 

Tip: If you want the full size of your bars to fit to 100%, you can create a 4th data series to act (for want of a better expression) as “a lengthener”. Fix it to 100% in all cases, give it no fill and no border and again with a 100% Series Overlap and 0% Gap Width and you will have an additional bar which isn’t visible but does act to keep the bar size to the full 100%.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *