Conditional Format using Icon Sets

brian6464

New Member
Joined
Nov 20, 2013
Messages
34
I must be missing something easy.

Let's say I use the 3 stoplights - Red, Yellow and Green.

My range is defined as follows:

>= 20 percent = Red
< 20 percent but >= 15 = Yellow
< 15 percent = Green

When I apply this to a worksheet range, say Q15:Q18, the cells do not format properly.


I can have the same value, say 5.8%, in Q15 and Q16. They should both be Green, but one is green and one is red.


I can set up conditional formatting separately for each cell and it works, but when I setup one rule for multiple cells, it does not work.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484
is your cell formula in relative or absolute refernce? double check that

I usually highlight the whole column and add in the formula
 
Upvote 0

brian6464

New Member
Joined
Nov 20, 2013
Messages
34
Actually, I get it to work properly if the formatting rule Type is set to Number and the cells are worksheet cells are formatted as such.


However, if I select Type Percent within the formatting rule and the values to be formatted are %'s, the conditional formats do not work right.
 
Upvote 0

brian6464

New Member
Joined
Nov 20, 2013
Messages
34
is your cell formula in relative or absolute refernce? double check that

I usually highlight the whole column and add in the formula

I have tried it both ways.

Also, the column contains multiple KPI's with different meanings and would require different formats, so I think I need to range them.
 
Upvote 0

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
5,279
Office Version
  1. 365
Platform
  1. Windows
ADVERTISEMENT
Sounds like you have it figures out. You can format your cells as a percent. In the format rule set TYPE to number and then 20% would be 0.20, 15% as 0.15, etc.
 
Upvote 0

brian6464

New Member
Joined
Nov 20, 2013
Messages
34
Sounds like you have it figures out. You can format your cells as a percent. In the format rule set TYPE to number and then 20% would be 0.20, 15% as 0.15, etc.

You are right. It worked perfectly that way. Thanks!



Still trying to figure out how to do it if I use type Percent though.
 
Upvote 0

brian6464

New Member
Joined
Nov 20, 2013
Messages
34
Re: <<Solved>> Conditional Format using Icon Sets

Take a look at this link which explains how Excel uses the percent in conditional formatting.

Excel Icon Sets - Easy Excel Tutorial

Thanks. The link shows that using Percent TYPE in conditional formatting will automatically adjust the quartile ranges based on the values of the data. Seems odd to me, but explains why I was having difficulty trying to set my own ranges.
 
Upvote 0

Forum statistics

Threads
1,195,631
Messages
6,010,783
Members
441,569
Latest member
PeggyLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top