# More than top 10% in conditional formatting

#### jonislindwert

##### New Member
Hi,

I have a little problem with conditional formatting.

I want to have intervalls of the conditional formatting, not only top 10 or bottom 10 %.

Eg. I want all top 10% people to have green background, I want the following 11-35% best people to have light green, 36-65% yellow etc

As it is now I can only choose the top 10%, and the bottom 10%, but I want to have intervalls for those in between aswell. Is this possible to do?

This will be an monthly report so I cant use any fixed values, they need to be ranked of the total amount of persons, where the best 10% is green, the next 11-35% is light green etc.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Welcome to the Board!

You could do it with a custom formula in the conditional formatting. The following formula will give you the top 10% and 11-35% and you can keep going. You will need to make sure that the 10% conditional format is first in the order of precedence and so on.

=A1>=LARGE(\$A:\$A,INT(0.1*COUNT(\$A:\$A)))
=A1>=LARGE(\$A:\$A,INT(0.35*COUNT(\$A:\$A)))

There may be a better way, but this is the first idea that came to my head.
Excel Workbook
A
169
23
384
479
551
643
755
874
930
1079
1184
1246
1397
1490
1571
1691
1797
1874
1991
2014
2148
2237
2310
2484
2582
2679
2735
2860
2931
3045
3159
3274
3342
3481
3530
3693
3775
Sheet1

Note that if you have other numbers in the column that can't be counted in the percentile, you wouldn't be able to use the whole column A as in this example.

Replies
0
Views
129
Replies
1
Views
729
Replies
5
Views
113
Replies
8
Views
383
Replies
2
Views
99

1,203,350
Messages
6,054,905
Members
444,760
Latest member
TeckTeck

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

### Which adblocker are you using?

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

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