Conditional Formatting only seeming to apply if the 'Less than' criteria is selected

Question

I am trying to apply some conditional formatting to a cell on Google Sheets. I want the cell to change green if it is larger than another cell, red if it is less, and yellow if it is the same.

However, for some reason when I add these rules, the only one that seems to take any effect is the less than criteria.

I have tested it somewhat and it seems that the formatting isn't applied unless it is the 'less than' criteria that is selected. So for instance, I tried out the greater than criteria, and made it true and it didn't apply the formatting, but as soon as I changed it to less than it worked.

Not to mention that it applies the formatting regardless of where the criteria is met or not and only doesn't apply the formatting if the cell is empty.

This isn't the end of the world, but it is frustrating and would make the at a glance of the sheet I want to apply it to much nicer.

I've likely missed some important details, so I'm obviously open to questions.

Answer 1

Most likely, you are using the built-in "Greater Than," "Less Than" and "Is Equal To" drop-down choices in the Conditional Formatting dialog window. Those only work with numeric values, not cell references.

On the ASCII chart, all numbers come before the letter "A." That is why your "Less Than" seems to be working: because any numeric value will fall before any cell reference, since all cell referenced begin with a letter of "A" or higher.

Where you are currently choosing "Greater Than," etc., in the drop-down, you'll want to choose the last option: "Custom formula is..."

Let's say the range to which you're applying the rule is A2:A, and that the cell reference to which you want to compare is E2. You'd write your formulas for the custom field like this:

=A2>E2

=A2<E2

=A2=E2

You only need to reference the top-leftmost cell in the applicable range; the rest is interpolated from that, or should be in your case as described.