Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Auto-Fit Column Widths When Writing with pandas

Stop Excel reports opening with ##### and truncated headers — measure content width in Python and set column widths with xlsxwriter or openpyxl, including a reusable helper.

A pandas-written report opens with every column at Excel's default width. Headers are clipped, a yyyy-mm-dd date column shows #####, and the reader's first action is to select all and double-click a column border. Excel has a real auto-fit, but it runs in Excel — nothing pandas writes can trigger it, so the width has to be computed in Python and written into the file. This guide gives you a helper that does it properly, including the number-format cases that naive measurement gets wrong. It extends Writing DataFrames to Excel with pandas.

Default column widths against fitted ones Two versions of the same four-column report. At Excel's default width the region name is clipped, the date column shows hash marks because the formatted date does not fit, and the header text is cut off mid-word. With widths computed from the content and the number format, every value and header displays in full, and the free-text column is capped so it cannot dominate the sheet. default widths fitted widths regi… invoi… reve… note North… ####### ####### prov… the reader's first action is to widen everything region invoice_date revenue note North 2026-08-15 5,150.00 prov… free-text column capped so it cannot dominate measure the FORMATTED width, not the raw value 46249 is five characters; "2026-08-15" is ten

Prerequisites

Bash
pip install pandas xlsxwriter openpyxl

A frame with the awkward cases — a long header, a date, a formatted number and a free-text column:

Python
import pandas as pd

df = pd.DataFrame({
    "region": ["North", "South-East Metropolitan", "West"],
    "invoice_date": pd.to_datetime(["2026-08-15", "2026-08-16", "2026-08-17"]),
    "revenue": [5150.00, 4268.50, 3511.25],
    "note": ["provisional",
             "restated after the Q2 close; see the reconciliation pack",
             ""],
})

Step 1 — Measure the content

Width in Excel is measured in characters of the default font, so the length of the longest string in a column is a good estimate. Include the header, which is often the longest thing in the column:

Python
def column_widths(df, padding=2, min_width=8, max_width=50):
    """Estimate a display width in characters for each column."""
    widths = {}
    for name in df.columns:
        longest_value = (
            df[name].astype(str).map(len).max() if len(df) else 0
        )
        widths[name] = max(
            min_width,
            min(max_width, max(int(longest_value), len(str(name))) + padding),
        )
    return widths

print(column_widths(df))
# {'region': 25, 'invoice_date': 21, 'revenue': 9, 'note': 50}

Three guards earn their place. min_width stops a column of single digits collapsing to something unusable. max_width stops the free-text column growing to fifty-six characters and pushing everything else off screen. And including len(str(name)) covers the common case where the header is longer than any value — invoice_date is twelve characters against a NaN-free column of ten.

df.astype(str) on a datetime column produces 2026-08-15 00:00:00, which is 19 characters — too wide for a column you intend to format as yyyy-mm-dd. That is the next step.

Step 2 — Size from the number format, not the value

For any column you give a number format, the format string tells you the display width directly. That is more reliable than measuring the underlying value, which may be a serial number or a full timestamp:

Python
FORMAT_WIDTHS = {
    "yyyy-mm-dd": 12,
    "yyyy-mm-dd hh:mm": 19,
    "#,##0.00": 12,
    '"$"#,##0.00': 14,
    "0.0%": 9,
}

def fitted_widths(df, formats=None, **kwargs):
    """Widths from content, overridden by the display width of a number format."""
    widths = column_widths(df, **kwargs)
    for name, fmt in (formats or {}).items():
        if name in widths:
            widths[name] = max(FORMAT_WIDTHS.get(fmt, 12), len(str(name)) + 2)
    return widths

FORMATS = {"invoice_date": "yyyy-mm-dd", "revenue": "#,##0.00"}
print(fitted_widths(df, FORMATS))
# {'region': 25, 'invoice_date': 14, 'revenue': 12, 'note': 50}

