Show Zeros as Dashes or Hide Them in Excel

A financial report full of 0.00 is hard to read: the figures that matter are lost among rows of zeros. Printed accounts solve this with a dash — "–" — in place of a zero, and some reports leave such cells empty altogether. Both are display choices, and Excel can make them without touching the underlying values, so totals and formulas keep working. The common shortcut in generated reports — writing an empty string or None instead of 0 — looks the same on screen but behaves differently: averages change, counts change, and formulas that do arithmetic on the cell can fail. This page shows zeros as dashes with a number format, hides them in chosen cells or a whole sheet, keeps the dashes aligned with the numbers around them, and checks with LibreOffice how each approach affects the calculations. It belongs to Applying Number and Date Formats in Excel.

Prerequisites

pip install openpyxl pandas

LibreOffice (soffice) is used to recalculate the workbook and export what each cell displays, so the formats can be checked without opening Excel.

Step 1 — A dash from the zero section

A number format has up to four sections separated by semicolons: positive; negative; zero; text. Put a dash in the third and zeros display as a dash while staying zero:

from openpyxl import Workbook

DASH = '#,##0.00;-#,##0.00;"–"'
DASH_ALIGNED = '#,##0.00_);(#,##0.00);"–"_)'
HIDDEN = '#,##0.00;-#,##0.00;'

wb = Workbook()
ws = wb.active
ws.title = "Formats"
ws.append(["Value", "General", "Dash", "Dash, aligned", "Hidden"])
for v in [1250.5, 0, -40, 0, 87.25]:
    ws.append([v, v, v, v, v])
for row in ws.iter_rows(min_row=2):
    row[2].number_format, row[3].number_format, row[4].number_format = DASH, DASH_ALIGNED, HIDDEN
print(DASH, "|", DASH_ALIGNED, "|", HIDDEN)

In DASH, the third section is a quoted en dash, so a zero shows "–". DASH_ALIGNED follows the accounting convention of negatives in parentheses: _) inserts a space the width of a closing parenthesis after positive numbers and the dash, so that digits and dashes line up with negatives such as (40.00) above and below them. HIDDEN leaves the third section empty — the semicolon is there, but nothing after it — so a zero displays as nothing at all. A dash written with "-" instead of the en dash works too; the en dash is simply the typographic convention in financial statements. The section syntax is explained further in Write Custom Number Format Codes in Excel with Python.

Step 2 — Hidden zero, empty string or true blank?

There are three ways to make a cell look empty, and they are not equivalent. Build a column of each, add formulas over them, and let LibreOffice calculate:

ws2 = wb.create_sheet("Behaviour")
ws2.append(["Zero, hidden by format", 'Formula returning ""', "Blank (None)"])
for v in [100, 0, 50]:
    ws2.append([v, v if v else '=""', v if v else None])
for c in ws2["A"][1:]:
    c.number_format = HIDDEN
labels = ["SUM", "AVERAGE", "COUNT", "COUNTA", "Cell + 1"]
formulas = ["=SUM({c}2:{c}4)", "=AVERAGE({c}2:{c}4)", "=COUNT({c}2:{c}4)", "=COUNTA({c}2:{c}4)", "={c}3+1"]
for r, (label, f) in enumerate(zip(labels, formulas), start=6):
    ws2[f"D{r}"] = label
    for col in "ABC":
        ws2[f"{col}{r}"] = f.format(c=col)
wb.save("zeros.xlsx")

import csv, subprocess
AS_SHOWN = "csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true,false,false,-1"
subprocess.run(["soffice", "--headless", "--convert-to", AS_SHOWN, "--outdir", "calc", "zeros.xlsx"], check=True, capture_output=True)
for name in ["Formats", "Behaviour"]:
    print(f"--- {name}")
    with open(f"calc/zeros-{name}.csv", encoding="utf-8") as f:
        for row in csv.reader(f):
            print(" | ".join(f"{c:>12}" for c in row[:5]))

The -1 at the end of the filter options exports every sheet to its own CSV file, with each cell as displayed. On the Formats sheet the dashes and hidden zeros appear exactly as designed. On the Behaviour sheet the three columns look identical — 100, nothing, 50 — but calculate differently. The hidden zero is still a number: SUM is 150, AVERAGE is 50 because it divides by three, COUNT is 3, and adding 1 to it gives 1. The formula returning "" — the familiar =IF(A2=0,"",A2) pattern — produces text: SUM ignores it, but AVERAGE becomes 75 because it only averages two numbers, COUNT drops to 2, COUNTA still counts it, and cell + 1 fails with #VALUE!. The true blank behaves like the empty text for SUM, AVERAGE and COUNT, but is not counted by COUNTA, and cell + 1 treats it as zero. Note that writing the Python value "" with openpyxl is not the same as the formula: openpyxl stores it as a string cell with no content, which LibreOffice reads as a blank — one more reason not to rely on empty strings to mean anything.

