Conditional Format Slows Down Excel

3
votes

I'm using excel Conditional Formatting to find duplicate rows by searching through multiple columns, and if those columns match in another row, it'll highlight those duplicate rows. The problem is, that when I use that conditional formatting, it really slows down the sheet. Selecting from a drop-down list takes 3-5 seconds, and copy and pasting takes a few seconds. I'm probably at most, pasting about 100 rows and about 8 columns.

Is there a way to make the sheet more efficient? Will creating a macro speed up the Excel?

Here's the formula. 

=COUNTIFS($B:$B,$B1,$C:$C,$C1, $E:$E,$E1,$F:$F,$F1,$G:$G,$G1,$I:$I,$I1) > 1

and it applies $A:$I.

Thanks!

1
vbaexcelconditional-formatting
In most cases, formulas work faster than VBA code. – braX

1 Answers

3
votes

Change your COUNTIFS so it doesn't search the entire column, but a limited range. For example, 1000 rows:

=COUNTIFS($B1:$B1000,$B1,$C1:$C1000,$C1, $E1:$E1000,$E1,$F1:$F1000,$F1,$G1:$G1000,$G1,$I1:$I1000,$I1) > 1

That should improve the performance substantially. If your data has too dynamic of a size for that to work, switch to using tables and when you select the whole column table you'll get a field-based formula which only selects the cells needed.