We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2014-07-20 18:07:42 +0200

EmmittBrownBTTF1 gravatar image

updated 2014-07-27 04:15:07 +0200

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.

Conditional formatting duplicated as editing sheet creates a patchwork

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 18:13:37.278162


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

oweng gravatar imageoweng ( 2014-07-21 12:22:36 +0200 )edit

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.

EmmittBrownBTTF1 gravatar imageEmmittBrownBTTF1 ( 2014-07-27 04:20:32 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2014-10-04 13:34:38 +0200

ted.w gravatar image

updated 2014-10-05 02:22:27 +0200

oweng gravatar image

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.

edit flag offensive delete link more


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.

m.a.riosv gravatar imagem.a.riosv ( 2014-10-04 14:59:24 +0200 )edit

answered 2015-08-30 11:43:44 +0200

Editor gravatar image

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

edit flag offensive delete link more

answered 2014-07-27 15:56:01 +0200

Lupp gravatar image

updated 2014-07-27 15:58:47 +0200

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-07-20 18:07:42 +0200

Seen: 4,827 times

Last updated: Oct 05 '14