Export a Styled pandas DataFrame to Excel

df.to_excel() writes values and nothing else: no colours, no bold headers, no highlighted outliers. Most people then reach for openpyxl and loop over cells. There is a shorter path for many reports. pandas' Styler — the object behind df.style that notebooks use to render coloured tables — has its own to_excel method, and it converts a useful subset of CSS into real Excel formatting. This guide, part of Writing DataFrames to Excel with Pandas, shows what carries over, what quietly does not, and how to fill the gaps.

Prerequisites

pip install pandas openpyxl jinja2 matplotlib

Styler needs jinja2 installed, and background_gradient needs matplotlib for its colour maps. The examples use this small frame:

import pandas as pd

sales = pd.DataFrame({
    "Region": ["North", "South", "East", "West"],
    "Revenue": [120500.0, 98200.5, 143900.25, 87150.0],
    "Growth": [0.12, -0.034, 0.081, -0.12],
    "Deals": [14, 11, 17, 9],
})

Step 1 — Write a styled frame

Chain style methods on df.style and call to_excel on the result exactly as you would on the DataFrame. The styling methods take a subset of columns, and functions return CSS strings per cell.

def negative_red(value: float) -> str:
    return "color: #be185d; font-weight: bold" if value < 0 else ""

styled = (
    sales.style
         .map(negative_red, subset=["Growth"])
         .set_properties(subset=["Region"], **{"font-weight": "bold"})
         .highlight_max(subset=["Revenue"], color="#C6EFCE")
)
styled.to_excel("styled.xlsx", sheet_name="Sales", index=False, engine="openpyxl")

map (called applymap in older pandas releases) applies a function to each cell; apply passes whole columns or rows, which suits rules that compare values with each other. Open the file and the negative growth figures are red and bold, the region names bold, and the best revenue cell green — all without touching openpyxl directly.

Step 2 — Know which CSS becomes Excel formatting

The conversion recognises a specific set of CSS properties and maps them onto Excel's style model. Anything else is ignored without a warning, which is the source of most "my styling disappeared" surprises.

from openpyxl import load_workbook

sheet = load_workbook("styled.xlsx")["Sales"]
for ref in ("A2", "B4", "C3"):
    cell = sheet[ref]
    print(ref, cell.value, "bold" if cell.font.b else "", cell.fill.fgColor.rgb,
          cell.font.color.rgb if cell.font.color is not None and cell.font.color.type == "rgb" else "")

Background colour, font colour, weight and style, borders, text alignment and wrapping all survive. Layout properties with no Excel equivalent — padding, width, hover effects — do not. And one very commonly used Styler feature does not carry over at all, which is the subject of the next step.

Step 3 — Use number-format, not format()

Styler.format() changes how values are displayed as text in HTML. It does not set an Excel number format, so a column formatted as {:,.2f} in a notebook is written to Excel as a raw float with the General format. The Excel equivalent is a pseudo-CSS property, number-format, whose value is an Excel format code.

formatted = (
    sales.style
         .set_properties(subset=["Revenue"], **{"number-format": "#,##0.00"})
         .set_properties(subset=["Growth"], **{"number-format": "0.0%"})
         .map(negative_red, subset=["Growth"])
)
formatted.to_excel("formatted.xlsx", sheet_name="Sales", index=False)

check = load_workbook("formatted.xlsx")["Sales"]
print(check["B2"].number_format, check["C2"].number_format)   # #,##0.00 0.0%

Keeping the values numeric and applying a format code is always the better outcome: the numbers still sum, sort and chart in Excel. Formatting values into strings before writing produces cells that look right and behave like text. The format codes themselves are the same ones used everywhere else on this site, collected in Write Custom Number Format Codes in Excel with Python.

Step 4 — Gradients and conditional highlights

background_gradient and highlight_between compute a colour for every cell from its value, then write those as fixed fills. The result looks like conditional formatting, but it is a snapshot: the colours were decided in Python and do not change if someone edits a number in Excel.

heat = (
    sales.style
         .background_gradient(subset=["Revenue"], cmap="Greens")
         .highlight_between(subset=["Growth"], left=-1, right=0, color="#FFC7CE")
         .set_properties(subset=["Revenue"], **{"number-format": "#,##0"})
)
heat.to_excel("heat.xlsx", sheet_name="Sales", index=False)
print(load_workbook("heat.xlsx")["Sales"]["B4"].fill.fgColor.rgb)

When more than one method styles the same cell, the later CSS wins for each property. background_gradient also sets a contrasting text colour automatically on dark fills, which is usually what you want. If the workbook will be edited after it is sent, use real conditional formatting instead, so the colours follow the data — see Apply Conditional Formatting with xlsxwriter.

Step 5 — Style headers and the index

Styler styles data cells by default. The header row written by to_excel gets pandas' own bold, bordered header style, which you can change with apply_index on the column labels. It takes a function that receives the labels and returns CSS for each.

def header_style(labels: pd.Index) -> list[str]:
    return ["background-color: #4338ca; color: #ffffff; font-weight: bold" for _ in labels]

report = (
    sales.style
         .apply_index(header_style, axis="columns")
         .set_properties(subset=["Revenue"], **{"number-format": "#,##0"})
)
report.to_excel("headers.xlsx", sheet_name="Sales", index=False)
print(load_workbook("headers.xlsx")["Sales"]["A1"].fill.fgColor.rgb)

