# Show some zero values when zero values not to be shown

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 close merge delete

Sort by » oldest newest most voted

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)

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 !

( 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 (@)."

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

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

more

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

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

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

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

Hello,

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

Hope that helps.