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.
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
Yes, you’re right, thanks.
0.00;-0.00;
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)
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 !
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 (@).”
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:
- Show 2 decimals, if value is a positive number
- Show 2 decimals, if value is a negative number
- Show nothing, if value is zero
Hence, if you use 0.00;-0.00;0 means
- Show 2 decimals, if value is a positive number (as above)
- Show 2 decimals, if value is a negative number (as above)
- 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.