#### bomberman411

##### Board Regular
Hello again to all,

I have a conditional format that colors dubs with yellow using this code:
Code:
``=ET(NB.SI(\$D\$10:\$D\$5000;D10)>1;NON(ESTNUM(TROUVE("?";D10)));D10>10000)``
I also have a hidden column that show "true/false" depending on if, on that same row, there's a dup or not.
Code:
``=ET(NB.SI(\$D\$10:\$D\$5000;D10)>1;NON(ESTNUM(TROUVE("?";D10)));D10>10000)``
I also have another cell which counts the dups and displays the total amount.
Code:
``=NB.SI(\$I\$10:\$I\$5000;"VRAI")/2``

Everything works as it should, and I'm pretty happy with it BUT...

The problem is, this process slows down the spreadsheet a lot.
For example, any new data entered in my D column (column where the dups are highlighted in yellow), it takes a good 5 seconds (probably a little more) before I can do anything else with my spreadsheet. Same thing if I do a SORT of any kind.

Is there anyway for me to get the speed I would like without sacrificing the functionnality I already have?

P.S.: sorry for the french formulas, my excel is in french. If needed, I will translate.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### bomberman411

##### Board Regular
I know for a fact that the thing that slows everything down the most are all the TRUE/FALSE in my hidden column...

So is there another way to count the dups without slowing everything down?

#### bomberman411

##### Board Regular
Thanks anyway, but I will live with the slowness ;P

#### Dave Patton

##### Well-known Member
In a column that you can hide,
a) try calculating duplicates with =COUNTIF(\$D\$10:\$D\$100,D10)>1 fill down
b) reference this column for the Conditional formatting instead of including in the Conditional formatting formula.
c) count of duplicates might be like =COUNTIF(E:E,TRUE)

You could search for information on Dynamic Ranges so you do not use formulas in more cells than necessary or if you use Excel 2003 or later use built in feature (2003 Data List).

You could convert the formulas to values prior to sorting etc.

If the above doesn't help, post a very concise example using tools mentioned at the top of the list of messages.

Replies
1
Views
128
Replies
0
Views
91
Replies
3
Views
915
Replies
6
Views
173
Replies
1
Views
146

1,191,366
Messages
5,986,242
Members
440,012
Latest member
StumpedGump1987

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back