Write Rich Text in a Cell with openpyxl

Put more than one format in a single cell — a red, bold OVERDUE inside a status line, a highlighted keyword in a comment, H₂O and m² with real subscripts and superscripts. Build CellRichText from TextBlock and InlineFont objects, generate it from data, read existing rich text back, and avoid the load-and-save that silently flattens it.

Cell styling & layout openpyxl

Normal cell formatting applies to the whole cell: the entire value is bold, or red, or italic. Sometimes one word needs to stand out from the rest — the "OVERDUE" in a status line, the search term inside a long note, the units in a heading — or a chemical formula needs a real subscript. Excel supports this as rich text: a cell value made of runs, each with its own font. openpyxl has supported writing and reading rich text since version 3.1. This page, part of Styling Excel Cells with openpyxl, builds rich text by hand and from data, and shows the one setting that stops it being lost.

If you are writing a new file with xlsxwriter, its write_rich_string does the same job; see Write Rich Text with Mixed Formats in One Cell with xlsxwriter.

Prerequisites

Bash

pip install "openpyxl>=3.1" pandas

invoices.xlsx has three invoices with a status, the days overdue and a free-text note.

Step 1 — Write mixed formatting in one cell

A CellRichText is a sequence of parts. Plain strings are shown in the cell's own font; each TextBlock pairs an InlineFont with the text it formats. Give inline fonts an explicit font name and size — a run without them falls back to the application's default, which in some programs is a serif font that looks out of place:

Python

from openpyxl import Workbook
from openpyxl.cell.rich_text import CellRichText, TextBlock
from openpyxl.cell.text import InlineFont

def font(sz=11, **kw):
    return InlineFont(rFont="Calibri", sz=sz, **kw)

wb = Workbook(); ws = wb.active
ws["A1"] = CellRichText("Status: ", TextBlock(font(b=True, color="FFC00000"), "OVERDUE"), " since 12 Sep")
ws["A2"] = CellRichText(TextBlock(font(b=True, sz=14), "Q3 report "), TextBlock(font(i=True, color="FF808080"), "(draft)"))
ws.column_dimensions["A"].width = 36
wb.save("rich_basics.xlsx")
print(type(ws["A1"].value).__name__, "->", str(ws["A1"].value))

str() of a rich text value is its plain text, "Status: OVERDUE since 12 Sep" — useful for widths, searching and logging. Colours are ARGB hex strings, like everywhere else in openpyxl. InlineFont takes the run-level font options: b, i, u for underline, strike, color, sz, rFont for the font name and vertAlign.

Step 2 — Generate rich text from data

The useful case is building these values from a table: colour the status word by its value, and highlight a keyword wherever it appears in a note. re.split with a capturing group keeps the matches, so they can be wrapped in a formatted block while the rest stays plain:

Python

import re
import pandas as pd
from openpyxl import load_workbook

STATUS = {"overdue": "FFC00000", "disputed": "FFB4740A", "paid": "FF0B6157"}

def status_line(status, days):
    word = TextBlock(font(b=True, color=STATUS[status]), status.upper())
    tail = f" — {days} days" if days else ""
    return CellRichText(word, tail)

def highlight(text, term):
    parts = re.split(f"({re.escape(term)})", text, flags=re.IGNORECASE)
    return CellRichText(*[TextBlock(font(b=True, u="single"), p) if p.lower() == term.lower() else p for p in parts if p])

df = pd.read_excel("invoices.xlsx")
df.to_excel("invoices_rich.xlsx", index=False)
wb = load_workbook("invoices_rich.xlsx", rich_text=True)
ws = wb.active
for row, r in enumerate(df.itertuples(index=False), start=2):
    ws.cell(row=row, column=2).value = status_line(r.Status, r.Days)
    ws.cell(row=row, column=4).value = highlight(r.Note, "audit")
ws.column_dimensions["B"].width = 20
ws.column_dimensions["D"].width = 44
wb.save("invoices_rich.xlsx")
print([str(ws.cell(row=i, column=2).value) for i in range(2, 5)])
print([len(ws.cell(row=i, column=4).value) for i in range(2, 5)], "parts per note")

pandas writes the table; openpyxl then replaces two columns with rich text. The notes that mention "audit" become two parts each — the bold underlined word and the text beside it, with the empty strings re.split leaves at either end filtered out — and the one without it stays a single plain part. Note the rich_text=True when loading: it is what keeps these values intact on the next save.

Two other places rich text earns its keep in reports. A title cell can carry the report name in large bold type and the period or "draft" marker in smaller grey text, in one cell that stays aligned with the table below — no second row, no merged cells. And a legend or key above a table can show each status word in the colour the table uses for it, so readers learn the colour code without a separate chart. In both cases size the column from the plain text, len(str(cell.value)), since the rich value's length is its number of parts, not characters.

