How to add Conditional Formatting in Excel for a Range of Values

Question

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!

Answer 1

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.