Slow Excel spreadsheet

[bomberman411]

Hello again to all,

I have a conditional format that colors dubs with yellow using this 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.

=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.

=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.

 

[bomberman411]

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]

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

 

[Dave Patton]

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.
I had no resources to translate your formulas.