Conditional Formatting bar chart

I’ve a table of data with target values and actual values, by month, like this:

Conditional_Formatting_Chart1

I want to create a bar chart with this data, something like this:

Conditional_Formatting_Chart2

But I would like, for better understand the data, to see if the actual values, for each month, are above or below the target value. If it’s on target, or over the target, I would like the month bar to show up in green color and, if it’s below target, to show up in red color. This can be done by adding two columns to our table, one for the results “under target” and one for the results “over target”. On this two columns, what we are doing, is to check if the actual sales value is less that the target, then show the actual value; if not, leave the cell blank. This for the “under target” column. For the “over target” column, we are going to check if the actual sales value is equal, or greater than the target value. If true, then, show the actual sales value; if not, leave the cell blank. The result will be like this:

Conditional_Formatting_Chart3

For the “under target” column, put this formula on cell D2 and copy down:

=IF(C2<B2,C2,””)

For the “over target” column, put this formula on cell D2 and copy down:

=IF(C2>=B2,C2,"")

Now we need to change our chart data source. If you right click on your chart and select “Select Data”, it will open the “Select Data Source” dialog box, like this:

Conditional_Formatting_Chart4

As you can see, your chart has only one data series. We are going to work with two data series: one for the “under target” values and one for the “over target” values. To start, we are going to change the series values for the existing series. For that, click on the Edit button on the Series box, on the left of the dialog box. This is pointing to column C, as you can see on the image below. We need to change the “Series name” to “=Sheet1!$D$1” and the “Series values” to “=Sheet1!$D$2:D$4” and click the OK button.

Conditional_Formatting_Chart5

Our chart series in now point it out to the “under target” column. We need to add the second series for the “over target” values. Click on the Add button, on the Series box:

Conditional_Formatting_Chart6

Fill out the “Edit Series” dialog box, like this, and click on the OK button:

Conditional_Formatting_Chart7

As you can see, you need to specify the “Horizontal Axis Labels” for this new series because it’s not showing up the months names. Just click on the Edit button and select the “=Sheet1!$A$2:$A$4” range.

Conditional_Formatting_Chart8

Close the “Select Data Source” dialog button, by clicking the OK button, and your chart should now look like this:

Conditional_Formatting_Chart9

We need to change the color of our series bars to display the “under target” values as red bars and the “over target” values as green bars. Just right click on the “over target” bars and select “Format Data Series”. Go to the Fill group and select “Solid fill” with a green Fill Color. Click the Close button when finished.

Conditional_Formatting_Chart10

Do the same for the “under target” series but for this select a red Fill Color. Before clicking on the Close button, select the “Series Options” group and on the “Series Overlap”, put 100% so that the chart bars appear center between each month.

Conditional_Formatting_Chart11

When you click the Close button, the result should be like this:

Conditional_Formatting_Chart12

Try changing any value on the B2:C4 range to see the conditional formatting of the chart adjust the colors.

3 comentários:

Kacka said...

Hi, very useful piece, but what about a situation that I need to display values for each column? In that case I end up with actual value for displayed column and 0 for the not displayed one :/

baca selengkapnya said...

Pretty nice post. I just stumbled upon your weblog and wished to say that I’ve really enjoyed surfing around your blog posts. After all I will be subscribing to your feed and I hope you write again very soon. Thanks!

Anonymous said...

This was useful for me..