Conditional Colors In Column Chart
There is no built-in way for applying conditional coloring to column charts. Yet you can do almost anything in excel with a little bit of creativity. In this post I’m going to explain how to use conditional colors in column chart.
As I stated above, we can’t make this happen by chart controls. Since we can’t set your chart to change color of columns based on values, we need to focus on what we can change. We can change values themselves. So our way of using conditional colors in column chart will be “by changing source values”.
Lets explain with an example:
We are tracking uptime of an equipment with a target of minimum 85%. So we want our chart to show a red column if uptime is below 85% for related week. Likewise a green column if the uptime is equal or above 85%.
Trick to do this is having two series, one red, one green. Then setting source data cells to fetch uptime value to red series column when it is below 85% and to green series column when it is above 85%. We need two helper columns for this 2 series. Here is the setup:
I used =IF(C2<B2;C2;””) formula for red column and =IF(C2>=B2;C2;””) formula for green column.
Now that everything is set, it is time to make our chart. Insert a column chart into the worksheet (insert->Column Chart). An empty chart will be inserted. Now select data (from ribbon or right-clik menu).
Select, target, red and green as data series (use add button) and weeks as axis label range (use edit button). It should look like this when finished:
Now select target data serie set it as secondary axis and change it’s type to line chart (How to: Combining Two Charts).
Do some formatting (don’t forget to set series overlap to 100%) and you are done. From now on, colors of weekly columns will change based on their values.
If you like this chart, you can download the example workbook and check it n detail.
For more charting tutorials make sure to visit Charts Category.