Conditional formatting for comparing cells

Occasional Contributor

I have a 2000+ row spreadsheet that I have columns that compared with v-lookup. I copied and pasted values for the lookup in a new column and now I need to highlight the rows that don't match. I have tried conditional formatting with the add new rule>Use a formula to determine which cells to format and the formula I use is =$A:$A<>$B:$B and click OK (I don't care what the colors are). It isn't working. There are obvious differences, but none of my cells are highlighted.


The sample I have loaded contains 18 rows of the spreadsheet and I need to compare columns A to B and highlight each row that is A3 is obviously different than B3. I would like to create a rule that would compare A3 to B3 and let me know that it is different so I can go change it in a database.

I hope I am clear on what I want to do

7 Replies


The Column B "visually" looks the same values of Column A
But you have a lot of "extra" blank spaces on column B.
So in this case is recommended remove these extra spaces by using the TRIM() formula.



I ran the formula but then how do I get the cells highlighted?
Have a look on the attached file.
Got it - Thanks

I must have something else wrong when I converted the formula to the actual worksheet. Not all of the non-matching cells highlighted. ie G3 & H3 or G4 & H4. See attached. @Juliano-Petrukio 


It looks all correct.

I made a small change on your file to optmise it.

Your file is very big even with a small quantity of data.

The main reason is because you expanded the formulas to the all rows of the spreadsheet. Plus than1 million rows (1,048,576). 

Thank you but apparently I did not convey what I needed exactly. I need differences in each row in those two columns highlighted. G3 and H3 are different, so I would like one of those cells to highlight telling me they are not a match. G2 & H2 are the same so they would not highlight.