Conditional Formatting bar chart

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


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


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:


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


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


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:


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.


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:


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


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.


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


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.


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.


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


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

10 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..

Unknown said...

Jacket Leather Indonesia
Civet Coffee Indonesia

anne jelita brenda anne caca tasya ruth
bantal web jual bantal bantal bantal silikon pembicara hellow bumbu bumbu bumbu bumbu bumbu distro distro


VinnyO said...

Nicely done! Thank you for your efforts and for sharing! This is exactly what I needed. The only thing missing was how to resize the graph elements but once I figured this out I was golden! Thank you so much!!

obat kuat cialis 80mg said...

kondom lele
obat pemutih gigi
obat penyubur seperma
obat penggemuk badan
obat peninggi badan
obat pelangsing badan meizitang
obat pelangsing badan body slim herbal
obat pelangsing badan fatloss

Anonymous said...

Helpful article! Thanks heaps!

Puremelda said...

If handling your academic obligations seems hard consider Essay Writing Help Services from professionals for your Custom Essay Writing Services and Legitimate Custom Writing Services.

prashant said...
This comment has been removed by the author.
assignmentauthors said...

I really love this, great content assignment expert