Conditional Color Chart

The conditional color chart is an interesting experiment for extending the 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 conditional formatting in Excel with cells. However, 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 to the cells, then we are talking about conditional formatting. Today’s presentation aims to display values by assigning color codes in connection with larger or smaller than a given parameter (average).

Unfortunately, Excel doesn’t support this method, but there is a solution for displaying values different from the average (in the 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 static. We have represented the average by a blue broken line. We will compare to this the points of daily values and differences. Thus, below average is represented by red, and above average is green.

The animation shows how the display will change if we change the value of the ‘Target’ (average) field. At first glance, everything seems 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. For example, notice that the sixth data point from the left has changed! (It’s value stayed: 39).

conditional color chart template

Conditional Color Chart – Calculated Fields

We will create a four-part conditional color chart based on the data seen in 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 compared to the average found in the ‘Target’ column.

conditonal-color-chart-math

The most interesting for us is the columns ‘Below’ and ‘Above.’ It can easily be decided using the Excel IF formula if the ‘Result’ column’s values are larger or smaller than the average, namely the value of the ‘Target’ column.

Two values can be placed in the ‘Below’ column; if Result < Target, then the formula gives a #N/A value, and if Result > Target, then the column 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 the chart will be empty at the columns containing the values #N/A to draw anything. And this is what we need right now.

In this picture, you can see what components make up the final conditional color chart.

The ‘Result’ column’s values are represented by grey color. The red and green markers (Series 1 and Series 2) were shaped by the values of ‘Below’ / ‘Above’ columns, and ‘Series 4’ represents the average.

conditional color chart fin

We hope you also liked today’s presentation. We have made a special Excel chart that can be very useful for us even in situations that appear hard. The template made by conditional formatting related to this article can be downloaded here!

Resources: