Conditional Formating not showing correct results

Question

I am having a problem while applying conditional formatting in MS Excel 2016.

I have two numeric columns [P & Q]. And want to highlight cells in column P where the value is greater than its corresponding cell in column Q. I have used the following formula.

=$P1 > $Q1

In conditional formatting. The formula would be applied on some 1500 rows in column P. However I am getting wrong results as well as correct ones. I have attached an image of some results. Need help.

Answer 1

Simply change the change the formula to

=$P2>$Q2

It will work. Make sure to give the reference of the “first cell to be evaluated” in the formula.

In your case it is the cell in 2nd row not the first row.

Answer 2

The problem is that you have used conditional formatting incorrectly, it doesn't quite work the same as doing a standard excel formula.

For the results needed, you need to do something similar as below:

Where your rule should be:

Cell value > $Q1

Don't worry that we haven't referenced the other cells in Q, excel is clever enough to know to apply the rule down the column and change the row number where applicable.

You then need to apply it to the column:

=$P:$P

This will give you the result that you need.

Answer 3

I have tried the same

=$A2 > $B2

for 40 sample columns, and found the result correct.