excel conditional formatting help

[Glasgowrob]

using conditional formatting on percentages in excel, but my scales a bit skewed,

is there an easy way to run the scale 0-100% with gradient colouring, without outlying numbers affecting the scale



ie with this i have a few well over 100% which makes the rest show as amber or even orange when they should be green

[Zetec-S]

Edit the rule and set your min, max and midpoint:

[bigandclever]

Change the max to 1.0000000001 and set to White.

Won't be as pretty a colour range because you're only playing with 2 colours then, but it works in not formatting > 100% data..

ETA Maybe I misread the request

[louiebaby]

Zetec-S said:

Edit the rule and set your min, max and midpoint:

Beat me to it.

[Glasgowrob]

thanks Zetec-S

[Zetec-S]

[paulrockliffe]

Create a second rule the formats values greater than 1 as white (or whatever you want those to be). Put the two rules int he right order and you will get the three colour range for 0 to 1 and the 4th colour for greater than 1.