Step 3 — Subscripts and superscripts

Units and formulas need characters raised or lowered within the line — m², CO₂, footnote markers. vertAlign on an inline font does it, and unlike Unicode superscript characters it works for any text:

Python

wb = Workbook(); ws = wb.active
sub, sup = font(vertAlign="subscript"), font(vertAlign="superscript")
ws["A1"] = CellRichText("Floor area (m", TextBlock(sup, "2"), ")")
ws["A2"] = CellRichText("CO", TextBlock(sub, "2"), " emissions")
ws["A3"] = CellRichText("Revenue", TextBlock(sup, "1"))
wb.save("scripts.xlsx")
print([str(ws[c].value) for c in ("A1", "A2", "A3")])

The plain-text versions read "Floor area (m2)" and "CO2 emissions", which is what a search or an export sees. In the sheet the 2s are raised and lowered. Footnote markers like the ¹ after "Revenue" pair well with a note below the table.

Step 4 — Read rich text back, and keep it

By default load_workbook returns rich text cells as plain strings. That is convenient for reading data, and dangerous for editing: save the workbook and the plain strings are written back, removing the formatting. Compare the two ways of loading:

Python

plain = load_workbook("invoices_rich.xlsx").active["B2"].value
rich = load_workbook("invoices_rich.xlsx", rich_text=True).active["B2"].value
print(type(plain).__name__, repr(plain))
print(type(rich).__name__, [(type(p).__name__, str(p)) for p in rich])

load_workbook("invoices_rich.xlsx").save("flattened.xlsx")
print("after a plain load and save:", type(load_workbook("flattened.xlsx", rich_text=True).active["B2"].value).__name__)

With rich_text=True, the value is a CellRichText whose parts can be inspected — each TextBlock exposes .font and .text — and edited. Without it, the value is a str, and the round trip through load_workbook and save produces a file where the status cell is plain text. When a script opens a template or a report that anyone might have formatted by hand, load it with rich_text=True if it will be saved.

Limits worth knowing

Rich text applies to text values only. A formula's result cannot be partly bold — for a formula cell, use a normal cell font or conditional formatting instead. Cell-level fonts still apply to the plain-string parts, so a cell with Font(size=9) shows its plain parts at 9 points while TextBlocks use their own sizes; set both consistently. And a very long cell with many runs is hard to edit by hand in Excel; keep rich text for short labels and highlights, not whole paragraphs of formatted prose.

Common pitfalls

Saving without rich_text=True. A plain load flattens every rich text cell on save.
Inline fonts without a name and size. Runs fall back to the application default; set rFont and sz.
Colours without alpha. Use ARGB strings such as FFC00000.
Formatting formula results. Rich text is for text constants only.
Expecting pandas to write it. Write with pandas, then replace cells with openpyxl.
Old openpyxl versions. CellRichText needs openpyxl 3.1 or later.

Performance notes

Building a CellRichText is cheap, but each run adds XML to the shared strings; thousands of rich cells are fine, millions will make the file large and slow to open. Create each InlineFont once and reuse it, as the STATUS mapping does with colours.

Conclusion

Build rich text from plain strings and TextBlock(InlineFont(...), text) parts, and assign the CellRichText to a cell. Generate it from data — status colours from a mapping, keywords split out with re.split — and use vertAlign for subscripts and superscripts. Give inline fonts an explicit name and size, read existing rich text with load_workbook(..., rich_text=True), and always load that way before saving a workbook that contains rich text.

Frequently asked questions

Can openpyxl write text with different formats in one cell? Yes, from openpyxl 3.1. Build a CellRichText from plain strings and TextBlock objects, where each TextBlock pairs an InlineFont — bold, italic, colour, size, font name — with the text it applies to, and assign it to the cell's value.

Why does my rich text disappear when I open and save a workbook with openpyxl? By default load_workbook reads rich text cells as plain strings, and saving writes those plain strings back. Pass rich_text=True to load_workbook whenever you will save a workbook that contains rich text.

How do I write subscript or superscript text in Excel with Python? Use an InlineFont with vertAlign="subscript" or vertAlign="superscript" for the characters that should be lowered or raised, such as the 2 in H2O or m2.

Can pandas write rich text to Excel? No. to_excel writes each value as a plain cell. Write the table with pandas, then open the file with openpyxl and replace the cells that need mixed formatting with CellRichText values — or use xlsxwriter's write_rich_string while writing.

Up one level: Styling Excel Cells with openpyxl — whole-cell fonts, fills and borders.
Write Rich Text with Mixed Formats in One Cell with xlsxwriter — the xlsxwriter equivalent.
Highlight Cells Containing Specific Text with openpyxl — whole-cell highlighting by rule.
Populate an Excel Template Without Losing Formatting — other things a load and save can lose.

More on cell styling & layout