Python Excel AutomationPractical guides for developers
Start learning

Guide

Formatting And Charting Excel Reports With PythonDeep dive

Set Column Width and Row Height in openpyxl

Set Excel column widths and row heights from Python with openpyxl, build an auto-fit pass by measuring content, hide columns, and set sensible defaults — runnable.

openpyxl gives you direct control over how wide each column is and how tall each row is, but the units are not pixels and there is no real auto-fit — you compute it yourself. This guide, part of Styling Excel Cells with openpyxl, shows the exact calls, an auto-fit pass you can drop into any generator, plus hiding and defaults. Every block runs in order against a sample workbook built first.

Install and create a sample workbook

Bash
pip install openpyxl
Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Inventory"

ws.append(["SKU", "Product Name", "Warehouse Location", "Qty"])
for row in [
    ["A-1001", "Stainless Steel Water Bottle", "Aisle 12, Bin 4", 340],
    ["A-1002", "Bamboo Cutting Board",         "Aisle 3, Bin 19", 88],
    ["A-1003", "Ceramic Pour-Over Dripper",    "Aisle 7, Bin 2",  152],
]:
    ws.append(row)

wb.save("sized_report.xlsx")
print("Sample workbook created")

Set column width

Set a column's width through ws.column_dimensions[letter].width. The unit is approximately the number of characters of the default font that fit in the column — not pixels. A width of 10 shows roughly 10 characters:

Python
from openpyxl import load_workbook

wb = load_workbook("sized_report.xlsx")
ws = wb["Inventory"]

ws.column_dimensions["A"].width = 12   # SKU
ws.column_dimensions["B"].width = 32   # Product Name (longest text)
ws.column_dimensions["C"].width = 22   # Warehouse Location
ws.column_dimensions["D"].width = 8    # Qty

wb.save("sized_report.xlsx")
print("Column widths set")

You always index column_dimensions by the column letter ("A", "B", ...), never by a number. If you have a 1-based index, convert it with get_column_letter.

Set row height

Set a row's height through ws.row_dimensions[index].height. Here the unit is points (1/72 inch), the same unit as font size. Give the header room and bump a data row:

Python
from openpyxl import load_workbook

wb = load_workbook("sized_report.xlsx")
ws = wb["Inventory"]

ws.row_dimensions[1].height = 24   # taller header
ws.row_dimensions[2].height = 18   # one roomier data row

wb.save("sized_report.xlsx")
print("Row heights set")

Auto-fit columns by measuring content

openpyxl has no true auto-fit, so you compute a width from the longest value in each column. Walk every cell, take len(str(value)), track the per-column maximum, add a small padding, then assign it. Convert the column index to a letter with get_column_letter:

Python
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

wb = load_workbook("sized_report.xlsx")
ws = wb["Inventory"]

for col_cells in ws.columns:
    longest = 0
    letter = get_column_letter(col_cells[0].column)   # .column is 1-based int
    for cell in col_cells:
        if cell.value is not None:
            longest = max(longest, len(str(cell.value)))
    ws.column_dimensions[letter].width = longest + 2   # padding

wb.save("sized_report.xlsx")
print("Columns auto-fitted")

This is an approximation: character count ignores font, bold weight, and proportional glyph widths, so wide content in a bold or large font may still clip slightly. Clamp the result with min(longest + 2, 60) if a single long value would otherwise blow out the layout.

Hide a column

Hide a column without deleting its data by setting hidden=True on its dimension. The data stays in the file and reappears if the user unhides it:

Python
from openpyxl import load_workbook

wb = load_workbook("sized_report.xlsx")
ws = wb["Inventory"]

ws.column_dimensions["C"].hidden = True   # hide Warehouse Location

wb.save("sized_report.xlsx")
print("Column C hidden")

The same attribute exists for rows: ws.row_dimensions[2].hidden = True.

Set a default width and height

Apply a baseline size to every column or row through the sheet's sheet_format. Per-column and per-row settings still override the default where you set them:

Python
from openpyxl import load_workbook

wb = load_workbook("sized_report.xlsx")
ws = wb["Inventory"]

ws.sheet_format.defaultColWidth = 15
ws.sheet_format.defaultRowHeight = 16

wb.save("sized_report.xlsx")
print("Defaults set")

Common pitfalls

SymptomCauseFix
Columns far too narrow or wideTreating width as pixelswidth ≈ character count of the default font; a value of 30 is ~30 characters
KeyError or no effect when sizing a columnIndexing column_dimensions by a numberUse the letter: column_dimensions["B"], or convert with get_column_letter(idx)
Row height ignoredConfusing height units with width unitsRow height is in points (like font size), not characters
"Auto-fit" call not foundopenpyxl has no autofit()Measure len(str(value)) per column and set width yourself
Wide font still clipsCharacter count ignores bold/large fontsAdd extra padding or measure against the actual font if exactness matters
Hidden column lost its dataConfusing hidden=True with deletionhidden=True keeps the data; only ws.delete_cols() removes it

A note on scale

The auto-fit pass iterates every cell, so for sheets with hundreds of thousands of rows it gets slow and memory-heavy. Sample the first few hundred rows, or cap the measured length, instead of scanning the whole column — the header plus a representative slice usually gives a good enough width.

Frequently asked questions

What unit is column width in openpyxl? Roughly the number of characters of the workbook's default font that fit in the column. It is not pixels and not points. A width of 20 shows about 20 characters.

What unit is row height? Points — 1/72 of an inch, the same unit used for font size. A 14-point font fits comfortably in a row about 18 points tall.

How do I auto-fit columns? openpyxl cannot measure rendered text, so there is no built-in auto-fit. Loop the cells, take the longest len(str(value)) per column, add padding, and assign it to column_dimensions[letter].width.

Why do I get a KeyError when setting a width? You indexed column_dimensions with a number. It is keyed by column letter. Convert an index with from openpyxl.utils import get_column_letter.

Does hiding a column delete its data? No. ws.column_dimensions["C"].hidden = True only hides it; the values remain in the file and reappear when unhidden. Use ws.delete_cols() to actually remove a column.

Conclusion

Column width is set in approximate character units through ws.column_dimensions[letter].width, and row height in points through ws.row_dimensions[index].height. There is no native auto-fit, so measure len(str(value)) per column and pad the result — clamping it for safety. Index columns by letter, convert from numbers with get_column_letter, and use hidden=True plus sheet_format defaults to round out the layout.

Where to go next