Alternative to cell highlighting using conditional formatting in Excel without causing freeze/crash

Question

In column A, I have a value and I want to highlight if it doesn't match the same value in column B.

I'm using the formula below in conditional formatting which works great.

=ISERROR(MATCH(A1,$B:$B,0))

After highlighting them, I need to be able to sort/filter by cell colour so I can easily find non-matching rows.

However as my data is often 60k+ rows, this often makes Excel freeze up and/or crash.

I know conditional formatting adds a lot of weight to a spreadsheet, so what lighter alternative is there for what I'm trying to do?

Answer 1

Adding the same formula in column C instead of (/ additionally to) conditional formatting should have the best performance. If that still isn't good then consider converting these formula to static values (of course you'll need to update it manually, but this way you can add conditional formatting on column C). Are you sure you need to keep columns A and B next to each other? If you could move column B to a different sheet and sort it that would be again quicker. Other options would be VBA but it's surely slower than using built in formulae.