Conditional Formatting using Icons

[rajeev0508]

Hello All,

I am no expert in Excel but also not a newbie. I am currently working on a project wherein I have to do a comparison between the Actuals and Budgeted Expenses in a Cash Flow Statement. I have almost achieved what I wanted but for a small issue where I have been stuck. I have also done the percentage wise comparison between the two (Actuals & Budget).I have been trying out conditional formatting with the help of Icon Sets but to vain.
Now the issues here are as follows:

1 - When a particular percentage is higher than or equal to "0" and less than "4" then it should indicate a green Icon.
2 - When a particular is less than "0" uptil "-5" or "-4" it should still indicate a green icon but anything after "-5" ("-6","-7","-8" so on) should be indicated with orange and anything after that should be a red icon.
3 - Any percent higher than "5" but less than "8" should be indicated by an orange icon.
4 - Any percent over and above "8" should be indicated with a red Icon.

Example as below.

Example.jpg

Although I have been able to achieve the points 1,3 & 4 but again if I have to do it, I should do conditional Formatting individually to all the cells and not the whole worksheet, because the icons are then arranged taking base the previous percents and not the individual values.

My questions are:

1 - Can something be done wherein I can Format the whole worksheet and not each cell, because that takes a lot of time.
2 - Can something be done to get the desired result when a percentage goes below "0" as in the point 2.


Any advice pertaining this is very much appreciated.

[rajeev0508]

Hello Guys,

Any suggestions on the above mentioned?

[krishnakuma6]

Hai Rajeev,

For your query , I attached sample sheet for you

In that C Column, I used "Wingdings 2" Font instead of Icon and then I used conditional formatting, Could please check and revert me

[rajeev0508]

Hi Krishnakumar,

Thats exactly how I wanted. Thanks for that.

But when I am trying the same thing on the original sheet with your formula, I am getting "~" symbol. Any idea how I can eliminate that?

[rajeev0508]

Hi Krishnakumar,

Thats exactly how I wanted. Thanks for that.

But when I am trying the same thing on the original sheet with your formula, I am getting "~" symbol. Any idea how I can eliminate that?

[rajeev0508]

Hello Krishnakumar,

I have now got the desired result, the way I wanted it on my sheet. Thanks for all the assistance.