Conditional Formatting slowing the spreadsheet

[gberg]

I have a table (45 rows x 109 columns). One column is named "Task", the task column has Data Validation that allows the user to pick from 39 different tasks.

I have conditional formatting for each of the 39 tasks that applies a "Fill Effect" to each cell in the row based on the task that is selected.

This is causing the spreadsheet to have a noticeable delay when any information (the information being entered are just numbers) is entered into one of the cells

Is there anything that could be done or is this just an issue with conditional formatting?

Here is a screenshot of some of the conditional formatting rules I have setup

 

[6StringJazzer]

The issue is that you are using INDIRECT in your rules. INDIRECT is a volatile function. Most Excel functions need to be recalculated only if there is a change in one of the cells they reference, but a volatile function must be recalculated every time there is any calculation.

Why are you using INDIRECT to reference named ranges? Why not refer directly to the range name:

=$B6=Task_01

 

[Alex Blakenburg]

I am sure that Indirect makes it that much worse but my understanding is that Conditional formatting is also volatile.
Exploring Conditional Format Performance Part 1: What’s slow, whats buggy and whats faster!

Do you have stop if True ticked on each condition ? Having it turned on may help speed it up a little.

 

[gberg]

I tried doing as 6StringJazzer suggested and removed the INDIRECT reference (guess I was overthinking things). But as Alex points out, it must be volatile because even with the INDIRECT reference gone it is still slow. I will try to "stop if true" and see if that resolves things, or just do away with the conditional formatting altogether

 

[6StringJazzer]

I am sure that Indirect makes it that much worse but my understanding is that Conditional formatting is also volatile.

I will take a look at that link. Unfortunately Microsoft is really bad at providing rigorous technical documentation about any of their features so it is difficult to know what is the underlying implementation.

But a CF rule that compares one value to another values is so simple that if that type of rule causes performance issues, then any CF will cause performance issues, which we know is not the case. So there may be more to @gberg's situation than meets the eye. Also the fact that each one of those rules is formatting 4770 cells might be coming into play.

 

[gberg]

I played around a bit. The more complex the conditional formatting (i.e. number formatting, text formatting, fill, etc.) the worse things get (longer delays after entering in new text) over just say changing the text to "Bold" as the only formatting option. When you start getting into "Fill Effects" and then put other conditions on top of that it seems to get slower as more things are introduced into the conditional formatting. I'm guessing that each formatting item is handled separately and "adds" to the number of calculations being preformed with each change being made to the worksheet table. This along with the almost 5,000 cells being looked at for each of the 39 conditional formats seem to take it's toll in the performance of Excel.

Thanks again for the info and suggestions but I think I will just do away with the Conditional Formatting for this sheet, it's not worth the performance issues

Thanks,

Greg

 

[Alex Blakenburg]

I would treat Conditional formatting the same as a volatile function and only use it sparingly and not on thousands of rows.
The link I gave to Charles Williams Fast Excel article was just the first of a 3 part series he did on it.
I believe a lot of revolves around screen refreshing so it might be interesting to see if the performance improves if you shrink the screen to only the small number or rows & columns you are actually updating.