Fix

Getting Started With Python Excel AutomationDeep dive

Fix #### Showing Instead of Values in Excel Output

Numbers and dates written from Python show as ##### in Excel when the column is too narrow for the formatted value, and negative dates and times always show that way in Excel. Set widths from the formatted text, use xlsxwriter's autofit, shrink to fit where space is fixed, and check the result by rendering the sheet.

Errors & fixes Number & date formats

The report is correct, but half the numbers look like this:

Text

#####

Excel is not reporting an error. A number or a date must be shown in full or not at all, so when the formatted value is wider than its column, Excel fills the cell with hash marks instead of showing a misleading part of it. Text behaves differently: it spills into an empty neighbour or is cut off. The value is intact; the column is too narrow for the format. Python libraries do not size columns unless told to, so reports written with openpyxl, xlsxwriter or pandas show this constantly. This page shows the causes and three fixes, and how to check the result without opening Excel. It is part of Troubleshooting Common Python Excel Errors.

Prerequisites

Bash

pip install openpyxl xlsxwriter pandas

The checks use LibreOffice in headless mode to render sheets to PDF and pdftotext to read what a reader would see. LibreOffice shows narrow numbers as ### the same way.

Step 1 — Reproduce it

Write a few typical values into columns six characters wide, the width many templates end up with:

Python

import datetime as dt
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Revenue", "Invoice date", "Customer", "Units", "Margin"])
ws.append([1234567.89, dt.date(2026, 9, 28), "Harbor Foods Ltd", 48210, 0.1834])
ws["A2"].number_format = "#,##0.00"
ws["B2"].number_format = "dd mmmm yyyy"
ws["D2"].number_format = "#,##0"
ws["E2"].number_format = "0.0%"
for col in "ABCDE":
    ws.column_dimensions[col].width = 6
wb.save("narrow.xlsx")

Bash

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf narrow.xlsx > /dev/null 2>&1
pdftotext -layout narrow.pdf - | sed -n '1,2p'

The revenue and the date become hash marks; the customer name is cut off at the next column; the units, 48,210, and the percentage just fit. Nothing about the values changed; only their display width did.

Why the width is not enough

Excel measures column width in characters of the default font, roughly the width of a digit. What matters is the length of the formatted value, which can be much longer than the number:

1234567.89 with #,##0.00 is 1,234,567.89, twelve characters;
a date with dd mmmm yyyy is 28 September 2026, seventeen;
a percentage with 0.0% is 18.3%, five.

A General-format number behaves better than a formatted one: Excel rounds it or switches to scientific notation to fit, so 1234567.89 might show as 1E+06, which is arguably worse because it looks like a value.

Fix 1 — Set widths from the formatted values

With openpyxl, compute each column's width from the longest formatted value in it. The formatting only has to be approximately right; a small helper covering the formats you use is enough:

Python

from openpyxl import load_workbook

def display_text(cell):
    v, fmt = cell.value, cell.number_format
    if v is None:
        return ""
    if isinstance(v, (dt.date, dt.datetime)):
        py = fmt.replace("yyyy", "%Y").replace("mmmm", "%B").replace("mmm", "%b").replace("dd", "%d").replace("mm", "%m")
        return v.strftime(py)
    if isinstance(v, (int, float)):
        if "%" in fmt:
            decimals = len(fmt.split(".")[1].rstrip("%")) if "." in fmt else 0
            return f"{v:.{decimals}%}"
        decimals = len(fmt.split(".")[1]) if "." in fmt else 0
        return f"{v:,.{decimals}f}" if "," in fmt else f"{v:.{decimals}f}"
    return str(v)

def fit_columns(ws, padding=2, max_width=60):
    widths = {}
    for row in ws.iter_rows():
        for c in row:
            widths[c.column_letter] = max(widths.get(c.column_letter, 0), len(display_text(c)))
    for letter, w in widths.items():
        ws.column_dimensions[letter].width = min(w + padding, max_width)
    return widths

wb = load_workbook("narrow.xlsx")
print(fit_columns(wb.active))
wb.save("fitted.xlsx")

Bash

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf fitted.xlsx > /dev/null 2>&1
pdftotext -layout fitted.pdf - | sed -n '1,2p'

Every value now shows in full. The max_width cap matters for text columns: a description column fitted to its longest entry becomes unreadably wide, and wrapping text is the better answer there.

Fix 2 — Let xlsxwriter autofit

xlsxwriter has worksheet.autofit(), which does the same estimation from the values and formats it wrote. It is the simplest fix when the report is written with xlsxwriter or with pandas using the xlsxwriter engine:

Python

import pandas as pd

df = pd.DataFrame({"Revenue": [1234567.89, 98.5], "Invoice date": pd.to_datetime(["2026-09-28", "2026-10-01"]),
                   "Customer": ["Harbor Foods Ltd", "Kestrel"], "Units": [48210, 12]})
with pd.ExcelWriter("autofit.xlsx", engine="xlsxwriter", datetime_format="dd mmmm yyyy") as writer:
    df.to_excel(writer, index=False, sheet_name="Report")
    ws = writer.sheets["Report"]
    ws.set_column("A:A", None, writer.book.add_format({"num_format": "#,##0.00"}))
    ws.autofit()

