Conditional Formatting always Volatile = speed killer. Idea to optimize?

d0rian

Active Member
Joined
May 30, 2015
Messages
263
So in trying to troubleshoot my laggy file, I got rid of all Volatile functions (which I understand to be speed killers), only to learn that the #1 culprit may in fact be the 120 conditional formatting (CF) rules I have, which my research indicates are always volatile, which I guess makes sense. But I really can't do without the CF, because it provides the highlight-indicators my file (which pulls in real-time stock quotes) requires. This 3-part series gave me some good background on CF, and suggested that CF rules themselves not be overly complex because they're always volatile aka/ get re-calculated with every recalculation on the sheet.

I want to know if the following idea has any merit: what if I take every CF rule/formula and make them into IF formulas that return "x" if true in unused columns in my sheet, and then change every CF rule/formula to a simpler IF([cell-reference]="x").

For example, here's one of my current CF rules that applies to range $BY:$BY

Code:
=AND(ISNUMBER(BY1),OR(ISNUMBER($GM1),ISNUMBER($GN1)),OR(BY1>$GN1,BY1<$GM1))

What if I instead put this formula into cell JA1:

Code:
=IF(AND(ISNUMBER(BY1),OR(ISNUMBER($GM1),ISNUMBER($GN1)),OR(BY1>$GN1,BY1<$GM1)),[B]"x"[/B],"")

And then I simply changed the Conditional Formatting rule (that applies to $BY:$BY) to:

Code:
=(JA1="x")

Would that have the effect of reducing the calculation time required by the always-volatile CF? I.e. would I essentially only be requiring the CF formula to check for a "x" value? Or would all of cell JA1's precedent cells also need to be recalculated by the CF function?

Hope that made sense.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
I'm guessing you'd gain a really tiny bit of speed. you could gain another really tiny bit by changing the proposed formula for JA1 to

=AND(ISNUMBER(BY1),OR(ISNUMBER($GM1),ISNUMBER($GN1)),OR(BY1>$GN1,BY1<$GM1))

This will then return TRUE or FALSE.
And in the conditional formula rule, simply have =JA1

For serious speed improvement a new approach/design is likely needed.
 

d0rian

Active Member
Joined
May 30, 2015
Messages
263
Thanks for reply. And good thought about skipping the whole "x" / "" thing with just TRUE/FALSE; if I implement this, I'll do that.

Still hoping to get an informed answer as to whether the strategy of outsourcing the CF rule's formula to a helper cell instead of the CF dialog box will sidestep the whole CF=volatile resource-drain. If the CF is truly just checking for an "x" (or a TRUE value if I use your suggestion) in the reference cell, then it seems promising. But if the CF will trigger a recalculation of the precedent cells that feed into the "x"/TRUE cell anyway, then it's probably moot. The good news, for anyone interested, is that the INDIRECT functions that I've gotten rid of seem to have been the cause of 80% of the lag...looks like CF may be responsible for the rest.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,478
Messages
5,601,900
Members
414,482
Latest member
morkar

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top