Conditionally Format Chart’s Background in Excel – How To

1
7875

Excel charting is in the air for last few weeks and we are learning some really cool tricks on making charts better looking and also fulfilling the purpose. Today we will learn how to make Excel charts with background that changes with the change in certain condition.

Every avid reader I think knows PHD yup the Pointy Haired Dilbert. Yeah he was PHD (and still is) before he became Chandoo. I was going through emails before a good night sleep that I read his latest article and went through the technique he employed. He discussed two possibilities and immediately the third sprang to my mind and thus writing it immediately.

And this is what I got with my attempt on the same requirement:

cond back 1

Download Fully worked Excel file
Don’t have time to go through the whole tutorial? You can now download the file for a little premium instantly and can read the tutorial later too!

Buy Now

Got a question? Contact details are here

This technique require no VBA and even no conditional formatting limitations like keeping the chart at one place or not changing chart dimensions etc:

cond back 2

So it is really cool. So lets do it!

Download Basic Excel Tutorial file
Click here to download the Excel tutorial file that contains basic data and will help you to follow the steps discussed in this tutorial

Step 1: Open the tutorial workbook you just downloaded.

Step 2: Go to cell B14 and put this formula:

=SLOPE(B2:B12,A2:A12)

cond back 4

Step 3: Go to cell C1 and make a heading Green and in cell D1 Yellow.

  • Go to cell C2 and put this formula:
    =IF($B$14>0,9000,NA()) and double click the fill handle
  • Go to cell D2 and put this formula:
    =IF($B$14<0,9000,NA()). Double click the fill handle to populate the cells with the same formula.

cond back 5

This will help us in getting the background conditionally colored depending on the data.

Step 4: Select the range in columns A to D. Go to Insert tab > Line chart > Line with markers. This will insert the chart.

cond back 6

Step 5: Having the chart active go to Design > Add chart element drop down button > Trendline > linear. A dialogue box will appear to ask for what series you require trend line. Select sales and click OK.

cond back 7

Step 6: Remove the unwanted chart items like labels and lines by selecting them and hitting delete button.

cond back 8

Step 7: Right click on the green line appearing at the top and from the menu select change series chart type.

Step 8: For green and yellow select stacked column chart type.

Step 9: Right click on green bars and select format data series. And reduce the gap width to 0%

Following animation will walk you through above steps:

cond back 9

Step 10: To change the color of background to your liking, having the chart active go to format tab > current selection group > select green series from the drop down menu and change the fill color. Same way, select the yellow series and change the fill color you like.

cond back 10

With few more cosmetic changes this is what we have:

cond back 11

Now change the value of March to see the effect instantly. Pick a number that decreases the trend like 5000

cond back 12

Check out more of our conditional charts tutorials:

  1. Excel Chart with highest value in different colour – Multi Color Bar Charts [How To]
  2. Variance Analysis in Excel – Making better Budget Vs Actual charts

1 COMMENT

LEAVE A REPLY