The ##### display appears exactly when a formatted number is wider than its column, so sizing from the format eliminates that failure entirely — the wider treatment of which is in fixing Excel serial numbers showing instead of dates.

Step 3 — Write the widths with xlsxwriter

set_column takes a column range, a width, and optionally a format — so one call does both jobs:

Python
import pandas as pd
from xlsxwriter.utility import xl_col_to_name

def write_fitted(df, path, sheet_name="Report", formats=None):
    """Write a DataFrame with fitted column widths and number formats."""
    formats = formats or {}
    widths = fitted_widths(df, formats)

    with pd.ExcelWriter(path, engine="xlsxwriter") as writer:
        df.to_excel(writer, sheet_name=sheet_name, index=False)
        book, sheet = writer.book, writer.sheets[sheet_name]

        header = book.add_format({
            "bold": True, "bg_color": "#EEF2FF", "border": 1,
            "align": "center", "valign": "vcenter",
        })
        cache = {}

        for position, name in enumerate(df.columns):
            sheet.write(0, position, str(name), header)

            fmt_string = formats.get(name)
            cell_format = None
            if fmt_string:
                # Reuse format objects — one per distinct format string.
                cell_format = cache.setdefault(
                    fmt_string, book.add_format({"num_format": fmt_string})
                )

            letter = xl_col_to_name(position)
            sheet.set_column(f"{letter}:{letter}", widths[name], cell_format)

        sheet.freeze_panes(1, 0)
        sheet.autofilter(0, 0, len(df), len(df.columns) - 1)

    return path

write_fitted(df, "report.xlsx", formats=FORMATS)

Caching the format objects matters. add_format creates a new entry every call, and creating one per column in a wide report inflates the workbook's format table for no benefit.

Recent xlsxwriter versions also offer sheet.autofit(), which estimates widths from what has been written. It is a reasonable one-liner, but it works only from the data already on the sheet and does not know about number formats you apply afterwards — so it re-creates the ##### problem on date and currency columns. The explicit helper stays predictable.

Step 4 — Write the widths with openpyxl

The width calculation, one step at a time Four stages producing a final width. The header length and the longest value length are compared and the larger taken. A small padding is added so text does not touch the cell border. The result is then clamped between a minimum, so a column of single digits stays usable, and a maximum, so one long free-text value cannot make the column dominate the sheet. For a formatted column the format string's display width replaces the measured value entirely. 1 · measure max(header, value) in characters 2 · pad + 2 clear of the border 3 · clamp min 8 · max 50 usable, not dominant 4 · override formatted column? use the format's width step 4 is what prevents ##### — the stored value 46249 is five characters, "2026-08-15" is ten

When you are modifying an existing workbook rather than creating one, the equivalent lives on column_dimensions:

Python
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

def fit_existing(path, dest, sheet_name=None, sample=500,
                 padding=2, min_width=8, max_width=50):
    """Fit the columns of a workbook that already exists."""
    wb = load_workbook(path)
    ws = wb[sheet_name] if sheet_name else wb.active

    for column in ws.iter_cols(min_row=1, max_row=min(sample, ws.max_row)):
        longest = 0
        for cell in column:
            if cell.value is None:
                continue
            # Approximate the rendered width for formatted numbers.
            text = (cell.number_format
                    if cell.number_format not in ("General",) and
                    isinstance(cell.value, (int, float))
                    else str(cell.value))
            longest = max(longest, len(text))

        letter = get_column_letter(column[0].column)
        ws.column_dimensions[letter].width = max(
            min_width, min(max_width, longest + padding)
        )

    wb.save(dest)
    return dest

Sampling the first few hundred rows rather than all of them is deliberate — on a large sheet, measuring every cell costs more than the resulting widths are worth, and the first few hundred rows are almost always representative. The related row and column sizing options are covered in setting column width and row height in openpyxl.

Common pitfalls and fixes

