I have an excel spreadsheet set up like a report card. The values are given as percentages. I'm looking to highlight all values between 0-49 in one color, 50-99 in another, and those equal to 100 in a third color. I'm having trouble applying these 3 different conditions in the range when I create a new rule. It keeps assuming I am wanting to score the numbers as a percentage of each other rather than just the actual number in the cell if that makes sense. Any help is appreciated!

CodePudding user response：

You need three separate rules, one for each condition. Select the range and create new rules that uses a formula. You can see the formulas I used in this example. Note that the formula must reference the first cell in the selection and should not contain $ signs, i.e. use C2 and not $c$2. In my example, C2 is the first cell in the selection and my rules are

=C2<0.5
=C2<1
=C2=1

Look at the screenshot and see how the rules must be ordered so the yellow is not applied to cells under 50%. You could also use the tick box for "Stop if true" to make sure no further rules are applied.