Changing formatting for a percentile-based Color Scale

Ezzard

New Member
Joined
Feb 22, 2014
Messages
14
I have a list of numerical data and I want to use conditional formatting to highlight certain cells based on their percentile within the data. For the top 15% I want to color the data in one color and for the bottom 15% I want to color the data in another color. Any data that is in between should not have its formatting changed at all. I have tried using the color scale to format this data, but when I do so, the cells in between the data are scaled between the two colors for the top and bottom percentiles.

How do I conditionally format the top and bottom percentiles without formatting the rest of the data at all?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
Assuming a data set in A1:A10, maybe these CF formulas would work for you?

=A1 >= PERCENTILE($A$1:$A$10,0.85)

=A1 <= PERCENTILE($A$1:$A$10,0.15)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,462
Messages
5,528,950
Members
409,848
Latest member
Blomsten
Top