Rotate Text in Excel Cells with openpyxl

A report with twelve months, thirty products or a column per day across the top has a layout problem: horizontal headers force wide columns, and the table no longer fits on the screen or the page. Rotating the header labels — angled at 45 degrees, or turned vertical — lets the columns shrink to the width of their numbers. Vertical text is also the standard way to label a block of rows along its left edge. openpyxl sets rotation through the cell's alignment; the details that trip people up are the numbering of the angles and the row height, which Excel does not adjust for a file written by Python. This page is part of Styling Excel Cells with openpyxl.

Prerequisites

pip install openpyxl

The examples build a sales table with a column per month and a row per product, grouped by region.

Step 1 — Angle the column headers

Set textRotation in the header cells' Alignment, then narrow the columns — the point of rotating is that the column no longer needs the width of the label:

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

months = ["January", "February", "March", "April", "May", "June",
          "July", "August", "September", "October", "November", "December"]
wb = Workbook(); ws = wb.active; ws.title = "Sales"
ws["A1"], ws["B1"] = "Region", "Product"
header = Alignment(textRotation=45, horizontal="center", vertical="bottom")
for col, month in enumerate(months, start=3):
    cell = ws.cell(row=1, column=col, value=month)
    cell.alignment = header
    cell.font = Font(bold=True)
    ws.column_dimensions[cell.column_letter].width = 7
print(ws["C1"].alignment.textRotation, ws.column_dimensions["C"].width)

Forty-five degrees keeps the labels readable while cutting their horizontal footprint by about 30%; ninety degrees saves the most space but makes readers tilt their heads. horizontal="center" places each angled label over the middle of its column. Create one Alignment object and reuse it — the same object for every header cell is faster than a new one per cell.

Step 2 — Give the header row enough height

In Excel, rotating text by hand makes the row grow to fit. openpyxl only records the rotation, so a file it writes keeps the default row height of 15 points, and the angled labels are clipped. Estimate the height from the longest label and the angle:

import math

def rotated_row_height(labels, angle, font_size=11, padding=6):
    if angle == 255:
        return max(len(s) for s in labels) * font_size * 1.3 + padding
    degrees = angle if angle <= 90 else angle - 90
    longest = max(len(s) for s in labels) * font_size * 0.55
    line = font_size * 1.3
    rad = math.radians(degrees)
    return round(longest * math.sin(rad) + line * math.cos(rad) + padding, 1)

ws.row_dimensions[1].height = rotated_row_height(months, 45)
print("header row height:", ws.row_dimensions[1].height, "points")

The estimate treats an average character as a little over half the font size wide, which holds for proportional fonts such as Calibri; wide capitals need a bit more. The vertical extent of rotated text is its length times the sine of the angle plus a line height times the cosine. For "September" at 45 degrees and 11-point type, that is about 54 points.

Step 3 — Label a block of rows vertically

A vertical label along the left of a group — the region for a block of product rows — is a merged cell with text rotated 90 degrees and centred both ways:

products = ["Brackets", "Hinges", "Panels", "Frames"]
row = 2
for region in ["North", "South"]:
    first = row
    for product in products:
        ws.cell(row=row, column=2, value=product)
        for col in range(3, 15):
            ws.cell(row=row, column=col, value=(row * 37 + col * 11) % 90 + 10)
        row += 1
    ws.merge_cells(start_row=first, start_column=1, end_row=row - 1, end_column=1)
    label = ws.cell(row=first, column=1, value=region)
    label.alignment = Alignment(textRotation=90, horizontal="center", vertical="center")
    label.font = Font(bold=True)
ws.column_dimensions["A"].width = 5
ws.column_dimensions["B"].width = 12
ws.freeze_panes = "C2"
wb.save("rotated_headers.xlsx")
print(ws.merged_cells.ranges)

The merged cell's formatting lives in its top-left cell, so the alignment goes on that cell. Column A can be narrow — five characters is plenty — because the label runs up the height of four rows. Freezing panes at C2 keeps both the angled headers and the region and product labels visible while scrolling; see Freeze the Header Row in Excel with openpyxl.

Step 4 — Rotate downward and stack letters

Values from 91 to 180 tilt the text downward: 90 plus the number of degrees below horizontal. And 255 is a special value for stacked text, letters one beneath another without rotation, sometimes used for short codes in narrow columns:

