Ask Your Question

Conditional Formatting Disappears On Save and Close [closed]

asked 2012-08-01 00:47:12 +0200

Tech.CG gravatar image

Hi there, My business uses LibreOffice quite extensively, especially Calc. Recently we have started using conditional formatting to make using the sheets more friendly for our less computer-savvy users. However, when we apply the conditional format and SAVE the file, we can reopen the file right afterwards and the conditional formatting is gone. Has anyone else had this problem?

Thank you in advance!

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 2015-10-19 13:08:44.430967



Do you use the native format to save?, What is your LibreOffice version and OS?

m.a.riosv gravatar imagem.a.riosv ( 2012-08-01 02:28:42 +0200 )edit

Cell Value Conditions "duplicated values" and "non duplicated values​​" not stored in the file. Did you mean these formats?

JohnSUN gravatar imageJohnSUN ( 2012-08-01 20:31:56 +0200 )edit

Yes, I the condition "duplicated values" is the condition we have started to use. Is there a way to achieve the highlighting of duplicate fields every time the file is opened? On any computer?

Tech.CG gravatar imageTech.CG ( 2012-08-01 21:51:42 +0200 )edit

Try to use the old method of conditional formatting for this case: "formula is" COUNTIF (<absolute address of range checked>;<relative address of the current cell>) > 1

JohnSUN gravatar imageJohnSUN ( 2012-08-02 07:22:35 +0200 )edit

I have tried to use the method provided and it works somewhat. The only problem is that not all of the duplicates are highlighted and sometimes cells that do not have a duplicate are highlighted. I will try it again, maybe I was using the formula incorrectly before.

Tech.CG gravatar imageTech.CG ( 2012-08-02 19:23:19 +0200 )edit

Usually I'm wrong when setting the second parameter of this formula. It is necessary to specify the address of the active cell (encircled black border). For example, it is necessary to mark duplicates in the range A2:C140. Select this range and see the address of the active cell... (to be continued)

JohnSUN gravatar imageJohnSUN ( 2012-08-03 09:37:15 +0200 )edit

(cont.) ... active cell (it can be A2, C2, A140, C140, or any other, depending on the method of selection). Let this be C140. So the formula should be COUNTIF($A$2:$C$140;C140)>1 (Pay attention to $-signs)After that select any other cell in the tested range and check that the formula points to it

JohnSUN gravatar imageJohnSUN ( 2012-08-03 09:39:29 +0200 )edit

Thank you! It worked great. I was missing one $! Just 1! Thanks for your help!

Tech.CG gravatar imageTech.CG ( 2012-08-03 16:27:44 +0200 )edit

Is there a way to make one of the duplicates, or all, show how many times that data has been duplicated? Like for phone numbers I might have 555-555-5555 duplicated 5 times. They all get highlighted.Could I make a formatting rule that will change the cell(s) to read 555-555-5555 +5

Tech.CG gravatar imageTech.CG ( 2012-08-04 00:41:37 +0200 )edit

Yes, it is possible. See the second example in the answer

JohnSUN gravatar imageJohnSUN ( 2012-08-06 20:37:00 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2012-08-04 11:40:31 +0200

JohnSUN gravatar image

updated 2012-08-06 20:37:41 +0200

Conditions "duplicated values" and "non duplicated values​​" created for validate existing data only. This works only in the current session and not saved in a file. If you need to store these conditional formats in your workbook, use classical equivalents:

    "duplicated values" => 
COUNTIF (<absolute address of range checked>;<relative address of the current cell>) > 1
    "non duplicated values​​" => 
COUNTIF (<absolute address of range checked>;<relative address of the current cell>) = 1

You can also use the function STYLE(), either alone or in combination with conditional formatting.

Small example - Show how many times duplicated.ods

Example of alternative solution - Combination of conditional format and STYLE.ods

edit flag offensive delete link more

answered 2012-11-23 01:12:06 +0200

France gravatar image


I have same problem with conditional formating. I use it to automatic color the weekends in a month. It's all working good until I copy the sheet and save the file. After I open the file again the sheet that is a copy, it doesn't have conditional formating anymore. Is there a bug? The copy sheet have all the conditional formating until I reopen the file.

Regards, F

edit flag offensive delete link more


A copy of the sheet contains the styles that have been created to format the weekends?

JohnSUN gravatar imageJohnSUN ( 2012-11-23 08:09:44 +0200 )edit

Yes it have. I did copy the whole sheet. It have also the formulas etc... but there is no formating effect. After I make the copy of the sheet I still have the conditional formating on both sheets. But after save/reopen the file, I have the cond. formating only on original sheet.

France gravatar imageFrance ( 2012-12-04 01:23:15 +0200 )edit

Any news about this bug? Has been long time since last post but still have same problems with ver 4+ :(

France gravatar imageFrance ( 2014-06-19 13:06:13 +0200 )edit

answered 2012-12-17 04:40:04 +0200

France gravatar image

Still no ideas about that bug? Here I did add a Calc where are two sheets. One of original default formating and the other copy sheet. Conditional formating.ods

edit flag offensive delete link more


Do you say about bug #56742? The patch will be available in LibreOffice 4.0.

JohnSUN gravatar imageJohnSUN ( 2012-12-17 09:55:05 +0200 )edit

Question Tools


Asked: 2012-08-01 00:47:12 +0200

Seen: 4,727 times

Last updated: Dec 17 '12