MS Excel – Conditional formatting in Graph

Conditional formatting is Data representation in an cell changes its state color, background etc as per value of data. Almost every uses it once.
But how to use conditional formatting in graph is something tricky but easy.

This guide will help you in applying conditional formatting in graph.

In our case we have used production waste from month of Jan 11 to April. Our requirement is to change colour of bars when waste is above 4.05%.

Procedure

1. We create a secondary table, which will serve as source data for our graph. This data will have two columns one when waste is below target and one when its above target.
Formula used is IF(C4<$E$2,C4,”n.a”) for below target and IF(C4>$E$2,C4,”n.a”) for above target.
2. Now click on Insert -> Chart and Select data source range.
3. Click on Series tab. There will be two series first with below target values and second with above target values.

Conditional_formatting_graph
4. Now finish and your graph is ready. Now with data values graph bars colour will change.

Download sample workbook click here