"Hide zero" conditional format not saving

Discuss the spreadsheet application

"Hide zero" conditional format not saving

Postby Marscaleb » Fri Jul 31, 2020 2:18 am

I'm making a spreadsheet for my parents to use with their business. I use Open Office myself but they have Microsoft Office, so I need to be saving this in the Microsoft Office format.

I need all the cells with a value of zero to appear as blank. I had set up a conditional format to state that if the value was zero it would use a "Hide Zero Values" style I made that just displays a blank cell. Number format code: 0;-0;;@
But when I saved the file and opened it up again, all the cells with calculations on it are written as zeroes instead of being blank, so it didn't save the property. It looks like the "hide zero values" style is still there, but the conditional format is gone.

How do I set the zero values to be hidden, and still apply that value when it is saved as an xls file?
Posts: 5
Joined: Wed Sep 07, 2011 9:09 am

Re: "Hide zero" conditional format not saving

Postby RusselB » Fri Jul 31, 2020 3:00 am

I see the following options:
1) Use your parents computer and find the appropriate setting in Microsoft Office (presuming that it exists).
2) Save the file in .ods format and use that file with your parents version of Microsoft Office. I believe any version of Microsoft Office after 2007 will work with the .ods file format.
3) Save the file in .ods format and convince your parents to switch to OpenOffice (or, imo, better yet, LibreOffice)
The problem you are running into is due to the settings being saved in different locations for the two file formats.

Afterthought, instead of using a conditional format, why not use the setting, which, in OpenOffice is located using Tools -> Options -> OpenOffice Calc -> View -> Display -> Zero Values
OpenOffice 4.1.7, LibreOffice on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Posts: 6308
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: "Hide zero" conditional format not saving

Postby Zizi64 » Fri Jul 31, 2020 7:17 am

Never was (and never will be) 100% compatibility between the different file formats.
You always will lose some formatting properties (or even some data!) when you save your document into a foreign (and obsolete, never standardized) file format.
Always use the native, ISO standard ODF file formats for your important documents.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Posts: 9724
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Return to Calc

Who is online

Users browsing this forum: No registered users and 18 guests