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

Write Rich Text with Mixed Formats in One Cell with xlsxwriter

Put bold labels, coloured status words, superscripts and line breaks inside a single Excel cell with xlsxwriter's write_rich_string — the argument order, the silent return codes when a string is rejected, combining with pandas output, and keeping rich text intact when openpyxl edits the file later.
Cell styling & layoutxlsxwriter

Most cells hold one style: a bold header, a red number. Some content reads better with mixed formatting inside the cell — a status line such as "Status: overdue since 3 March" with the key word in red, a unit with a superscript like m³, or a note whose first word is bold. Excel calls this rich text, and xlsxwriter writes it with write_rich_string. This guide, part of Building Excel Reports with xlsxwriter, covers the argument pattern, the quiet ways a rich string can fail, and how to keep the formatting when another tool edits the file afterwards.

Cells with mixed formatting Cell A1 reads Status overdue since 3 March with the word overdue in bold red. Cell A2 shows CO2 emissions in m3 with the 2 as a subscript and the 3 as a superscript. Cell A3 has a bold Note label followed by a second line of plain text in a wrapped cell. A 1 Rich text in one cell 2 Status: OVERDUE since 3 March 3 CO₂ emissions in m³ 4 Note: ↵ check the March accruals each cell holds one string made of differently formatted fragments

Prerequisites

Bash
pip install xlsxwriter openpyxl pandas

Step 1 — Fragments and formats

write_rich_string takes the cell, then a sequence of strings and formats. A format applies to the string immediately after it; a string with no format before it uses the cell's default font:

Python
import xlsxwriter

wb = xlsxwriter.Workbook("rich.xlsx")
ws = wb.add_worksheet("Notes")
ws.set_column("A:A", 42)

red_bold = wb.add_format({"bold": True, "font_color": "#B91C1C"})
result = ws.write_rich_string("A1", "Status: ", red_bold, "overdue", " since 3 March")
print(result)                                # 0 means written

Read the arguments in pairs from the left: "Status: " is plain, red_bold applies to "overdue", and " since 3 March" is plain again. Spaces belong inside the fragments — there is no automatic separator, so "Status:" followed by "overdue" without a space would read "Status:overdue". The whole cell is still one text value; formulas that refer to it see "Status: overdue since 3 March".

Step 2 — Superscripts, subscripts and line breaks

The same pattern handles scientific and unit notation with font_script — 1 for superscript, 2 for subscript — and multi-line notes with a newline plus a wrapped cell format as the final argument:

Python
sub = wb.add_format({"font_script": 2})
sup = wb.add_format({"font_script": 1})
bold = wb.add_format({"bold": True})
wrap = wb.add_format({"text_wrap": True, "valign": "top"})

ws.write_rich_string("A2", "CO", sub, "2", " emissions in m", sup, "3")
ws.write_rich_string("A3", bold, "Note:", "\ncheck the March accruals", wrap)
ws.set_row(2, 32)

A format passed after the last fragment applies to the cell as a whole — alignment, wrapping, borders, fill — rather than to a fragment. Without text_wrap, Excel shows the newline as nothing and runs the lines together. Setting the row height avoids relying on Excel to auto-fit a wrapped row, which it does not always do for files it did not create.

Reading the arguments of write_rich_string The call write_rich_string A1, Status, red_bold, overdue, since 3 March has a cell reference, a plain fragment, a format, the fragment it applies to, and a final plain fragment. An optional last format applies to the whole cell. 1 ws.write_rich_string( 2 1 "A1", 3 2 "Status: ", 4 3 red_bold, "overdue", 5 4 " since 3 March", 6 5 wrap) 1 the cell 2 plain fragment in the default font 3 a format, then the fragment it styles 4 plain again; spaces belong to the text 5 optional last format for the whole cell

Step 3 — Check the return code

write_rich_string does not raise when it cannot write; it emits a warning, returns a negative number and leaves the cell empty. Two cases catch people out:

Python
print(ws.write_rich_string("A5", bold, "only one fragment"))     # -5
print(ws.write_rich_string("A6", "plain", "text"))               # -5
print(ws.write_rich_string("A7", "a", bold, "", "c"))            # -4

A rich string needs at least three items — two fragments with a format between them, or a format and fragment plus another fragment. A single formatted fragment is not rich text: write it with ws.write("A5", "only one fragment", bold) instead. And Excel does not allow empty fragments, which easily appear when text is built from data — a status that happens to be blank. Build the arguments in a helper that drops empty pieces and falls back to write when fewer than three remain:

Python
def write_rich(ws, cell, parts, cell_format=None):
    args = []
    for fmt, text in parts:
        if text:
            args += [fmt, text] if fmt else [text]
    if len(args) < 3:
        plain = "".join(a for a in args if isinstance(a, str))
        return ws.write(cell, plain, cell_format)
    return ws.write_rich_string(cell, *args, *([cell_format] if cell_format else []))

print(write_rich(ws, "A8", [(None, "Owner: "), (bold, ""), (None, "unassigned")]))   # 0
wb.close()

Checking the result in a report generator — or turning warnings into errors with warnings.simplefilter("error") in tests — stops blank cells from slipping into a report unnoticed.

Step 4 — Rich text in a pandas report

pandas writes plain values. Write the frame first, then overwrite the cells that need mixed formatting using the same XlsxWriter worksheet:

Python
import pandas as pd

