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

Choose and Customise Excel Table Styles with Python

Pick the right look for an Excel table from Python: the 60 built-in styles and how their names work, the four banding and emphasis switches, styles in openpyxl and XlsxWriter, a custom company table style written with openpyxl, why typos in style names pass silently, and why LibreOffice and PDF conversions show tables unstyled unless you add direct formatting.
Tables, filters & outlinesCell styling & layout

An Excel table brings filter buttons, structured references and a growing range — and a style that colours the header, stripes the rows and can bold the first and last columns. From Python the style is a single name plus four switches, which makes it easy to pick one, easy to mistype one, and easy to miss that some applications ignore table styles altogether. This page goes through the built-in style families and the switches, sets them from openpyxl and XlsxWriter, writes a custom company table style, adds a check for style names, and shows how to make a table look right when the workbook is opened in LibreOffice or converted to PDF. Creating the table itself is covered in Create an Excel Table with Python; this page is about how it looks. It belongs to Creating Excel Tables and Autofilters with Python.

Prerequisites

Bash
pip install openpyxl xlsxwriter pillow

The examples use a small regional sales table with a header row and four data rows (rows in the code). Step 6 uses LibreOffice (soffice) and Pillow to render the result and look at its colours; that part is optional.

Step 1 — How the built-in styles are named

Excel ships 60 table styles in three families, and every one has an internal name of the form TableStyle<Family><Number>:

Python
from collections import Counter
from openpyxl.worksheet.table import TABLESTYLES

families = Counter(name.rstrip("0123456789").replace("TableStyle", "") for name in TABLESTYLES)
print(dict(families))
print(TABLESTYLES[0], "…", TABLESTYLES[-1])

There are 21 Light styles, 28 Medium and 11 Dark. Within each family the numbers run in groups of seven colours in the order of the default Office theme: the first style of each group is neutral (black or grey), and the next six use the theme's accent colours (the last four Dark styles break the pattern with two-colour combinations). TableStyleMedium9 — the blue banded style Excel picks by default — is the second style of the second Medium group. Because the colours come from the workbook's theme, the same style name looks different in a workbook with a different theme. Excel shows the styles in a gallery under Table Design → Table Styles, which is the quickest way to choose one: hover to preview, then note the name from its tooltip ("Medium 9" means TableStyleMedium9).

The three families of built-in table styles Light styles have thin lines and pale or no fill, Medium styles have a coloured header and banded rows, and Dark styles have a dark body with strong contrast. Each family cycles through a neutral style and six theme accent colours. Light 1–21 thin rules, little fill prints cleanly in mono good for dense numbers Medium 1–28 coloured header row banded body rows Medium9 is Excel's default Dark 1–11 dark, saturated fill high contrast headline sparing use in reports

Step 2 — The four switches

A style defines formats for several table elements — header row, total row, first and last column, row stripes, column stripes — and four switches decide which of them apply:

Python
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active
ws.title = "Sales"
for r in rows:
    ws.append(r)
ws.add_table(Table(displayName="Sales", ref="A1:D5", tableStyleInfo=TableStyleInfo(
    name="TableStyleMedium2",
    showRowStripes=True,       # alternate row shading
    showColumnStripes=False,   # alternate column shading
    showFirstColumn=True,      # emphasise the label column
    showLastColumn=True,       # emphasise the total column
)))
wb.save("table_switches.xlsx")
print(ws.tables["Sales"].tableStyleInfo.name, "first/last column on")

For a report with labels in the first column and a total in the last, turning on first and last column emphasis makes both stand out; for a wide table of similar measures, column stripes help the eye down each column. Row stripes are the usual default. The header row itself is part of the table definition (headerRowCount), not a switch, and the total row is controlled by totalsRowCount — adding one is covered in Add a Total Row to an Excel Table.

Step 3 — The same in XlsxWriter

XlsxWriter writes the same table XML but uses Excel's display names and lower-case switch names:

Python
import xlsxwriter

book = xlsxwriter.Workbook("table_xlsxwriter.xlsx")
sheet = book.add_worksheet("Sales")
sheet.add_table("A1:D5", {
    "name": "Sales",
    "style": "Table Style Light 11",
    "banded_rows": False,
    "banded_columns": True,
    "first_column": True,
    "last_column": True,
    "columns": [{"header": h} for h in rows[0]],
    "data": rows[1:],
})
plain = book.add_worksheet("Plain")
plain.add_table("A1:D5", {"name": "PlainSales", "style": None,
                          "columns": [{"header": h} for h in rows[0]], "data": rows[1:]})
