Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Wrap Text and Align Cells with openpyxl

Make long text readable in a generated Excel report — wrap text inside cells, set row heights that fit the wrapped lines, align text to the top so multi-line rows read cleanly, indent hierarchies, rotate narrow headers, centre a title across columns without merging and shrink text as a last resort.
Cell styling & layoutopenpyxl

Long text in a report — comments, descriptions, addresses, ticket summaries — either spills across neighbouring empty cells or, once the neighbour holds a value, is cut off at the column edge. Wrapping fixes that, but it brings its own questions: how tall should the row be, where should text sit in a tall row, and what about the short cells next to it? openpyxl controls all of it through the cell's Alignment. This guide, part of Styling Excel Cells with openpyxl, wraps and aligns a ticket list, sizes the rows, and covers indentation, rotation and centring without merging.

A ticket list with wrapped, top-aligned descriptions Three tickets. T-1 has a long description wrapped over three lines, T-2 a short note on one line and T-3 a note with explicit line breaks over three lines. All cells are aligned to the top, so the ticket numbers sit level with the first line of their descriptions. A B C 1 Ticket Priority Description 2 T-1 High Customer reports that the ↵ monthly export stops at ↵ row 65,536 … 3 T-2 Low Short note 4 T-3 Medium Line one ↵ Line two ↵ Line three wrap_text breaks at the column width and at newlines; vertical="top" keeps rows aligned

Prerequisites

Bash
pip install openpyxl

A ticket list with a mix of long, short and multi-line text:

Python
import math
from copy import copy

from openpyxl import Workbook
from openpyxl.styles import Alignment, Font

tickets = [
    ("T-1", "High", "Customer reports that the monthly export stops at row 65,536 when opened in the old viewer."),
    ("T-2", "Low", "Short note"),
    ("T-3", "Medium", "Line one\nLine two\nLine three"),
]
wb = Workbook()
ws = wb.active
ws.title = "Tickets"
ws.append(["Ticket", "Priority", "Description"])
for ticket in tickets:
    ws.append(ticket)
ws.column_dimensions["A"].width = 10
ws.column_dimensions["B"].width = 10
ws.column_dimensions["C"].width = 34

Step 1 — Wrap the text and align it to the top

Give every body cell an Alignment with wrap_text=True and vertical="top". Wrapping breaks the text at the column width and at newline characters; top alignment makes every cell in a tall row start on the same line:

Python
for row in ws.iter_rows(min_row=2):
    for cell in row:
        cell.alignment = Alignment(wrap_text=True, vertical="top")
wb.save("tickets.xlsx")
print(ws["C4"].alignment.wrap_text, ws["C4"].alignment.vertical)     # True top

Without vertical="top", Excel's default bottom alignment puts "T-1" and "High" level with the last line of a three-line description, which reads as though they belong to the row below. Top alignment is almost always right for lists with wrapped text. Newlines in the data — "Line one\nLine two" — only show as line breaks when wrapping is on; otherwise the text runs together.

Step 2 — Set row heights that fit

Whether rows grow to fit wrapped text depends on the application: rendering this file in LibreOffice adjusted the heights automatically, while Excel does not always recalculate heights for files it did not write itself. When the layout matters, set the heights. A good estimate counts the lines each cell needs at its column width:

Python
def lines_needed(text, width_chars):
    if text is None:
        return 1
    return sum(max(1, math.ceil(len(part) / max(width_chars - 1, 1))) for part in str(text).split("\n"))

def fit_row_heights(ws, line_height=15, min_row=2):
    for row in ws.iter_rows(min_row=min_row):
        lines = 1
        for cell in row:
            if cell.alignment.wrap_text:
                width = ws.column_dimensions[cell.column_letter].width or 8.43
                lines = max(lines, lines_needed(cell.value, width))
        ws.row_dimensions[row[0].row].height = lines * line_height

fit_row_heights(ws)
wb.save("tickets.xlsx")
print([ws.row_dimensions[r].height for r in range(2, 5)])       # [45.0, 15.0, 45.0]

Column width in openpyxl is measured in characters of the default font, and a line of 11-point Calibri is about 15 points tall, so the estimate is close for ordinary text. Wide letters and bold text need a little more room; add a line of slack for descriptions where clipping would hide information. Column widths themselves are covered in Set Column Width and Row Height in openpyxl.

Letting the application size rows or setting heights Leaving heights unset relies on the application to autofit wrapped rows, which LibreOffice did in our test but Excel does not always do for generated files. Setting heights from an estimate of wrapped lines gives the same layout everywhere, at the cost of occasionally too much or too little space for unusual text. Leave heights unset no code exact fit where autofit runs rows may stay one line tall depends on the application Set heights from an estimate same layout everywhere predictable printouts estimate, not a measurement update if widths change

Step 3 — Horizontal alignment that matches the content

Text reads best left-aligned and numbers right-aligned — which is Excel's default for each type — so the main job is to leave them alone and fix the exceptions. Short codes such as priorities look tidier centred, and headers should align with the data beneath them:

Python
ws["B1"].alignment = Alignment(horizontal="center", vertical="bottom")
for (cell,) in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    cell.alignment = Alignment(horizontal="center", vertical="top", wrap_text=True)
for cell in ws[1]:
    font = copy(cell.font)
    font.bold = True
    cell.font = font
wb.save("tickets.xlsx")

