Conditional formatting slows down spreadsheet

[0nyx175]

Hi guys.

I have a spreadsheet which shows information about internal complaints against people in our business from other areas of the business which has the following columns

Date
Staff member 1st name
Staff member surname
Staff number
Job
Department
Based
Company
Complainant
Complainant Surname
Complainant Staff number
Complainant Job role
Complainant based

I need to be able to easily identify those who come up more than once (staff # complained about, area of the business complained about, staff # making the complaint, area of the business complaining) so that we can highlight repeat offenders. Firstly I set up conditional formatting but that only let me have three pieces of criteria so I then set up four columns:

Staff # complained about
Area complained about
Staff # complaining
Area complaining

I used conditional formatting in each cell with this code

=COUNTIF($I:$I,I2)>1

Obviously changing it depending on which area im looking at.

The problem with this is that it is really really slowing down the spreadsheet. Can anyone suggest a better way to do this?

 

[Jerry Sullivan]

Hi 0nyx175,

Conditional formatting formulas are recalculated each time anything calculates.

You might have better performance if you put your formulas in the worksheet cells instead of the CF formulas. You can then just use CF to highlight cells or rows based on the resulting value of the formula.

 

[Norie]

You could specify the range in the formula, eg I2:I2000, instead if using entire columns.

 

[Jerry Sullivan]

You could specify the range in the formula, eg I2:I2000, instead if using entire columns.

Norie,

That seems pretty intuitive, particularly if the CF formulas are complex.
I saw this advice by David McRitchie, and I'm not sure how to interpret it as relates to the OP example.

http://dmcritchie.mvps.org/excel/slowresp.htm#likeformats

I understand his point that it is better to have one rule than many, both for file size and performance.
Based on that post, do you think Mr. McRitchie would advise using I:I in this case?

 

[0nyx175]

All good ideas, especially the I:I alternative, I shall look this aft and let you know how it went!!

 

[Norie]

Jerry

I'm not sure what you mean.

Are you saying that the full column reference should be used?

 

[Jerry Sullivan]

Jerry

I'm not sure what you mean.

Are you saying that the full column reference should be used?

Norie,

No, I wasn't suggesting that. I was saying that it's intuitive that limiting the range in the formula would be better;
and because of that I am puzzled by David McRitchie's advice to apply CF to entire columns.

I suppose it isn't fair or polite of me to ask one expert to comment on another expert's recommendation.
I assumed that both pieces of advice are correct and that you might help me reconcile them.

"You could specify the range in the formula, eg I2:I2000, instead if using entire columns."

Your comment refers to the formula and not the CF "Applies to: " reference;
however in this context those ranges should be the same.

 

[Norie]

Jerry

I couldn't access the link earlier but it doesn't seem to mention conditional formatting when talking about applying to a whole column.

I wasn't even thinking of the range the CF was being applied to, just saw the formula itself.

I think that might be because I saw a workbook full of array formulas using full column references which strangely was taking a long time to calculate.

So now whenever I see a reference to an entire column...

 

[Jerry Sullivan]

Norie, Thanks for the explanation.

The linked article starts with:
Format entire columns instead of individual cells (#likeformats)
Color, format, or conditionally format the entire column at one time, ....

I believe the emphasis of this tip was on not having separate CF rules applied to each cell and instead apply one rule to the entire range (which could be a Column).

Applying the CF to the entire Column is convenient and it avoids the risk of the user unknowingly adding data below the CF applies to range, so for simple CF rules (eg A2>0), applying the CF to the entire Column might be the best choice. However for more complex formulas, limiting the range seems clearly better.

 

[Norie]

Jerry

I was just going to have another look at the link but I'm getting the same error message again.