Bug 153766 - Conditional formatting needs autocalculate to be enabled to work
Summary: Conditional formatting needs autocalculate to be enabled to work
Status: UNCONFIRMED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.0 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2023-02-20 21:12 UTC by Gabor Kelemen (allotropia)
Modified: 2024-08-16 11:38 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Screenshot of the Products2 sheet in Excel and Calc (221.07 KB, image/png)
2023-02-20 21:12 UTC, Gabor Kelemen (allotropia)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen (allotropia) 2023-02-20 21:12:18 UTC
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.
Comment 1 m_a_riosv 2023-02-21 01:26:03 UTC
Some related bugs:
tdf#56896
tdf#57028

+1
Comment 2 ady 2023-02-21 05:46:26 UTC
> 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.
Comment 3 Buovjaga 2024-08-16 11:38:17 UTC
(In reply to ady from comment #2)
> 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.

Do we have example files that we could try?