Line of Business (LoB) wise CAGR (%) is shown in range A3:B8. A benchmark growth rate is shown in cell B11. The task to show a column chart of the data presented in range A3:B8. LoB's with a CAGR (%) greater than the benchmark rate should appear in Green colour and those with a CAGR (%) lesser than the benchmark rate should appear in Red colour. The colour of the column should change when:
a. Data in range B4:B8; and/or
b. The benchmark rate in cell B11 changes
You may refer to my solution in this workbook.
Thanks Ashish - a very useful solution and worked beautifully!
You are welcome.
Ashish - I have a need to show Planned Vs Actual revenue for each quarter for year 2012 in bar chart. If the Actual bar is taller than Planned bar, it should be of GREEN color. If Actual revenue is less than planned, it should be of RED color.
The need is to show both values - Actual as well as planned.
Any suggestions on how to do it?
Thanks
Hi,
Upload your file to SkyDrive/Google Docs etc. and share the link of the uploaded file here.
Hi Ashish,
Thanks for looking into this.
Here's the link to the document uploaded on Google drive
https://docs.google.com/file/d/0B0J_aAhANoRyQUw3cWhZdDBuUmM/edit?usp=sharing
Hi,
See if this works.
Hi Ashish,
Liked the idea and it almost worked. Until I had to add data labels
For make it easy to compare, I had to show the value for each bar and then the 0s (Zero) started showing up.
Wondering if there's a way to suppress data label if it is zero as shown in this doc- https://docs.google.com/file/d/0B0J_aAhANoRyQUw3cWhZdDBuUmM/edit?usp=sharing
You're site is very informative. Wish I had found it earlier.
Thanks for your help
Hi,
To suppress the 0 data labels, try this
1. Select anyone data label so that all data labels for a series get selected and press Ctrl+1.
2. Under Number > Category > Custom > Type, type the following format to suppress 0's
#,##0;-#,##0;;
3. Click on OK/Apply
4. Repeat steps 1-3 for the other data series as well.
This is what I did on my uploaded file to suppress 0's on the chart.
I am glad you liked my site.