I too have experienced the “###” being displayed for zero values when migrating a document from Excel to Calc when using Excel’s “Accounting” format. The strange thing is that seemingly the same format code applied to different cells can result in different output (one with “-”, the other with “###”) and changing the width of the cell does not appear to help! Bizarre. Anyway, this can be fixed…
The “Format code” consists of up to 3 sections, separated by the semi-colon (
;). The 3rd section indicates what is shown when the value is zero. (The first two are the positive and negative variants respectively.)
So, to display a hyphen (
-) when the value is zero, you could use a format code like this:
The above format code includes 2 decimal places, 1 leading zero and thousands separators. Negative values are [RED] and surrounded by parentheses. And finally a single hyphen is shown when the value is zero.
You could pad the hyphen with a couple of spaces on the right to make it more Excel like.