Every assignment replaces the whole Alignment, so repeat vertical="top" and wrap_text when changing only the horizontal setting — or copy the existing alignment and change one attribute, the same pattern used for fonts. A header over a numeric column should be right-aligned so it sits above the digits it describes; a centred header over right-aligned numbers looks slightly off, especially when the values vary in length.

Step 4 — Indent hierarchies and rotate narrow headers

Two less common settings solve specific layout problems. indent shows a hierarchy in one column — categories and their items — without extra columns. text_rotation turns long headers above narrow columns sideways:

Python
ws2 = wb.create_sheet("Budget")
for label, level in [("Operations", 0), ("Rent", 1), ("Utilities", 1), ("Staff", 0), ("Salaries", 1)]:
    ws2.append([label])
    ws2.cell(ws2.max_row, 1).alignment = Alignment(horizontal="left", indent=level * 2)
ws2["C1"], ws2["D1"] = "Actual this month", "Budget this month"
for cell in (ws2["C1"], ws2["D1"]):
    cell.alignment = Alignment(text_rotation=90, horizontal="center", vertical="bottom")
ws2.row_dimensions[1].height = 110
ws2.column_dimensions["C"].width = ws2.column_dimensions["D"].width = 6
wb.save("tickets.xlsx")
print(ws2["A2"].alignment.indent, ws2["C1"].alignment.text_rotation)     # 2.0 90

Indent levels are counted in steps of about three characters; two levels per hierarchy step reads clearly. Rotated headers need a taller header row — set it explicitly, because the rotated text will not grow the row in every application. text_rotation takes 0 to 90 for text rising to the right and 91 to 180 for text falling, where 180 means −90°; 255 stacks the letters vertically, one under another, which is rarely easier to read than simple rotation.

Alignment settings and what they solve wrap_text fits long text in a column. vertical top keeps multi-line rows aligned. horizontal centerContinuous centres a title across columns without merging. indent shows hierarchy in a single column. text_rotation fits long headers over narrow columns. shrink_to_fit makes text smaller instead of wrapping it. Solves wrap_text=True long text in a column vertical="top" misaligned multi-line rows horizontal="centerContinuous" titles without merging indent=n hierarchy in one column text_rotation=90 long headers narrow columns shrink_to_fit=True slightly too long labels

Step 5 — Centre a title without merging

A report title centred over the table is usually made with merged cells, which then get in the way of sorting, selecting and copying. centerContinuous centres text across the selected cells while keeping them separate — set it on the title cell and on the empty cells it should span:

Python
ws.insert_rows(1)
ws["A1"] = "Open tickets"
for col in "ABC":
    ws[f"{col}1"].alignment = Alignment(horizontal="centerContinuous")
for r in range(2, ws.max_row + 1):                         # heights did not move with the rows
    ws.row_dimensions[r].height = None
fit_row_heights(ws, min_row=3)
wb.save("tickets.xlsx")
print(ws["A1"].value, ws["C1"].alignment.horizontal)      # Open tickets centerContinuous
print([ws.row_dimensions[r].height for r in range(3, 6)])  # [45.0, 15.0, 45.0]

The title appears centred across A1:C1, yet clicking C1 selects an ordinary empty cell. Note the extra lines: insert_rows moves cell values and styles down but leaves row heights at their old row numbers, so the 45-point height meant for T-1 would now sit on the header row. Clearing the heights and fitting them again after any row insertion keeps them with their rows. The trade-offs against a real merge are covered in Merge Cells and Centre a Report Title with openpyxl. The last option, shrink_to_fit=True, reduces the font size until the text fits the cell; it suits labels that are only slightly too long, and becomes unreadable for anything longer — wrap instead.

Common pitfalls

  • Vertical alignment on the header only. Aligning body cells to the top but leaving the header at the bottom is fine; the reverse — body at the bottom — is what makes tall rows hard to scan.
  • Wrapping without widths. A wrapped cell in a default-width column breaks after eight characters. Set a sensible column width first.
  • Replacing alignment piecemeal. Alignment(horizontal="center") resets wrap_text and vertical. Pass all the settings a cell needs, or copy and modify.
  • Inserting rows after sizing them. Row heights stay at their row numbers when rows are inserted or deleted. Size rows last, after the sheet's structure is final.
  • Wrapping numbers. Wrapping a numeric column never helps; a number too wide for its column shows as ####. Widen the column or change the number format.

Conclusion

Wrap long text with Alignment(wrap_text=True, vertical="top"), and set row heights from an estimate of the wrapped lines so the layout does not depend on whether the application autofits. Keep text left and numbers right, centre short codes, indent hierarchies, rotate headers over narrow columns and centre titles with centerContinuous instead of merging. Remember that each assignment replaces the whole alignment, so pass every setting a cell needs.

Frequently asked questions

How do I wrap text in a cell with openpyxl? Assign an Alignment with wrap_text=True, for example cell.alignment = Alignment(wrap_text=True, vertical="top"). The text then breaks at the column width and at any newline characters in the value.

Why do my wrapped rows not get taller? Whether rows grow automatically depends on the application. LibreOffice adjusted rows without a set height in our test; Excel does not always recalculate heights for files it did not write. Set row_dimensionsrow.height explicitly when the layout matters.

How do I centre a title across several columns without merging? Put the text in the first cell and set horizontal="centerContinuous" on that cell and the empty cells to its right. The title is centred across them, while the cells stay separate for sorting and selection.

How do I indent text in a cell? Use Alignment(indent=n) with a horizontal alignment of left or right. Each level indents by roughly the width of three characters, which suits category and sub-category labels.

More on cell styling & layout

More on cell styling & layout →