tasks = pd.DataFrame({"task": ["Renew licence", "Quarter close", "Audit prep"],
                      "status": ["overdue", "on track", "overdue"], "days": [10, 0, 3]})
with pd.ExcelWriter("tasks.xlsx", engine="xlsxwriter") as writer:
    tasks.to_excel(writer, sheet_name="Tasks", index=False)
    book, sheet = writer.book, writer.sheets["Tasks"]
    alert = book.add_format({"bold": True, "font_color": "#B91C1C"})
    calm = book.add_format({"font_color": "#0B6157"})
    for i, row in tasks.iterrows():
        fmt = alert if row["status"] == "overdue" else calm
        sheet.write_rich_string(i + 1, 1, fmt, row["status"], f" ({row['days']} days)")
    sheet.set_column("A:B", 20)
print(pd.read_excel("tasks.xlsx")["status"].tolist())
# ['overdue (10 days)', 'on track (0 days)', 'overdue (3 days)']

Row and column numbers are zero-based in XlsxWriter, and row 0 is the header, hence i + 1. Reading the file back shows the combined text — which is also what any formula, filter or pivot table sees. If readers need to filter by status, keep the plain status in its own column and use rich text only for the presentation column.

Step 5 — Keep the formatting when openpyxl edits the file

Rich text is fragile in one specific way: openpyxl, by default, reads it as plain text, and saving the workbook writes it back plain. A later script that updates one cell with openpyxl silently strips every rich string in the workbook. Load with rich_text=True to keep it:

Python
from openpyxl import load_workbook

load_workbook("rich.xlsx").save("rich_plain.xlsx")                    # formatting lost
load_workbook("rich.xlsx", rich_text=True).save("rich_kept.xlsx")     # formatting kept
for path in ("rich_plain.xlsx", "rich_kept.xlsx"):
    value = load_workbook(path, rich_text=True)["Notes"]["A1"].value
    print(path, type(value).__name__)
# rich_plain.xlsx str
# rich_kept.xlsx CellRichText

With rich_text=True, the cell value is a CellRichText made of plain strings and TextBlock objects, each carrying its font, so you can also inspect or build rich text with openpyxl directly. Other things a default openpyxl round trip can drop are listed in What openpyxl Loses When You Save an Existing Workbook.

Opening a rich-text workbook with openpyxl Loading with the default settings reads rich cells as plain strings, and saving writes them back without their fragment formatting. Loading with rich_text True reads each cell as CellRichText with its text blocks and fonts, and saving keeps the formatting. load_workbook(path) rich cells become plain str saving strips the formatting fine for read-only analysis load_workbook(path, rich_text=True) CellRichText with TextBlocks fonts per fragment kept safe to edit and save

Step 6 — The openpyxl equivalent

When a workbook is edited with openpyxl rather than created with XlsxWriter, build rich text from the same pieces openpyxl reads: plain strings and TextBlock objects, each with an InlineFont:

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

wb2 = Workbook()
wb2.active["A1"] = CellRichText(
    "Status: ", TextBlock(InlineFont(b=True, color="B91C1C"), "overdue"), " since 3 March")
wb2.save("rich_openpyxl.xlsx")
print(load_workbook("rich_openpyxl.xlsx", rich_text=True).active["A1"].value)
# Status: overdue since 3 March

InlineFont uses the short attribute names of the file format — b for bold, i for italic, sz for size, vertAlign="superscript" for superscripts — rather than the Font names used for whole cells. The result is the same cell XlsxWriter would write, so choose the library by the job: XlsxWriter when generating a new report, openpyxl when changing an existing one.

Common pitfalls

  • Expecting fragments to be searchable separately. Excel treats the cell as one string; COUNTIF for "overdue" needs wildcards, and filters see the whole text.
  • Overusing it. Rich text inside dense tables is hard to scan. Use it for notes, labels and single status cells; use conditional formatting for columns of values.
  • Fonts that do not match the sheet. Fragments without a format use the workbook's default font, not the cell's. If the column uses a different font or size, give every fragment an explicit format so the cell does not mix typefaces.
  • Column width. Rich strings do not auto-size. Set the width, and the row height for wrapped cells.

Conclusion

write_rich_string builds a cell from fragments, each formatted by the format object before it, with an optional final format for the whole cell. Keep spaces inside fragments, use font_script for superscripts and subscripts, add text_wrap for line breaks, and check the return code — -5 for too few items and -4 for an empty fragment both leave the cell blank. When openpyxl edits the workbook later, load it with rich_text=True, or the formatting disappears on save.

Frequently asked questions

How do I make part of a cell bold with Python? Use xlsxwriter's worksheet.write_rich_string(cell, "Plain ", bold, "bold part", " plain again"). Each format object applies to the string fragment that follows it; fragments without a preceding format use the default font.

Why did write_rich_string write nothing? It returned an error code instead of raising. -5 means fewer than three fragments and formats were passed — a rich string needs at least one format between two pieces, or two formatted pieces. -4 means one of the fragments was an empty string, which Excel does not allow.

Can openpyxl read rich text? Yes, if you load the workbook with load_workbook(path, rich_text=True). The cell value is then a CellRichText made of text blocks with their fonts. With the default settings openpyxl reads plain text, and saving the workbook again removes the formatting.

How do I put a line break inside a rich string? Include "\n" in a fragment and pass a cell format with text_wrap enabled as the last argument. Without wrapping, Excel shows the text on one line.

More on cell styling & layout

More on cell styling & layout →