The conditional color chart is an interesting experiment for extending Excel toolbox. Today’s article will help us in the solution of a very rare but important issue. So far we could mostly have the opportunity to use Excel conditional formatting function with cells, and with a little thinking we can easily make a spectacular Excel chart template also.
What is this all about? When we color or assign distinctive signs the cells than we talking about conditional formatting. The aim of today’s presentation is to display values by assigning color codes in connection of if they are larger or smaller than a given parameter (average).
Unfortunately Excel doesn’t support the use of this method but there is a solution for displaying values different from the average (in positive or negative direction). On the animation below can be seen the essence of this data visualization. You will use it more in the future because it is a lot better to see things in dynamics than in static. We have represented average by a blue broken line we will compare to this the points of daily values and differences. Below average is represented by red and above average is green.
Animation shows how will display change if we change the value of the ‘Target’ (average) field. At first glance it seems that everything is unchanged and it is true to some degree because values haven’t been altered. However average has changed and data points have been colored according to this fact. Notice that the sixth data point from the left has changed! (It’s value stayed: 39).
Conditional Color Chart – Calculated Fields
We will create a four part conditional color chart based on the data can be seen on the above picture. No, it is not a mistake, it is true that here only two can be seen but we indeed need four of them.
In column ‘Period’ we see those days results will be analyzed. The ‘Result’ column can mean income, expenditure or anything else we would like, the bottom line is that these are the daily values get compared to the average can be found in the ‘Target’ column.
The most interesting for us are the columns ‘Below’ and ‘Above’. It easily can be decided by using Excel IF formula if the ‘Result’ column’s values are larger or smaller than the average, namely the value of ‘Target’ column.
There are two values can be placed in the ‘Below’ column; if Result < Target than the formula gives a #N/A value, and if Result > Target, than the column Result value will be displayed.
Also two values can be placed in the ‘Above’ column based on the previous method the difference is only that the direction of the relation is opposite.
Conditional Color Chart – Multiple lines
Finally the 4 columns are formed from which we will make the 1-1 line chart. You best imagine that at the columns containing the values #N/A the chart will be empty so we wouldn’t draw anything. And this is what we need right now.
On this picture you can see what components make up the final conditional color chart.
The ‘Result’ column’s values represented by grey color, the red and green markers (Series 1 and Series 2) was shaped by the values of ‘Below’ / ‘Above’ columns and ‘Series 4’ represents the average.
We hope you also liked today’ presentation, we have made a special Excel chart that can be very useful for us even in situations that appear hard. The Excel template made by conditional formatting related to this article can be downloaded here!