wb2 = Workbook(); ws2 = wb2.active
for col, (label, angle) in enumerate([("Up 45", 45), ("Down 45", 135), ("Up 90", 90), ("Down 90", 180), ("STACK", 255)], start=1):
    ws2.cell(row=1, column=col, value=label).alignment = Alignment(textRotation=angle, horizontal="center")
    ws2.column_dimensions[ws2.cell(row=1, column=col).column_letter].width = 8
ws2.row_dimensions[1].height = rotated_row_height(["STACK"], 255)
wb2.save("rotation_samples.xlsx")
print([c.alignment.textRotation for c in ws2[1]], ws2.row_dimensions[1].height)

Any value outside 0–180 other than 255 is invalid and openpyxl raises a ValueError when you set it. Stacked text needs the most height of all — one line per letter — which is why the helper treats 255 separately; five letters need almost 80 points.

The same in xlsxwriter

xlsxwriter uses signed angles instead: set_rotation(45) tilts upward, set_rotation(-45) downward, and set_rotation(270) stacks the text:

import xlsxwriter

book = xlsxwriter.Workbook("rotated_xlsxwriter.xlsx")
sheet = book.add_worksheet()
angled = book.add_format({"rotation": 45, "bold": True, "align": "center"})
sheet.set_row(0, rotated_row_height(months, 45))
sheet.write_row(0, 0, months, angled)
sheet.set_column(0, len(months) - 1, 7)
book.close()
print("written with rotation", angled.rotation)

xlsxwriter converts the signed angle to Excel's own scale when it writes the file: -45 ends up as 135 inside the workbook and 270 as 255 — the same values openpyxl reads back and uses.

Rotate, wrap or abbreviate?

Rotation is one of three ways to fit long headers over narrow columns, and not always the best. Wrapping keeps text horizontal and splits it over two or three lines — right for two-word headers such as "Units sold" but useless for single long words. Abbreviating — Jan, Feb, Mar — is the most readable when a standard short form exists, and needs no extra height at all. Rotating suits long single words, product names and dates where no abbreviation is obvious. Many reports combine them: abbreviated months rotated 45 degrees fit twelve columns into the width of four unrotated ones. When the sheet has an autofilter, leave a little extra height: the filter button sits in the bottom-right corner of each header cell and can cover the end of an angled label.

Rotated text when printing

Angled headers print exactly as they appear, which makes them a good fit for wide reports that must fit a page's width. Combine them with the page setup — landscape orientation and fit-to-width — and repeat the header row on every printed page, so the angled labels appear above the data on page two as well; Set Print Area and Page Setup with openpyxl covers those settings.

Common pitfalls

• Clipped labels. Set the header row height; openpyxl does not grow it for you.
• Negative angles in openpyxl. Use 91–180 for downward text; negative numbers raise an error.
• Rotating without narrowing the columns. The space saving only happens if you reduce the widths.
• Formatting the wrong cell of a merge. Put the alignment on the top-left cell of the merged range.
• Vertical text for long labels. Past a dozen characters, a 45-degree angle reads more easily than 90.

Performance notes

Rotation is a style property like any other, so reuse one Alignment object across all the header cells; headers are a single row, so the cost is negligible either way.

Conclusion

Rotate header labels with Alignment(textRotation=45) and narrow the columns to their data, use 91–180 for downward text and 255 for stacked letters, and put vertical labels for row groups in merged cells rotated 90 degrees and centred. Set the header row height yourself from the longest label and the angle, because openpyxl does not resize rows for rotated text. In xlsxwriter, use the rotation format property with signed angles.

Frequently asked questions

How do I rotate text in a cell with openpyxl? Set the cell's alignment with a textRotation value: cell.alignment = Alignment(textRotation=45). Values from 1 to 90 tilt the text upward, counter-clockwise, by that many degrees.

How do I rotate text downward with openpyxl? Use a value from 91 to 180, where 90 + n means n degrees downward (clockwise). textRotation=135 tilts the text 45 degrees downward, and 180 turns it to read straight down.

How do I make vertical stacked text, one letter under another? Use textRotation=255. Excel reserves that value for stacked text, where the letters are not rotated but placed one below the other. Make the row tall enough for every letter.

Why is my rotated header text cut off? Rotation does not change the row height automatically when the file is written by Python. Set the header row's height yourself, based on the longest label and the angle.

Related

• Up one level: Styling Excel Cells with openpyxl — fonts, fills, borders and alignment.
• Wrap Text and Align Cells with openpyxl — the other alignment options.
• Set Column Width and Row Height in openpyxl — sizing rows and columns.
• Merge Cells and Centre a Report Title with openpyxl — merged labels.