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. Once the columns are sized, it usually pays to freeze the header row so labels stay visible while the sheet scrolls. Every block runs in order against a sample workbook built first.

Column width in character units and row height in points A small grid shows column width measured in character units along the top and row height measured in points down the side, set through column_dimensions and row_dimensions. A1 B1 A2 B2 width = 18 character units height = 30 pt column_dimensions ["A"].width row_dimensions [1].height

Prerequisites

  • Python 3.8 or newer.
  • The openpyxl package:
Bash
pip install openpyxl
  • A basic grasp of opening, editing, and saving a workbook. If any of that is unfamiliar, start with Using openpyxl for Excel File Manipulation — it covers the Workbook, load_workbook, and save calls that every block below relies on.

Column width and row height are properties of the worksheet, not of individual cells, so you set them once per column or row rather than cell by cell.

Create a sample workbook

Every snippet below runs against this file, so build it first:

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:

The auto-fit pass for one column A four-stage pipeline, repeated for every column: scan the cells and take the longest len(str(value)), keep that maximum, add a small padding, then assign the total to column_dimensions[letter].width. Here the longest value is 28 characters, plus 2 padding gives a width of 30. for each column scan the cells len(str(value)) keep the max 28 longest value add padding 28 + 2 breathing room set the width 30 .width = 30 next column
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. It also measures the stored value, not what the reader sees — a number displayed through a number or date format can render wider or narrower than str(value) suggests, so size those columns after you apply the format. 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.

Widths are characters, heights are points

The two different units openpyxl uses Column width is measured in characters of the default font, so a width of ten fits about ten digits. Row height is measured in points, where a standard row is fifteen points and a wrapped two-line row needs about thirty. width: characters ~10 fits ten digits font-dependent no true auto-fit height: points 15 is the default row ~30 for two lines set it when wrapping
Python
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

wb = load_workbook("report.xlsx")
ws = wb["Orders"]

for cells in ws.columns:
    letter = get_column_letter(cells[0].column)
    longest = max((len(str(c.value)) for c in cells if c.value is not None), default=0)
    ws.column_dimensions[letter].width = min(max(longest + 3, 9), 42)

ws.row_dimensions[1].height = 30                # room for a wrapped header
wb.save("report_sized.xlsx")

Because the units differ, the two settings need different reasoning. Widths can be approximated from character counts, which is what makes the loop above good enough for every report. Heights only need attention where text wraps or where a row carries a larger font — Excel auto-fits height when a user types, but never when openpyxl writes, so a wrapped header in a default row shows one line.

Hiding a column is the same mechanism: ws.column_dimensions["G"].hidden = True keeps a working column available to formulas while removing it from the reader's view, which is usually better than deleting it and rebuilding every reference.

Sizes are part of the report

Column widths are as much a part of a report's meaning as its numbers: a truncated header or a column of #### tells the reader the file was not finished. Because openpyxl cannot auto-fit, deriving widths from the content — with a floor, a ceiling and a few characters of padding — is the practical equivalent, and running it over every sheet as part of the finishing pass keeps a whole workbook consistent.

Cap the widest column

Without an upper bound, one long free-text column stretches past the screen and pushes every figure out of view. A ceiling of about forty characters keeps the table readable and lets the text wrap instead.

Presentation comes after the data

Any write replaces what it covers, so formatting, filters, images and charts belong in a single finishing pass that runs after the last value has been written. Splitting the job that way — build the frame, write it, then decorate the finished sheet — is what stops a style disappearing the month someone adds a to_excel call in the middle. It also gives a report one obvious place to change when the house style moves, instead of a dozen scattered blocks that have to be found first.

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.