SymptomCauseFix
Dates show as #####Column narrower than the formatted dateSize from the format string, not the value.
One column fills the screenFree text, no capSet a max_width of around 50.
Header clipped although values fitHeader longer than any valueInclude len(str(name)) in the measurement.
Widths ignoredset_column called before to_excelWrite the frame first, then format.
AttributeError on column_dimensionsUsed the xlsxwriter API on an openpyxl sheetThe two engines have different APIs.
Workbook has thousands of formatsadd_format called per columnCache format objects by format string.
Widths wrong after appending rowsFitted before the new rows were writtenRe-fit after the final write.
Very slow on a large sheetMeasuring every cellSample the first few hundred rows.

Performance and scale notes

Measuring scales with rows; applying does not Two cost bars for a million-row frame. Measuring every cell builds a string per cell across every column, which dominates the total. Sampling the first thousand rows reduces that to a sliver while giving practically the same widths. Applying the width itself, through set_column or column_dimensions, is a single operation per column and is unaffected by row count either way. fitting a 1,000,000-row frame with 30 columns measure everything 30,000,000 temporary strings sample 1,000 rows practically the same widths applying the width — constant, one call per column

Measuring is the cost, and it scales with rows. df[name].astype(str).map(len).max() materialises a string for every cell — on a million-row frame with thirty columns that is thirty million temporary strings.

Sample instead. Column widths are a presentation detail, and the widest value in the first thousand rows is almost always wide enough:

Python
def column_widths_sampled(df, sample=1000, **kwargs):
    """Measure a sample rather than the whole frame."""
    head = df.head(sample) if len(df) > sample else df
    return column_widths(head, **kwargs)

Two further habits. Skip measurement entirely for formatted columns — the format string gives the width with no data access at all, which is free regardless of row count. And apply widths per column, not per cell: set_column and column_dimensions are both O(1) in the number of rows, so the write side never scales badly even when the measurement does.

For genuinely large reports written through openpyxl's streaming mode, note that write_only workbooks do support column_dimensions, but you must set them before appending rows — the dimensions are written into the sheet header, which is emitted first. That makes format-derived widths the only practical option there, since you cannot measure data you have not written yet:

Python
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.dimensions import ColumnDimension

wb = Workbook(write_only=True)
ws = wb.create_sheet("Report")

for position, width in enumerate([25, 14, 12, 50], start=1):
    letter = get_column_letter(position)
    ws.column_dimensions[letter] = ColumnDimension(ws, index=letter, width=width)

ws.append(list(df.columns))
for row in df.itertuples(index=False):
    ws.append(list(row))
wb.save("large_report.xlsx")

That combination — streaming rows with widths set up front — is what keeps a large write-only report both memory-flat and readable.

Conclusion

pandas has no auto-fit, so measure and set the widths yourself. Take the longest of the header and the values, add a little padding, and clamp between a sensible minimum and a maximum of around fifty so one free-text column cannot dominate. For any column carrying a number format, size it from the format string instead of the underlying value — that is what eliminates ##### on dates and currency. Then write the width with set_column in xlsxwriter or column_dimensions in openpyxl, sample rather than measure everything on large frames, and always fit after the final write.

Frequently asked questions

Does pandas have an autofit option for to_excel? No. pandas writes values and leaves every column at Excel's default width, so wide text is clipped and formatted numbers show as hash marks. You measure the content and set the widths yourself.

What unit is the width argument in? Approximately the number of characters of the default font. It is not pixels and not points, which is why measuring the longest string's length and adding a small padding works well as an estimate.

Why is my date column still showing #####? Because you measured the underlying value rather than its formatted display. A date stored as 46249 is five characters, but rendered as yyyy-mm-dd it needs ten. Size date and currency columns from the format string.

Does xlsxwriter have a real autofit? Recent versions expose a worksheet autofit method that estimates widths from the written data. It is convenient, but it works from what has been written so far and does not know your intended number formats, so an explicit helper is still more predictable.

Should I cap the maximum width? Yes. One long free-text comment can otherwise make a column hundreds of characters wide and push everything else off screen. Cap at around fifty and let the cell wrap or clip.