Created attachment 185503 [details] Screenshot of the Products2 sheet in Excel and Calc This is sort of a mis-feature removal request. attachment 185496 [details] from bug 153763 is a conditional formatting learning material from https://support.microsoft.com/en-us/office/use-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f This file has several sheets where the conditional formatting does not look the same as in Excel, such as Products2 , Banded rows , Compare to totals , Products3, Customers2. 1, Open attachment 185496 [details] in Calc and Excel 2, Compare the conditional formatting of the above mentioned sheets. This happens because the conditions are cell references/formula and the Data - Calculate - Autocalculate option is turned OFF in the file (in Excel: Formulas - Calculation - Calculation Options - Manual). Enabling this option is necessary for such conditions to be evaluated, as documented in the help: https://help.libreoffice.org/7.5/en-US/text/scalc/01/05120000.html?&DbPAR=CALC&System=WIN "To apply conditional formatting, AutoCalculate must be enabled. Choose Data - Calculate - AutoCalculate (you see a check mark next to the command when AutoCalculate is enabled)." Enabling the Autocalculate option and scrolling a bit corrects the formatting, so the XLSX-interop part works fine. This is happening in current master and back to 3.5, so my guess is this could be an old performance workaround. Now the request is to lift this "CF needs autocalculate enabled" limitation for conditions defined as formulae/cell references. Excel can apply such conditional formatting with disabled autocalculate, so we should too.
Some related bugs: tdf#56896 tdf#57028 +1
> This is happening in current master and back to 3.5, so my guess is this > could be an old performance workaround. There is one thing to consider. IIUC, CF works "somewhat" like volatile functions, so it may demand more calculation resources in some sense. There was a time when users would make changes to a workbook, and only then would activate calculation (whether automatic or manual). Nowadays, most "normal" size worksheets (if there is such a thing) are used with AutoCalculate set on, whereas "big" workbooks are calculated manually in order not to waste time waiting for some meaningless calculation, until the results are really needed. Setting conditional format to disregard the Calculate setting could be detrimental for such "big" size workbook users that intentionally set AutoCalculate to off. IDK whether CF deserves its own "AutoCalculate" setting, but that seems a possible way to allow users control their resources according to their needs and priorities, instead of imposing one way or the other. The additional factor to consider would be the potential discrepancy between the way a cell "looks" (CF) and the (not-yet-calculated) results somewhere in the worksheet.