Bash

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf autofit.xlsx > /dev/null 2>&1
pdftotext -layout autofit.pdf - | sed -n '1,3p'

Revenue fits, but the dates are hash marks again, in both rows. autofit() is an estimate, and it does not read number formats: every date is sized as if it were mm/dd/yyyy, and numbers are sized by their raw digits rather than their formatted text. A long date format, thousands separators on large numbers, or a currency symbol all come out narrower than they display. Size those columns yourself after calling it:

Python

with pd.ExcelWriter("autofit.xlsx", engine="xlsxwriter", datetime_format="dd mmmm yyyy") as writer:
    df.to_excel(writer, index=False, sheet_name="Report")
    ws = writer.sheets["Report"]
    ws.set_column("A:A", None, writer.book.add_format({"num_format": "#,##0.00"}))
    ws.autofit()
    ws.set_column("B:B", len("28 September 2026") + 2)     # autofit assumes mm/dd/yyyy

Bash

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf autofit.xlsx > /dev/null 2>&1
pdftotext -layout autofit.pdf - | sed -n '1,3p'

autofit() must also run after the data is written, and it only knows about what xlsxwriter wrote; formulas contribute nothing because their results are not known when the file is written. For formula columns, set a width yourself.

Fix 3 — When the width is fixed

Templates and print layouts often fix column widths. Then change what is displayed instead: a shorter number format (#,##0 instead of #,##0.00, thousands with #,##0,"k", dates as dd/mm/yy), or shrink_to_fit, which reduces the font size until the value fits:

Python

from openpyxl.styles import Alignment

wb = load_workbook("narrow.xlsx")
ws = wb.active
ws["A2"].number_format = '#,##0,"k"'
ws["B2"].number_format = "dd/mm/yy"
ws["D2"].alignment = Alignment(shrink_to_fit=True)
for col, width in {"A": 8, "B": 9, "D": 6}.items():
    ws.column_dimensions[col].width = width
wb.save("fixed_width.xlsx")

Bash

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf fixed_width.xlsx > /dev/null 2>&1
pdftotext -layout fixed_width.pdf - | sed -n '1,2p'

Shrinking works for the odd long value; used on a whole column it produces text too small to read, so prefer a shorter format. Format Numbers in Thousands and Millions in Excel with Python covers the scaling formats.

Negative dates and times

One case is not about width at all. In Excel's default date system, dates and times are positive numbers of days since 1900, and a negative date or time cannot be displayed; Excel shows ##### however wide the column. It typically appears when a script writes a time difference that came out negative, for example an end time earlier than a start time, formatted as hh:mm. LibreOffice is more lenient and wraps it around the clock, showing -0.25 days as 18:00, which is misleading in a different way. Fix the calculation or write such differences as plain numbers of hours:

Python

start, end = dt.datetime(2026, 9, 28, 18, 0), dt.datetime(2026, 9, 28, 12, 0)
hours = (end - start).total_seconds() / 3600
print(f"{hours:+.2f} hours")

Common pitfalls

Widening for the header only. Numbers with formats are usually wider than their headers; measure the formatted values.
Autofitting before writing data. autofit() measures what has been written so far.
Formula columns. Their results are unknown when the file is written; give them explicit widths.
Wide text columns. Cap widths and wrap text rather than fitting a 300-character comment.
Checking only in LibreOffice. Font metrics differ slightly; leave a character or two of padding.

Conclusion

##### means a number or date is wider than its column in its current format, or, in Excel, that a date or time is negative. Set column widths from the formatted values with openpyxl, call autofit() after writing with xlsxwriter, or, where widths are fixed, use a shorter number format or shrink to fit. Render the sheet with LibreOffice to confirm that no value is hidden before the report goes out.

Frequently asked questions

Why does Excel show ##### instead of my number? The formatted number or date is wider than the column. Widen the column, use a shorter number format, or turn on shrink to fit. The value in the cell is unchanged.

How do I autofit column widths with openpyxl? openpyxl has no autofit. Loop over the cells, compute the length of each formatted value, and set the column's width in ws.column_dimensions to the longest plus a little padding.

Does xlsxwriter have autofit? Yes, worksheet.autofit() estimates widths from the data written so far. Call it after writing the data. It sizes dates as mm/dd/yyyy and numbers by their raw digits, and cannot size formula columns, so widen those yourself.

Why does a time difference show as ##### even in a wide column? In Excel's default date system, negative dates and times cannot be displayed. Correct the calculation or write the difference as a number of hours.

Up one level: Troubleshooting Common Python Excel Errors — the other common errors.
Format Numbers in Thousands and Millions in Excel with Python — shorter formats for fixed widths.
Calculate Working Hours Between Two Timestamps — time differences done right.
Render an Excel Range as an Image — seeing a sheet the way readers do.
Fix Floating-Point Noise Like 0.1 + 0.2 ≠ 0.3 in Excel Output — another display problem with numbers.

More on errors & fixes