How to display zero as a dash
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Show "-" instead of 0 in reports, accounting-style, without destroying the numbers.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | #,##0;-#,##0;"-" | This is a custom NUMBER FORMAT (Ctrl+1 → Custom), not a formula — the cell still contains the real 0. |
| Google Sheets | #,##0;-#,##0;"-" | Format → Number → Custom number format. Identical syntax. |
| LibreOffice Calc | #,##0;-#,##0;"-" | Format → Cells → Number → Format code. Identical. |
How it works
Number formats have up to three value sections separated by semicolons — positive;negative;zero — so putting "-" in the third slot swaps every zero's DISPLAY for a dash while the cell keeps its true value for sums and charts (the verification proves the format string via TEXT). This beats formula tricks like IF(A2=0,"-",A2), which turn numbers into text and quietly break downstream math. The built-in Accounting format uses exactly this mechanism.
Verified, not just documented
We ran =TEXT(0,"#,##0;-#,##0;""-""") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned - — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned - for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.