With index=True, apply_index(axis="index") styles the row labels the same way. Column widths are not something Styler controls; set them afterwards with openpyxl, or use the approach in Auto-Fit Column Widths When Writing with pandas.

Step 6 — Write several styled frames into one workbook

Styler.to_excel accepts an ExcelWriter just like DataFrame.to_excel, so a multi-sheet report can mix styled and plain sheets, and post-processing — freezing panes, setting widths — can happen on the same writer before it closes.

summary = sales.groupby(sales["Growth"] >= 0).agg(Regions=("Region", "count"), Revenue=("Revenue", "sum"))
summary.index = ["Shrinking", "Growing"]

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    heat.to_excel(writer, sheet_name="Detail", index=False)
    summary.style.set_properties(**{"number-format": "#,##0"}).to_excel(writer, sheet_name="Summary")
    detail = writer.sheets["Detail"]
    detail.freeze_panes = "A2"
    detail.column_dimensions["B"].width = 14

print(load_workbook("report.xlsx").sheetnames)

writer.sheets exposes the underlying openpyxl worksheets while the writer is open, which is the hook for anything Styler cannot express.

Step 7 — Borders, alignment and a reusable table style

A report looks finished when numbers are right-aligned, text wraps where it should and rows are separated by a light rule. Those are all CSS properties the converter understands, so they can live in one function that every export in a project applies. Keeping the palette and rules in one place is the Styler version of a house style.

BASE = {
    "border-bottom": "1px solid #cdd5e6",
    "font-family": "Calibri",
}
NUMERIC = {**BASE, "text-align": "right"}
TEXT = {**BASE, "text-align": "left", "white-space": "normal"}

def house_style(frame: pd.DataFrame, formats: dict[str, str]):
    numeric_cols = list(frame.select_dtypes("number").columns)
    text_cols = [c for c in frame.columns if c not in numeric_cols]
    styler = (
        frame.style
             .set_properties(subset=numeric_cols, **NUMERIC)
             .set_properties(subset=text_cols, **TEXT)
             .apply_index(header_style, axis="columns")
    )
    for column, code in formats.items():
        styler = styler.set_properties(subset=[column], **{"number-format": code})
    return styler

house_style(sales, {"Revenue": "#,##0", "Growth": "0.0%"}).to_excel("house.xlsx", index=False)
cell = load_workbook("house.xlsx").active["B2"]
print(cell.alignment.horizontal, cell.border.bottom.style, cell.number_format)

Each set_properties call adds to the CSS already attached to those cells, so the number formats layered on at the end combine with the alignment and borders rather than replacing them. A one-pixel CSS border becomes Excel's thin border style, which is the lightest rule Excel draws. When a style guide calls for more — banded rows, a totals row with a double top border — build it the same way with apply and a function that returns CSS per row position.

Common pitfalls

Performance and scale notes

Styler builds CSS for every cell and the Excel writer converts each distinct style, so styled exports are noticeably slower than plain to_excel — fine for summary tables of hundreds or a few thousand rows, sluggish for hundreds of thousands. For large sheets, write plain values and apply formatting per column with xlsxwriter formats, or add conditional formatting rules that cover whole ranges in a single definition. Both are covered in Building Excel Reports with XlsxWriter.

A practical split that works well: style the summary sheets of a report with Styler, where the tables are small and the styling is detailed, and write the detail sheets with plain to_excel plus a handful of column formats. Readers look closely at the summary, and the detail sheet is mostly filtered and searched, so the detailed styling budget is spent where it is seen.

Conclusion

df.style.….to_excel() is the quickest way to get colours, bold text and highlights into a pandas export. Colours, fonts, borders and alignment translate into real Excel styles; Styler.format() does not, so set Excel number formats with the number-format property and keep values numeric. Treat gradients and highlights as fixed snapshots, style headers with apply_index, and hand off to openpyxl through the writer for widths and panes. When the table is large or the colours must follow future edits, switch to xlsxwriter formats and conditional formatting.

Frequently asked questions

Why does my Styler format not show in Excel? Styler.format only changes the text shown in HTML output. Excel needs a number format code, which you set with set_properties and the number-format CSS property.

Does Styler.to_excel work with the xlsxwriter engine? Yes. Both openpyxl and xlsxwriter engines convert the supported CSS into Excel formats, so choose the engine for its other features.

Can Styler create real conditional formatting rules? No. Every colour is computed in Python and written as a fixed fill. Use xlsxwriter's conditional_format or openpyxl's conditional formatting rules for colours that react to edits.

Can I set column widths with Styler? No. Width is not converted to Excel. Set column widths through the ExcelWriter's sheets after writing, with openpyxl column_dimensions or xlsxwriter set_column.

Related

• Up one level: Writing DataFrames to Excel with Pandas — every option for getting frames into workbooks.
• Write Multiple DataFrames to One Excel File — multi-sheet writers in depth.
• Styling Excel Cells with openpyxl — full control over every style property.
• Highlight Cells Above a Threshold with openpyxl — colours that follow the data.
• Format Numbers as Percentages in Excel with Python — number formats for ratios.