How to use Conditional Formatting. Red, Yellow and Green Cells

ccualumni

New Member
Joined
Sep 12, 2009
Messages
15
I am struggling trying to find the proper way to set this up, yet it seems like it should be so easy.

Let's say I have a set of data. The data can be just about any number, but I want to put the low range in red, the middle range in yellow and the top range in green.

For the top 35%, they would be green
For the middle 30%, they would be yellow
For the bottom 35%, they would be red.

For example:
if my Data set is: 1, 25, 45, 51, 80, 100
1 (would turn this cell red)
25(would turn this cell red)

45(would turn this cell yellow)
51(would turn this cell yellow)

80(would turn this cell green)
100(would turn this cell green)

I see where I can put above average, below average, or to set a specific range.

But is there a way to select the above without having to set 3 rules? i tried looking at stuff here and on Google, but could not find what I was looking for.

How do I set it so that a range of numbers can be put into the colors I am looking for?

I hope I explained that well enough.

Thanks
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Are you talking about a percentage of the whole. (Largest number)? Or, will you always be looking at a 1-100 data set/
lenze
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Look at the PERCENTRANK function...

Say those numbers you listed are in A1:A6
in B1 put

=PERCENTRANK($A$1:$A$6,A1) and fill down

You should be able to use that in conditional formatting like

Red would be
=PERCENTRANK($A$1:$A$6,A1)<=0.35
Yellow would be
=PERCENTRANK($A$1:$A$6,A1)<=0.64
Red would be
=PERCENTRANK($A$1:$A$6,A1)>=0.65
 

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
In Excel 2007 you can use Icon Sets within Conditional Formatting, which can put a red, yellow or green icon in the cell with the number based on it's percentile. But if you want the cell background or font to change, you'd have to use 3 formulas (conditions).
 

ccualumni

New Member
Joined
Sep 12, 2009
Messages
15

ADVERTISEMENT

Thanks for the replies.

the numbers in the columns will always be different and measure stuff like amount of time, number of sales, number of calls and such.

I will rank each column and want just the cell to change color, not the numbers (I could not figure out how to do that on here with my example).

Thanks for the help.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Jonmo's formulas work perfectly. You don't even need the helper column "B"

lenze
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Jonmo's formulas work perfectly. You don't even need the helper column "B"

lenze

Yeah, I guess that was a little confusing.
I only suggested the formula in column B as an example of how PercentRank worked.
That was not meant as part of the conditional formatting solution..
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,908
Members
414,110
Latest member
docops

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