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

Question

My Excel spreadsheet is formatted to look like a report card. Percentages are used to indicate the values. I want to use three different colours to highlight all numbers between 0 and 49, 50 and 99, and 100. When I make a new rule, it's difficult for me to apply these three separate conditions in the range. If that makes sense, it continues presuming that I want to evaluate the numbers relative to one another rather than just the value in the cell. We appreciate any assistance!

Answer 1

Three distinct rules are required, one for each circumstance. Choose the range, then use a formula to generate new rules. The formulas I used in this example are visible. Keep in mind that the formula must utilize the first cell in the selection as a reference and should not contain any $ signs, so use C2 rather than $c$2. C2 is the initial cell in the selection in my example, and my rules are:

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

Take a look at the screenshot to see how the rules must be set up such that cells with less than 50% of the yellow colour are not affected. For added assurance that no additional rules are enforced, you might optionally check the "Stop if true" box.