Step 3 — Choose what the cell should be, then how it looks

The table makes the rule clear. If the value is genuinely zero — no sales of a product this month, a cost line that was nil — write 0 and use a number format to show a dash or nothing; averages and counts then include it, as they should. If the value is missing — no data, not applicable — write a true blank, so averages and counts leave it out. Never use empty text — a "" value or a formula returning "" — to stand for zero in a numeric column. From pandas this means keeping zeros as zeros and missing values as NaN, which to_excel writes as true blanks:

import pandas as pd
from openpyxl import load_workbook

df = pd.DataFrame({"Product": ["Bolts", "Nuts", "Washers", "Rivets"],
                   "Jul": [1200.0, 0.0, 310.0, None], "Aug": [980.0, 0.0, 0.0, 150.0], "Sep": [1105.5, 45.0, 0.0, 0.0]})
df.to_excel("sales_q3.xlsx", index=False)
wb = load_workbook("sales_q3.xlsx")
ws = wb.active
for row in ws.iter_rows(min_row=2, min_col=2):
    for cell in row:
        cell.number_format = DASH_ALIGNED
wb.save("sales_q3.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", AS_SHOWN, "--outdir", "calc", "sales_q3.xlsx"], check=True, capture_output=True)
from pathlib import Path
print(Path("calc/sales_q3-Sheet1.csv").read_text(encoding="utf-8"))

The zeros now read as aligned dashes — Nuts in July and August, Washers in August and September — while Rivets' July, missing rather than zero, stays empty. A reader can tell the difference between "nothing sold" and "no data", which a sheet of blanks or a sheet of zeros would hide. How pandas writes NaN and infinity in general is covered in Control How NaN and Infinity Appear in Excel Output.

Step 4 — Hide every zero on a sheet

To hide zeros across an entire sheet — including formula results — Excel has a sheet-level option, Show a zero in cells that have zero value. openpyxl exposes it on the sheet view:

wb = load_workbook("sales_q3.xlsx")
wb.active.sheet_view.showZeros = False
wb.save("sales_q3_nozeros.xlsx")

import zipfile
with zipfile.ZipFile("sales_q3_nozeros.xlsx") as z:
    xml = z.read("xl/worksheets/sheet1.xml").decode()
print("showZeros written:", 'showZeros="0"' in xml)

The setting is saved in the sheet's XML as showZeros="0" and Excel honours it when the file is opened; LibreOffice does not read it from .xlsx files, which is why the CSV export cannot be used to check it. Because it is a view setting, it applies to every cell on the sheet and a reader can turn it back on in Excel's options — useful for a dashboard sheet, but a number format is the better tool when only some cells should change. Neither approach alters values, so charts still plot zeros as zero; if a chart should leave a gap instead, the cell needs to be blank or #N/A, as in Build a Rolling 12-Month Chart on an Excel Dashboard.

Common pitfalls

• Writing "" instead of 0. It is text: averages, counts and arithmetic change.
• Forgetting the third section. Two sections apply the negative format to zeros too.
• Misaligned dashes. Use _) so dashes line up with parenthesised negatives.
• Hiding missing data as zeros. Missing values belong as blanks, not hidden zeros.
• Expecting charts to skip hidden zeros. They plot the value, not the display.

Conclusion

To show zeros as dashes, give the number format a third section such as "–", with _) padding if negatives use parentheses; to hide them, leave the third section empty, or turn off showZeros for a whole sheet. Keep real zeros as 0 and missing values as true blanks — never empty strings — so that averages, counts and arithmetic stay correct, and check the displayed result with a LibreOffice CSV export.

Frequently asked questions

How do I display zeros as dashes in Excel with Python? Set a number format with a zero section, for example #,##0.00;-#,##0.00;"–", using openpyxl's cell.number_format.

How do I hide zero values without deleting them? Use a number format whose third section is empty, such as #,##0.00;-#,##0.00;, or set ws.sheet_view.showZeros = False to hide all zeros on a sheet.

Is a hidden zero the same as an empty cell? No. A hidden zero is still counted by AVERAGE and COUNT, while blank cells and empty strings are skipped; empty strings also cause VALUE errors in arithmetic.

Why do my dashes not line up with negative numbers in parentheses? Add ) after the positive and zero sections so they reserve the width of a closing parenthesis, as in #,##0.00);(#,##0.00);"–"_).

Related

• Up one level: Applying Number and Date Formats in Excel — number formats from Python.
• Show Negative Numbers in Red with Parentheses — the negative section.
• Read Cell Values as Displayed in Excel — reading formatted text back.
• How Missing Values Change Sums, Averages and Counts — the same question in pandas.