book.close()
print("written with Table Style Light 11 and with no style")

"Table Style Light 11" is written to the file as TableStyleLight11. "style": None gives a table with filter buttons and structured references but no colours at all — useful when you want to apply your own cell formatting. XlsxWriter has no API for custom table styles; for those, use openpyxl as in the next step.

Step 4 — A custom company table style

A custom table style is a named set of differential formats (the same partial formats conditional formatting uses), one per table element. openpyxl can write one, though only through private attributes of the workbook — _differential_styles and _table_styles — so pin your openpyxl version and keep this code in one function:

Python
from openpyxl.styles import Border, Font, PatternFill, Side
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.styles.table import TableStyle, TableStyleElement

def add_table_style(wb, name, elements):
    """elements: {"headerRow": DifferentialStyle, ...}; returns the style name."""
    parts = []
    for kind, dxf in elements.items():
        wb._differential_styles.append(dxf)
        parts.append(TableStyleElement(type=kind, dxfId=len(wb._differential_styles.dxf) - 1))
    wb._table_styles.tableStyle.append(TableStyle(name=name, pivot=False, count=len(parts), tableStyleElement=parts))
    return name

wb = Workbook()
ws = wb.active
ws.title = "Sales"
for r in rows:
    ws.append(r)
brand = add_table_style(wb, "BrandTable", {
    "wholeTable": DifferentialStyle(border=Border(bottom=Side("thin", color="C7D2FE"))),
    "headerRow": DifferentialStyle(font=Font(bold=True, color="FFFFFF"), fill=PatternFill("solid", bgColor="4338CA")),
    "firstRowStripe": DifferentialStyle(fill=PatternFill("solid", bgColor="EEF2FF")),
    "lastColumn": DifferentialStyle(font=Font(bold=True)),
})
ws.add_table(Table(displayName="Sales", ref="A1:D5",
                   tableStyleInfo=TableStyleInfo(name=brand, showRowStripes=True, showLastColumn=True)))
wb.save("table_custom.xlsx")

from openpyxl import load_workbook
back = load_workbook("table_custom.xlsx")
print([s.name for s in back._table_styles.tableStyle], "→", back["Sales"].tables["Sales"].tableStyleInfo.name)

The workbook's styles.xml now holds a BrandTable style with four elements, and the table refers to it by name; reloading the file shows the style survives a round trip through openpyxl. In Excel it appears under Custom at the top of the table style gallery, so colleagues can apply it to their own tables. Note that in differential formats a solid fill's colour goes in bgColor, not fgColor — the same rule as for conditional formatting in Highlight Cells Above a Threshold with openpyxl.

How a custom table style is put together Each table element such as the header row or the first row stripe gets a differential format. The formats are stored in the workbook's dxf list, a table style lists the elements with their dxf ids, and the table refers to the style by name. Differential formats font, fill, border per element Table style BrandTable: element → dxf id The table tableStyleInfo name Excel gallery listed under Custom

Step 5 — Catch misspelt style names

openpyxl and Excel accept any string as a style name. A typo such as TableStyleMedium29 (there are only 28) is written without complaint, and the table then appears unstyled. A small check before saving catches it:

Python
def check_table_styles(wb):
    known = set(TABLESTYLES) | {s.name for s in wb._table_styles.tableStyle}
    problems = []
    for sheet in wb.worksheets:
        for table in sheet.tables.values():
            name = table.tableStyleInfo.name if table.tableStyleInfo else None
            if name is not None and name not in known:
                problems.append(f"{sheet.title}!{table.displayName}: unknown style {name!r}")
    return problems

ws.tables["Sales"].tableStyleInfo.name = "TableStyleMedium29"
print(check_table_styles(wb))
ws.tables["Sales"].tableStyleInfo.name = brand
print(check_table_styles(wb))

The check accepts the 60 built-in names plus any custom styles defined in the workbook, and reports the sheet and table of anything else. Running it in a test of the report generator stops a silently plain table reaching readers.

Step 6 — Tables in LibreOffice and PDF exports

