Ask Your Question

Conditional Formatting does not paste correctly

asked 2018-06-14 17:19:52 +0200

PDB11 gravatar image

I have a spreadsheet in which one column has conditional formatting.

I insert some blank rows. I then copy from a row that has formulas but no data, in order to populate the blank rows with formulas before entering data in them.

The column with the conditional formatting contains a formula which evaluates to zero unless there is an error in the data that I entered. This column is formatted to show in red, but the conditional formatting displays this in pale grey if it is zero.

Before I enter data, this evaluates to zero, but in the newly pasted rows it shows red. If I copy a cell from that column (rather than a whole row), and paste it on there, it correctly goes grey. This is true whether I copy from a cell that is displaying correctly or one that is displaying incorrectly. I can even copy a cell onto itself, and it turns from red to grey.

If I save the spreadsheet, close LibreOffice, and open the spreadsheet again, all rows display correctly, but any further rows I insert display incorrectly again.

I copy some data from a bank statement I downloaded, and the column correctly shows an error until I enter data elsewhere in the row. I now find that sometimes the error column remains red when I have entered the correct data and cleared the error. After I sorted the spreadsheet, this now happens in the rows I inserted, in the new locations where they have gone - i.e. they have taken the problem with them. This behaviour is not consistent - usually the error column behaves correctly, going grey when I clear the error.

Where I have copied and pasted cells (or formats) to correct the conditional formatting, it creates new regions on the list of conditional formatting, which could bloat the spreadsheet in the long term

The spreadsheet was created in OpenOffice 4 under Windows 7, where it worked correctly; I have only just migrated to LibreOffice 5.4.7 and Windows 10.

edit retag flag offensive close merge delete


can you show us your file? all data from cell may be deleted

kompilainenn gravatar imagekompilainenn ( 2018-06-14 18:32:54 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-06-15 05:04:19 +0200

Mike Kaganski gravatar image

The issue about increasing format count on paste (creation of separate formats with new ranges) is bug 95295, which is fixed for version 6.1. The issue that @m.a.riosv mentioned is about joining existing fragmented formats when a file with them is opened.

The problem with wrong display of formatted cells after paste (until the file is reloaded) needs a sample file (as @kompilainenn suggested) with specific steps to reproduce, like "copy range X1:Y2 to clipboard; select V3:W4 and paste; expected is A; actual is B". Possibly this is a bug - then it will need to be filed to bug tracker; or it might be fixed in 6.0 or later.

edit flag offensive delete link more


Thanks for pointing me at bug tracker. I think this is bug 62208. It appears to be still open. I can reproduce the bug easily in a new spreadsheet, so I shall post instructions there as to how to do that.

PDB11 gravatar imagePDB11 ( 2018-06-15 16:00:37 +0200 )edit

Only do that if you can add something substantial to the already existing description there; otherwize it will only be noise in the bug, that only makes the bug less manageable. (However, I see that the bug has a request to confirm that with a current version; please do it if you can.)

Adding yourself to CC of the bug "promotes" it collaterally, since it's kind of a measure of "affected user count".

Mike Kaganski gravatar imageMike Kaganski ( 2018-06-15 16:07:56 +0200 )edit

Looking more closely at 62208, I now don't think it is the same bug. I intend to submit a bug report, but probably not before next week.

PDB11 gravatar imagePDB11 ( 2018-06-15 16:14:53 +0200 )edit

At least I cannot reproduce either of bugs mentioned in that bug's test document, using Version: (x64) Build ID: 0588a1cb9a40c4a6a029e1d442a2b9767d612751 CPU threads: 4; OS: Windows 10.0; UI render: default; Locale: ru-RU (ru_RU); Calc: CL

Mike Kaganski gravatar imageMike Kaganski ( 2018-06-15 16:15:27 +0200 )edit

answered 2018-06-14 18:33:36 +0200

m.a.riosv gravatar image

I know about the issue of duplicated conditions, there is a bug reported pretending that this doesn't happen, tdf#87274, because it have two effects, spreadsheet having a lot of CF, difficult to maintain, while all of them doing the same for different and contiguous ranges, what sometimes can carry to a high degradation of the performance.

The better way I have found it's using paste special to copy without format, extending the range of the CF manually editing it, or having already it long enough. Going to do a lot of copy/paste perhaps can be quick reducing the range in the CF, restoring it after finish the copy/paste.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-06-14 17:19:52 +0200

Seen: 24 times

Last updated: Jun 15