jump to navigation

Excel Trick: Chart with Automatic Colour Change February 10, 2020

Posted by Admin in Tutorials, Tips & Tricks.
Tags: , , ,
add a comment

The article below provide you with trick to make an automatic colour change graphic. Let say you have certain target, and you want the graph in the certain month or certain area that the target wasn’t achieved, coloured by red automatically.

Automatic Colour Chart

“Assalmualaikum…. I wanna ask.
I made a program (file) using a graph. Let say target of the data is 5, and the value of  are a=4, b=5, c=6, how to change the color of the graph automatically, if the value is less than the target (in) “a”= red, equal the target “b”= yellow, more than the target “c”= green. If a=6, b=4, c=5 how to change the color of the graph automatically.”

Few days ago, someone posted the above question in QA page, and I’ve promised to make it as new post in this blog. So here comes the explanation with example file to make it clearer.

The Case That Needed Automatic Colour Change

This time I will discuss an excel trick to make ordinary bar chart with extraordinary color. When a certain value is less than the target, than the bar color will change into red. If it matches the target than the color turn into yellow, and when it override the target, the color become green. The picture below will show you the illustration of the above case.

Automatic Colour Graph

Automatic Colour Graph

How to Make Automatic Chart Without Macro

There are two ways to make that kind of graph, using macro or without using macro. This time I will discuss the trick without using macro. The point of the trick is preparing the dummy data, as you can see from the picture above, next to the real data. There are 3 dummy data that represent less than the target data, equal, and more than the target (you can make more/less than 3 depend on the number of categorization of the value).

Formula for dummy data is =if(value>target,value,na()) for the dummy of more than target, and so does for equal and less than target, just replace > with = and <.

After dummy data is ready, the final step is inserting the graph it self. It is just an ordinary graph, but instead of inputting the real data as data source, get the dummy data. So there will be 3 series available in the graph. Just give each series the required color for given condition.

Example of Automatic Chart

Here is the example of the above trick.

New Version


Old Version



Don’t forget, practices make perfect. Hopefully useful.