Fixing Python openpyxl That Fails to Autofit Column Widths on Save

You write your data to an Excel file with openpyxl, open it, and every column is a uniform narrow strip that truncates half your content. You search for an autofit method, find nothing, and wonder if you missed something. You didn't — openpyxl genuinely has no built-in autofit, and this article shows you the reliable workaround.

What You'll Learn

• Why openpyxl cannot autofit columns and what Excel does instead
• How to estimate column widths from cell content in Python
• How to handle edge cases: merged cells, bold text, and number formats
• A reusable utility function you can drop into any project
• The most common mistakes people make when setting column widths manually

The Problem: openpyxl Has No True Autofit

If you have tried column_dimensions['A'].bestFit = True and found it does nothing visible, you are not alone. The bestFit attribute exists in the OOXML spec and openpyxl exposes it, but in practice most spreadsheet applications — including Microsoft Excel — ignore it entirely when opening a file. The attribute was intended as a hint to the application at load time, not a directive that sticks.

True autofit in Excel is a runtime calculation. Excel measures the rendered pixel width of each cell's content using the actual font metrics of the installed font, then sets the column width accordingly. openpyxl runs at write time, has no access to font rendering, and so cannot replicate this process exactly. The best you can do is a close approximation, and that approximation is good enough for virtually all real-world reports.

Why openpyxl Can't Autofit Natively

openpyxl is a pure-Python library that reads and writes .xlsx files as ZIP archives of XML. It has no rendering engine and no font measurement capabilities. When Excel opens a file and you click "AutoFit Column Width", Excel uses its own GDI/DirectWrite layer to measure text. openpyxl simply has no equivalent.

Some libraries like xlsxwriter ship with a lookup table of character widths for common fonts, giving slightly more accurate estimates. openpyxl takes no such approach, so you have to build the estimation yourself. The good news is that for monospaced or near-monospaced content — IDs, dates, numbers — a simple character-count approach is accurate enough. For proportional text like names and descriptions, a small fudge factor gets you close.

How Column Widths Work in openpyxl

Column widths in OOXML are measured in "character units" — roughly the width of the digit zero (0) in the default font. A width of 10 means the column is approximately 10 zero-characters wide. When you set ws.column_dimensions['A'].width = 20, openpyxl writes that value directly into the XML and Excel respects it on open.

This unit system is why a naive pixel calculation fails. You need to think in characters, not pixels. A practical rule of thumb: set the column width to the length of the longest string in that column, plus a small padding buffer (typically 2–4 characters), capped at a sensible maximum like 60 or 80 characters so a single outlier value doesn't produce a comically wide column.

The Core Workaround: Estimate Width from Cell Content

The most reliable approach is to iterate every cell in each column, measure the string representation of the cell value, track the maximum, and set the column width after all data is written. Here is a clean utility function you can copy directly into your project:

import openpyxl
from openpyxl.utils import get_column_letter


def autofit_columns(worksheet, min_width=8, max_width=60, padding=2):
    """Set each column's width to fit its longest cell value."""
    for col_index, column_cells in enumerate(worksheet.columns, start=1):
        max_length = 0
        for cell in column_cells:
            if cell.value is None:
                continue
            try:
                cell_text = str(cell.value)
                if len(cell_text) > max_length:
                    max_length = len(cell_text)
            except Exception:
                pass
        col_letter = get_column_letter(col_index)
        adjusted_width = min(max(max_length + padding, min_width), max_width)
        worksheet.column_dimensions[col_letter].width = adjusted_width


# Example usage
wb = openpyxl.Workbook()
ws = wb.active

headers = ["Order ID", "Customer Name", "Product", "Quantity", "Unit Price"]
rows = [
    ["ORD-10042", "Alexandra Pemberton", "Wireless Keyboard", 2, 49.99],
    ["ORD-10043", "Ji-ho Shin", "USB-C Hub", 1, 34.50],
    ["ORD-10044", "Marcus Delacroix", "27-inch Monitor", 1, 349.00],
]

ws.append(headers)
for row in rows:
    ws.append(row)

autofit_columns(ws)
wb.save("orders.xlsx")

Running this produces a workbook where every column is sized to its content rather than the default narrow width. The min_width guard prevents single-character columns from looking absurdly thin, and max_width stops a rogue long string from blowing out the layout.

Handling Merged Cells Without Breaking Widths

Merged cells introduce a subtle trap. When openpyxl iterates worksheet.columns, merged cells appear as MergedCell objects. Calling str(cell.value) on a merged cell that is not the top-left anchor returns "None", which is harmless. But the anchor cell itself may contain a long string that spans multiple columns — using its full length to size a single column would make that column far too wide.

The safest fix is to skip merged cells entirely when calculating width. openpyxl exposes the merged ranges via worksheet.merged_cells.ranges. You can build a set of merged coordinates and skip them during your iteration:

from openpyxl.utils import get_column_letter
from openpyxl.cell.cell import MergedCell


