Calc very very slow with conditional formatting

[Enricho]

I have a sheet that I have created that is very slow to open and refresh. It uses a bit of conditional formatting. I have recreated it with the conditional formatting applied to ranges and blocks of cells instead of individual cells but am still having the speed issue. The extent of the conditional formatting which applies a specific style is only 36 rows and 27 columns. I have copied out the formulas and text and pasted it into a new file and noted without the conditional formatting the file size size drops to 23k and with the formatting the file size is 600k. The Libre Office Version is 25.2 and I have also disabled Java runtime and OpenGL with no change.

The PC is a 9th gen i7 running on Windows 11 and it only seems to be this sheet that has slow performance. A confouding issue is that each week my intention is to copy the template sheet into a new sheet in the same file, then after updating it with that weeks data I copy it back over the template. After four weeks there would be five sheets in the file, the template and one for each week. The speed slows down even more dramtically when there are multiple sheets. As a workaround I have also tried saving the file .xls format and it seems to work 10x faster in .xls format, still opening it in Libre Office, but the conditional formatting is a bit more funky and doesn’t look the way I intended.

One theory I have is that there is some cumulative formatting issue and as I copy each weeks sheet back over the template in preperation for the next week it ‘multiplies’ what ever the reference or formatting issue is that is slowing this file down. This is because I suspect that it is getting slower and slower each week, even if I delete all the weeks sheets from the file and the issue seems to be with the template. I have also been able to recreate the sheet and the file size is down to 50k, so it appears to be something to do with the copy and paste each week that slows it down and increases the file size.

I have uploaded the sheet and would appreciate any perspective. Yellow in the conditional formatting is for user entry while blue/grey has a formula and calculates it from the user entry. Thanks in advance for assisting in identifying the issue or error in my formatting logic.

Test Analysis.ods (595.5 KB)

[sokol92]

The problem with your file is not in conditional formatting, but in the fact that several tens of thousands of graphic objects are linked to the first row of the “Template” sheet.
The easiest way to fix it (deleting the first row can take a long time).
Select the range of entire rows 2:36 of the Template sheet, copy and paste into the range of cells of the new document, starting with cell A2. If you now save the second document, its size will be 18K.

[ms777]

… or save as .fods, edit e.g. with Notepad++, remove the ~100.000 lines. They look like
<draw:control draw:z-index="0" draw:style-name="gr1" draw:text-style-name="P1" svg:width="0.8894in" svg:height="0.4724in" svg:x="-2.0469in" svg:y ="0in"/>
Test Analysis-corrected.ods (18.6 KB)

[Enricho]

Thanks you very much. I have spent many hours tinkering aorund the edges without looking at the file level and your help is much appreciated. I have noticed a few other changes now, where it is able to select all instead of stopping the selection at L3. I suspect I may have ‘injected’ it somehow when I was playing around with date selection options on row 1. Thank you again.

[mariosv]

When there are problems with a file, it is never a bad idea to use the navigator tool to see if there is something strange.