Ask Your Question

Show some zero values when zero values not to be shown

asked 2020-05-14 16:21:30 +0100

Pete of Ebor gravatar image

updated 2020-07-22 13:16:54 +0100

Alex Kemp gravatar image

Sorry if the question sounds a bit non-sensical.. I have a spreadsheet which I have set up NOT to show zero values as there are lots of them. I would rather have a blank column than a column of zeros.
Tools > Options > LibreOffice calc > view > display zero values - is unchecked so as to not show zero values. However, there are a few specific cells where I would like to show zero values as 0.00 Can this be done ? If so, How ? I have looked at 'format cells' but I cannot seem to do it that way. I could just use an IF statement - eg IF {value} = 0 then "0.00", but that would make the cell a text value and could cause more problems elsewhere on the sheet.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2020-05-14 16:46:17 +0100

JohnSUN gravatar image

updated 2020-05-14 16:49:10 +0100

To solve such a problem, I would return Tools - Options - LibreOffice Calc - View - Display - Zero values ​​to the original checked value. I would create two custom styles, for example, “ZerosVisible” and “ZerosNotVisible”. For the second one, I would set the value format using Format code 0.00;-0.00;;@ or something like.

Now we can apply the second style to all cells, and for individual cells use the first format in which zeros are visible (Instead of ZerosVisible, you can use the usual Default, if you do not want to emphasize zeros in a special way, for example, in red)


edit flag offensive delete link more


I notice you have added the '@' character on the end of your format.. is that to do with text ? The first time I tried this, (without the '@' character) all my text was turned into numbers.. was able to 'undo' it - luckily !

Pete of Ebor gravatar imagePete of Ebor ( 2020-05-15 12:30:27 +0100 )edit

Yes, you are absolutely right, this is for text. Number Format Codes: "Number format codes can consist of up to four sections separated by a semicolon (;)... Fourth section applies if the content is not a value, but some text. Content is represented by an at sign (@)."

JohnSUN gravatar imageJohnSUN ( 2020-05-15 14:03:19 +0100 )edit

answered 2020-05-14 16:39:55 +0100

Vez gravatar image

updated 2020-05-14 17:26:19 +0100

Nice question :)

I just tried the following which worked. Tools > Options > LibreOffice calc > view > display zero values - is checked

Where you want to suppress formatting of 0s use custom format with format code similar to 0.00;-0.00;

The third format is for 0 and is empty!!

Negative corrected thanks @JohnSUN

edit flag offensive delete link more


@Vez It seems you have missed the minus sign for a negative value, haven't you?

JohnSUN gravatar imageJohnSUN ( 2020-05-14 16:52:17 +0100 )edit

Yes, you're right, thanks. 0.00;-0.00;

Vez gravatar imageVez ( 2020-05-14 17:05:10 +0100 )edit

answered 2020-05-14 16:57:19 +0100

Opaque gravatar image

updated 2020-05-14 17:03:47 +0100


you can't override a global setting by formatting - but you can do the following:

Enable setting Tools -> Options -> LibreOffice Calc -> View -> Option: [x] Zero values and create different cell styles with format codes (or apply formats directly)

  • showing a zero value and
  • not showing zero values

Example: The following format code: 0.00;-0.00;"" has 3 fields separated by semicolon (;) and means:

  1. Show 2 decimals, if value is a positive number
  2. Show 2 decimals, if value is a negative number
  3. Show nothing, if value is zero

Hence, if you use 0.00;-0.00;0 means

  1. Show 2 decimals, if value is a positive number (as above)
  2. Show 2 decimals, if value is a negative number (as above)
  3. Show 0, if value is zero

See also: LibreOffice Help - Number Format Codes

Hope that helps.

If the answer helped to solve your problem, please click the check mark (✔) next to the answer.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-05-14 16:21:30 +0100

Seen: 271 times

Last updated: May 14 '20