def autofit_columns_safe(worksheet, min_width=8, max_width=60, padding=2):
    """Autofit columns while skipping merged cell anchors."""
    for col_index, column_cells in enumerate(worksheet.columns, start=1):
        max_length = 0
        for cell in column_cells:
            # Skip non-anchor merged cells
            if isinstance(cell, MergedCell):
                continue
            if cell.value is None:
                continue
            try:
                cell_text = str(cell.value)
                if len(cell_text) > max_length:
                    max_length = len(cell_text)
            except Exception:
                pass
        col_letter = get_column_letter(col_index)
        adjusted_width = min(max(max_length + padding, min_width), max_width)
        worksheet.column_dimensions[col_letter].width = adjusted_width

If you are working with merged cells heavily, you may also want to look at how openpyxl can lose data in merged cells on re-open — it is a related footgun worth knowing about.

Accounting for Bold Text and Number Formats

Bold characters are physically wider than regular characters in proportional fonts. If your headers are bold and your data is not, the header row often determines the widest cell — and bold characters can be roughly 10–15% wider than their regular counterparts. A simple adjustment is to multiply the length of bold cells by a factor like 1.1:

def cell_display_length(cell):
    """Estimate the display length of a cell, accounting for bold text."""
    if cell.value is None:
        return 0
    text = str(cell.value)
    length = len(text)
    # Bold text renders roughly 10% wider
    if cell.font and cell.font.bold:
        length = int(length * 1.1)
    return length

Number formats are a separate concern. A cell that holds the float 1234567.89 might be displayed as "$1,234,567.89" due to an applied format. The str(cell.value) call gives you the raw Python value, not the formatted string. If accurate sizing matters for numeric columns, you need to reconstruct the formatted string. For most reports, sizing to the raw value plus a couple of extra padding characters is close enough. If it is not, the babel or openpyxl.styles.numbers module can help you interpret the format code, though that is a significant added complexity.

For a deep dive into why number formatting behaves unexpectedly in openpyxl, see fixing openpyxl number formatting that doesn't apply to cells.

Applying the Fix to Multiple Sheets

Real-world workbooks often have several sheets. The utility function above works on one worksheet at a time, so wrap it in a loop over workbook.worksheets to cover everything in one pass:

def autofit_all_sheets(workbook, **kwargs):
    """Apply autofit to every sheet in a workbook."""
    for sheet in workbook.worksheets:
        autofit_columns_safe(sheet, **kwargs)


# After writing all data
autofit_all_sheets(wb, min_width=10, max_width=70, padding=3)
wb.save("multi_sheet_report.xlsx")

Call autofit_all_sheets as the last step before wb.save(). If you call it earlier and then append more rows, those new rows won't be measured. Always size columns after your data is complete.

If your script writes data to a sheet and you keep hitting the wrong sheet being modified, double-check your sheet references — openpyxl writing to the wrong sheet tab is a common mistake when working with multi-sheet workbooks.

Common Pitfalls and Gotchas

Using bestFit and expecting it to work

Setting ws.column_dimensions['A'].bestFit = True alongside a width does nothing in Excel 365, Excel 2019, or LibreOffice Calc. You will still see the width you explicitly set (or the default if you set none). Remove this attribute from your code — it creates false confidence and no visible result.

Calling autofit before appending all rows

This is the most common timing mistake. If you measure columns after writing the headers but before appending data rows, the widths will only reflect the header text. Always autofit as the final step before saving.

Iterating worksheet.columns in read_only or write_only mode

If you open a workbook with read_only=True or create one with write_only=True, iterating worksheet.columns raises an error or returns nothing useful. Autofit only works in the default (normal) mode. If you are using write-only mode for performance on large files, you must calculate widths yourself before writing any rows, since you cannot iterate back over already-written rows.

Forgetting to handle formula strings

Cells containing formulas store the formula string (e.g., =SUM(A1:A10)) as the value, not the computed result, unless you opened the file with data_only=True. A formula string length has no relationship to the rendered value's length. If your sheet contains formulas, either skip formula cells during width calculation or measure the formula string and accept the imprecision. For more on this distinction, see why openpyxl reads formulas instead of computed values.

Unicode and multibyte characters

Python's len() counts code points, not rendered character widths. A CJK character (Chinese, Japanese, Korean) is typically rendered twice as wide as a Latin character. If your data contains CJK text, multiply the length of those characters by 2 before comparing. The unicodedata standard library module can help: unicodedata.east_asian_width(char) returns 'W' or 'F' for full-width characters.

import unicodedata


def display_width(text):
    """Estimate display width accounting for full-width CJK characters."""
    width = 0
    for char in str(text):
        eaw = unicodedata.east_asian_width(char)
        width += 2 if eaw in ('W', 'F') else 1
    return width

Swap in display_width(cell.value) in place of len(str(cell.value)) if your output targets audiences who write in those scripts.

Wrapping Up: Next Steps

openpyxl's lack of native autofit is a known limitation, not a bug — but with a simple character-counting utility, you get readable column widths without any external dependencies. Here are the concrete actions to take now:

1. Copy the autofit_columns_safe function into a shared utilities module in your project and import it wherever you generate Excel output.
2. Add the CJK width fix using unicodedata.east_asian_width if any of your data sources can contain non-Latin text.
3. Set a sensible max_width (60–80 characters) to prevent outlier values from producing unreadable layouts.
4. Call autofit as the last step before wb.save() — never before all rows are appended.
5. Test with your actual data by opening the file in Excel and LibreOffice Calc; rendered widths differ slightly between applications, so verify with both if cross-compatibility matters.