# Conditional Format using Icon Sets

#### brian6464

##### New Member
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
is your cell formula in relative or absolute refernce? double check that

I usually highlight the whole column and add in the formula

#### brian6464

##### New Member
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.

#### brian6464

##### New Member
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.

#### AhoyNC

##### Well-known Member
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.

#### brian6464

##### New Member
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.

#### brian6464

##### New Member
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.

Replies
16
Views
382
Replies
0
Views
92
Replies
1
Views
356
Replies
1
Views
290
Replies
4
Views
124

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?

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