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

[d0rian]

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

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

What if I instead put this formula into cell JA1:

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

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

 

[Fazza]

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]

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.

 

[llandyw]

Here's the real problem with conditional formatting. EVERY time you insert or add data to a row, Excel splits the conditional formatting.

So, say you have a brand new sheet, with data in it, and you add the conditional formatting to a column. You have a single conditional per condition, say there's 5 conditions, so you are just fine.

Now, you insert a single row somewhere the middle. The conditionals get split. Upper portion, new row and lower portion.
You started with 5 condition rules. Now you have 15.

Insert a new row, below the previously inserted row, and you now have 20. (the other rows are already split)
But, insert a new row in the middle of data where there's no split, you now have 5 sections, so you have 25 conditionals.

Doesn't sound too bad, does it? Well, lets say you have 4 columns, and all of the columns use a conditional.
To start with, you have 4x5 or 20 conditionals per row/column. Now the fun starts.

Insert a row. You now have 60 conditionals.
Insert a row apart from the row you just inserted. You now have 100 conditionals.

Then add on the volatile nature, and you already have a loss of speed due to there being so many. As you add data, your sheet slows down more.

You get a sheet, such as I use for my movies and TV episodes, where the movies sheet has 1836 items, and when a new movie is added fairly frequently. Still not too bad on it's own. But the TV sheet is another animal. 10,601 rows. As the conditionals get fragmented, it can really slow it down a lot.

MS needs a method to optimize conditionals. Sort of a defrag for spreadsheet conditionals. Easy enough to do as I've done it by hand by editing the sheet XML file that's in the xlsx file, getting rid of the excess conditionals and extending the one for each column to cover all rows.

This isn't too difficult. Make a copy of your xlsx file and change the extension to .zip. Extract all files. Each sheet will have it's own xml file. Edit the ones you need to, then remake the .zip file and rename the extension back to xlsx. It works. Had a sheet that took a couple of minutes to recalculate just for the conditionals, and defrag of the conditionals brought it back to less than a second.