# Conditionally Format Chart’s Background in Excel – How To

1
8373

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:

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!

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:

So it is really cool. So lets do it!

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

=SLOPE(B2:B12,A2:A12)

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.

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.

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.

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

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:

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.

With few more cosmetic changes this is what we have:

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

## Check out more of our conditional charts tutorials:

SHARE
Teaching professional business subjects to the students of FIA, ACCA, CIMA, CA etc. He also found ACCA LIVE which is Pakistan's first portal to provide online classes and distance learning solutions to FIA/ACCA students. At PakAccountants.com he is busy making study material for different qualifications. Beside writing articles he answers questions asked using ASK TUTOR!