Table styles are drawn by Excel. LibreOffice (tested with version 26.2) keeps the table's range and filter buttons but does not draw table styles — neither built-in ones like Medium9 nor custom ones — so the same file opened there, or converted to PDF with LibreOffice on a server, shows a plain grid. If that matters, add the look as ordinary cell formatting on top of the table. Excel lets direct cell formatting take precedence over the table style, and LibreOffice draws it as normal:

Python
from openpyxl.styles import Font, PatternFill

def bake_table_look(ws, table, header="4338CA", stripe="EEF2FF"):
    """Copy a simple header + row-stripe look into direct cell formatting."""
    (first, *body) = ws[table.ref]
    for cell in first:
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = PatternFill("solid", fgColor=header)
    for i, row in enumerate(body):
        if i % 2 == 0:
            for cell in row:
                cell.fill = PatternFill("solid", fgColor=stripe)

bake_table_look(ws, ws.tables["Sales"])
wb.save("table_baked.xlsx")
print("header and stripes copied into", ws.tables["Sales"].ref)

Note that ordinary cell fills use fgColor — it is only differential formats that use bgColor. Rendering both files shows the difference:

Python
import subprocess
from PIL import Image

def colour_share(xlsx, rgb):
    subprocess.run(["soffice", "--headless", "--convert-to", "png", xlsx], check=True, capture_output=True)
    img = Image.open(xlsx.replace(".xlsx", ".png")).convert("RGB")
    counts = img.getcolors(img.width * img.height)
    near = sum(n for n, p in counts if all(abs(a - b) < 12 for a, b in zip(p, rgb)))
    return near / (img.width * img.height)

indigo = (0x43, 0x38, 0xCA)
for f in ("table_custom.xlsx", "table_baked.xlsx"):
    print(f"{f:18} indigo header pixels: {colour_share(f, indigo):.2%}")

The custom-style file renders with no indigo at all in LibreOffice, while the baked file shows the indigo header. The baked formatting is fixed to the rows it was written to — it does not move when rows are sorted or added in Excel the way a table style does — so use it for finished reports and rely on the table style for workbooks people will keep editing. Striping a plain range without a table is covered in Add Alternating Row Colours with openpyxl.

Indigo header pixels in the LibreOffice rendering Share of the rendered page in the header colour. The table with the custom style shows none, because LibreOffice does not draw table styles. The table with the look baked into cell formatting shows the indigo header. custom table style 0% · style not drawn baked cell formatting header drawn

Common pitfalls

  • A misspelt style name. It is written without error and the table appears plain; validate names.
  • Expecting the same colours in every workbook. Built-in styles use theme colours.
  • fgColor in a table style. Differential formats take a solid fill's colour from bgColor.
  • Relying on table styles in LibreOffice or PDF exports. They are not drawn; bake the look into cells.
  • Baked formatting in a table people sort. Direct fills stay on their rows; keep the table style for live workbooks.

Conclusion

An Excel table's look is a style name from the Light, Medium or Dark families plus four switches for row stripes, column stripes and first and last column emphasis. openpyxl sets them through TableStyleInfo, XlsxWriter through style, banded_rows and friends, and openpyxl can also define a custom company style from differential formats. Validate style names, since typos pass silently, and when a workbook will be opened in LibreOffice or converted to PDF, copy the look into direct cell formatting because table styles are only drawn by Excel.

Frequently asked questions

How do I set the style of an Excel table in openpyxl? Pass a TableStyleInfo to the Table with a name such as TableStyleMedium9 and the switches showRowStripes, showColumnStripes, showFirstColumn and showLastColumn.

What table style names does Excel have? Sixty built-in styles: TableStyleLight1 to Light21, TableStyleMedium1 to Medium28 and TableStyleDark1 to Dark11. openpyxl lists them in the TABLESTYLES tuple of its worksheet.table module.

Can I create a custom table style with Python? Yes, with openpyxl, by adding differential formats and a TableStyle to the workbook through its private _differential_styles and _table_styles attributes. XlsxWriter does not support custom table styles.

Why is my Excel table unstyled in LibreOffice? LibreOffice does not draw Excel table styles. Copy the header and stripe formatting into the cells directly if the file will be opened there or converted to PDF with it.

More on tables, filters & outlines

More on tables, filters & outlines →