Why does conditional formatting blow up when I move around cells?

[EmmittBrownBTTF1]

I have a spreadsheet, with what should be about three or four rules.

But after editing the values of the sheet, removing cell contents, inserting rows, the rules tend to explode, as holes appear in the cell ranges. These holes don’t appear to be related to my edits, and many of the cell ranges appear to be contiguous.
They can create a worrying performance hit when the sheet is recalculated.

Any chance of adding move up / move down buttons on the conditional formatting management dialogs?
Would also be good to be able to use named ranges for conditional formatting cell ranges.

[oweng]

You should have enough karma now. Please edit your question to include the screenshot. Thanks.

[EmmittBrownBTTF1]

Image now uploaded, showing one conditional rule set that has been fractured into many rule sets.
Note that some ranges shown above would make contiguous rectangles, if they were normalised.
The original cell range was A3:B1048576.
This problem occurs in 4.1.6 and 4.2.5.

[ted.w]

The associated bug report is fdo#57486

A comment from a LibreOffice dev states that it won’t be fixed:

This will not be fixed.

Copying conditional formats will
create new conditional formats to
prevent increasing the range in case
they are not overlapping. This is
important for conditional formats that
are not only based on the current cell
value but calculate based on the cond
format range.

In my experience, this happens when pasting or moving rows/columns.

One trick is to first insert an empty row/column then special-paste the source without formatting:

1. Select the source row/column, cut it, then delete it
2. Insert an empty row/column at the destination and paste special (Ctrl+Shift+V)
3. In the Selection region, uncheck Paste all and select everything else except Formats, OK

Also, if you frequently move stuff to the very end of the conditionally formatted area, another trick is to include an extra row/column in the area’s range definition. This way, you’ll be inserting inside the area, which will expand automatically.

[mariosv]

Perhaps report a new bug could be a not so bad idea, the comment from developer is two years old when he was beginning developing the new CF. Maybe now is more feasible for devs solve it. Would be great if was done, it’s really annoying at least for me.

[Lupp]

Seems nobody is pressing foreward as a volunteer answering this question.

I, on the other hand, can only give a very unsatisfactory answer: We have to take it.

Conditional formatting was enhanced some time ago with the release of one of the 3.x versions.

Before that only 3 conditions were supported and there was no ‘Manager’ but just a simple dialogue. Editing an existing CF required knowig the exact range first (as I remeber - if correct). This may have helped to establish a reasonable restraint on using CF in a too complicated manner. In addition: We couldn’t see the mess we possibly had caused. These times are gone. The current implementation of CF is in my humble opinion not satisfactory (and rather a mess). I even think that some fundamental questions about consistency and viability are not sufficiently explored. What about overlapping, fragmentation, behaviour when ranges are filled … How to get that in fashion? Furthermore the formula editor inside the manager is causing headache now and then … Even if everything got clear we might need an extra ‘Guide’ of some 100 pages to explain it.

On the other hand CF is sometimes really a valuable means.

My suggestion: Use it with caution.

Introduce it late during the design of a new sheet.

If sheet editing is going on, call the CF-manager in not too long intervals. First delete cf ranges you don’t need any longer, collect (not so easy), simplify and adjust in time - as long as you have still clear in mind what you wanted to achieve.

Two or three conditional formats should be enough for a sheet. Who should evaluate all the ‘visibility’ they create?

Wait for the perfect final solution.

[Editor]

A solution would be to allow named ranges in conditional formatting area definition like Excel does.