Python Excel AutomationPractical guides for developers
Start learning
Guide
Automating Reporting WorkflowsDeep dive

Add a KPI Summary Block to an Excel Dashboard

Generate the top strip of a dashboard from Python: merged tiles with label, value and signed comparison, drawn by one reusable function in xlsxwriter or openpyxl.

A dashboard's top strip — four or five numbers in boxes, each with a label and a comparison — is the part everybody reads and the part that is most fiddly to generate. It is not a table, so to_excel does not help; it is a small grid of merged cells with their own formats. This guide, part of Building Multi-Sheet Excel Dashboards, builds one from a DataFrame with xlsxwriter, then shows the openpyxl equivalent for workbooks that already exist.

A KPI tile is three cells, not a table Each tile stacks a label, a large value and a signed comparison in a merged block, which is why a DataFrame export cannot produce one and a small drawing function can. anatomy of a tile label row what the number is value row large, number-formatted delta row signed, arrow by format three merged ranges per tile, repeated across the strip

Prerequisites

Bash
pip install pandas xlsxwriter openpyxl
Python
import pandas as pd

orders = pd.DataFrame({
    "Region": ["North", "South", "North", "West", "South", "North"],
    "Revenue": [24500.0, 15320.0, 31200.0, 9800.0, 18400.0, 27600.0],
    "Units": [245, 153, 312, 98, 184, 276],
    "Ordered": pd.to_datetime([
        "2026-08-04", "2026-08-11", "2026-08-19", "2026-08-22", "2026-08-27", "2026-08-30",
    ]),
})

Decide the numbers before you draw anything

A KPI tile has three parts: a label, a value and a comparison. Computing all of them first — as plain Python values — keeps the drawing code free of business logic and makes the figures testable without opening a workbook.

Python
def kpis(frame: pd.DataFrame, prior: pd.DataFrame) -> list[dict]:
    def delta(now, before):
        return None if not before else (now - before) / before

    return [
        {"label": "Revenue", "value": frame["Revenue"].sum(),
         "format": "money", "delta": delta(frame["Revenue"].sum(), prior["Revenue"].sum())},
        {"label": "Orders", "value": len(frame),
         "format": "count", "delta": delta(len(frame), len(prior))},
        {"label": "Units", "value": int(frame["Units"].sum()),
         "format": "count", "delta": delta(frame["Units"].sum(), prior["Units"].sum())},
        {"label": "Average order", "value": frame["Revenue"].mean(),
         "format": "money", "delta": delta(frame["Revenue"].mean(), prior["Revenue"].mean())},
    ]

Returning None for the delta when the prior period is zero avoids the division that would otherwise produce infinity, and lets the drawing code render a dash rather than a nonsense percentage.

Draw the tiles with xlsxwriter

One number format handles the whole comparison row Excel's three-section number format applies different text to positive, negative and zero values, so the arrow and colour follow the sign without any conditional formatting rule. Section Applies to Renders first positive values ▲ 12.4% second negative values ▼ 3.1% third zero a dash semicolons separate the sections; no rules needed

Each tile spans three columns and three rows: a label row, a value row and a comparison row. merge_range writes the value and applies the format to the whole merged block in one call, which is what makes this tolerable to write.

Python
import xlsxwriter

def write_kpi_block(book, sheet, tiles, first_row=1, first_col=1, width=3):
    label = book.add_format({
        "font_size": 10, "font_color": "#5B6780", "align": "center",
        "valign": "vcenter", "bg_color": "#F0F4FF", "top": 1, "left": 1, "right": 1,
        "border_color": "#CDD5E6",
    })
    money = book.add_format({
        "font_size": 18, "bold": True, "num_format": "#,##0", "align": "center",
        "valign": "vcenter", "bg_color": "#F0F4FF", "left": 1, "right": 1,
        "border_color": "#CDD5E6",
    })
    count = book.add_format({
        "font_size": 18, "bold": True, "num_format": "#,##0", "align": "center",
        "valign": "vcenter", "bg_color": "#F0F4FF", "left": 1, "right": 1,
        "border_color": "#CDD5E6",
    })
    up = book.add_format({
        "font_size": 10, "font_color": "#0B6157", "num_format": '▲ 0.0%;▼ 0.0%;–',
        "align": "center", "valign": "vcenter", "bg_color": "#F0F4FF",
        "bottom": 1, "left": 1, "right": 1, "border_color": "#CDD5E6",
    })

    for index, tile in enumerate(tiles):
        col = first_col + index * (width + 1)
        sheet.merge_range(first_row, col, first_row, col + width - 1, tile["label"], label)
        sheet.merge_range(
            first_row + 1, col, first_row + 1, col + width - 1,
            tile["value"], money if tile["format"] == "money" else count,
        )
        sheet.merge_range(
            first_row + 2, col, first_row + 2, col + width - 1,
            tile["delta"] if tile["delta"] is not None else "–", up,
        )
    sheet.set_row(first_row + 1, 34)

The number format '▲ 0.0%;▼ 0.0%;–' is doing real work: Excel's three-section format applies the first part to positive numbers, the second to negatives and the third to zero, so the arrow follows the sign without any conditional formatting. The details of that syntax are in Write Custom Number Format Codes in Excel with Python.

Assemble the dashboard sheet

Python
prior = orders.assign(Revenue=orders["Revenue"] * 0.86, Units=(orders["Units"] * 0.9).astype(int))

with pd.ExcelWriter("dashboard.xlsx", engine="xlsxwriter") as writer:
    by_region = orders.groupby("Region", as_index=False).agg(
        Revenue=("Revenue", "sum"), Orders=("Revenue", "size"), Units=("Units", "sum"),
    )
    by_region.to_excel(writer, sheet_name="Dashboard", index=False, startrow=6, startcol=1)
    orders.to_excel(writer, sheet_name="Detail", index=False)

    book, sheet = writer.book, writer.sheets["Dashboard"]
    sheet.hide_gridlines(2)
    sheet.set_column("A:A", 2)
    sheet.set_column("B:M", 11)
    title = book.add_format({"font_size": 16, "bold": True, "font_color": "#172033"})
    sheet.write("B1", "August 2026 — regional performance", title)
    write_kpi_block(book, sheet, kpis(orders, prior), first_row=2)

hide_gridlines(2) is the single change that makes a generated sheet look designed rather than exported. Leaving column A narrow and empty gives the block a left margin, which is the other half of the same effect.

The openpyxl version, for an existing workbook

xlsxwriter cannot open a file, so a dashboard added to a workbook that already exists has to go through openpyxl. The same tiles, expressed as merged ranges with styles:

Python
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side

book = load_workbook("existing-report.xlsx")
sheet = book["Dashboard"] if "Dashboard" in book.sheetnames else book.create_sheet("Dashboard", 0)

fill = PatternFill("solid", fgColor="F0F4FF")
edge = Side(style="thin", color="CDD5E6")
box = Border(left=edge, right=edge, top=edge, bottom=edge)

for index, tile in enumerate(kpis(orders, prior)):
    col = 2 + index * 4
    sheet.merge_cells(start_row=3, start_column=col, end_row=3, end_column=col + 2)
    sheet.merge_cells(start_row=4, start_column=col, end_row=4, end_column=col + 2)
    sheet.cell(row=3, column=col, value=tile["label"])
    value_cell = sheet.cell(row=4, column=col, value=tile["value"])
    value_cell.number_format = "#,##0"
    value_cell.font = Font(size=18, bold=True)
    for row in (3, 4):
        for offset in range(3):
            cell = sheet.cell(row=row, column=col + offset)
            cell.fill = fill
            cell.border = box
            cell.alignment = Alignment(horizontal="center", vertical="center")

sheet.sheet_view.showGridLines = False
book.save("existing-report-with-dashboard.xlsx")

The inner loop applying the border to every cell of the merged range is the part people leave out. Only the top-left cell holds the value, but a merged range with a border on one cell draws one edge, not a box.

Common pitfalls

SymptomCauseFix
The KPI box has a partial borderBorder applied only to the anchor cellApply it to every cell in the merged range
Values overflow the tileRow height left at the defaultset_row(..., 34) or set row_dimensions[...].height
Percentages show as 1240%A proportion written where a percentage was formattedWrite 0.124, and let the number format add the sign
Merged cells lose their fillFill applied before merging in openpyxlMerge first, then style every cell in the range
The block shifts when a column is insertedAbsolute column positionsCompute positions from one first_col variable
A delta shows as infPrior period was zeroReturn None and render a dash

Keeping the block aligned with the table below

A dashboard reads badly when the tiles and the table beneath them use different column widths. The fix is to pick one grid — twelve narrow columns, say — and express both in terms of it: each tile spans three, the table below spans all twelve. Because the tile-drawing function already takes first_col and width, changing the layout is two arguments rather than a rewrite.

Python
write_kpi_block(book, sheet, tiles[:4], first_row=2, first_col=1, width=3)   # 4 across
write_kpi_block(book, sheet, tiles[:3], first_row=2, first_col=1, width=4)   # 3 across

Fixing the widths once with set_column("B:M", 11) and then never setting individual widths keeps that grid intact. It is also what makes the sheet print sensibly, since a uniform grid fits a page width predictably — the setup covered in Set the Print Area and Page Setup with openpyxl.

Testing the block without opening Excel

Because the tiles are computed as data before anything is drawn, the numbers can be asserted in a test that never touches a workbook — which is the main practical reason to keep that separation.

Python
def test_kpis_handle_an_empty_prior_period():
    frame = pd.DataFrame({"Revenue": [100.0, 200.0], "Units": [1, 2]})
    empty = frame.iloc[0:0]
    tiles = kpis(frame, empty)
    assert tiles[0]["value"] == 300.0
    assert tiles[0]["delta"] is None       # no prior period, no growth figure

The empty-prior case is the one worth testing first, because it is what happens on the first run of a new report and it is where a division by zero would otherwise surface in front of the audience. Once the values are covered, a second test can open the generated workbook and assert on a handful of cells — the approach in Test Excel Output with pytest.

Performance and scale

Where the tile numbers come from Computing each headline figure with its own pass over the frame scans the data once per tile, while a single aggregation produces all of them in one pass. a pass per tile 8 scans for 8 tiles re-reads the archive grows with the strip one aggregation 1 scan total prior period read once constant in tile count aggregate the drawing is free; the scanning is not

A KPI block is a few dozen cells, so its own cost is nothing. What does matter is where the numbers come from: computing each tile with a separate pass over a large frame means one scan per tile, and computing them from a single grouped aggregation means one scan in total.

Python
# One pass over the frame, all the headline numbers
totals = orders[["Revenue", "Units"]].sum()
summary = {
    "Revenue": float(totals["Revenue"]),
    "Units": int(totals["Units"]),
    "Orders": len(orders),
    "Average order": float(orders["Revenue"].mean()),
}

The same argument applies to the prior period: read it once and hold it, rather than re-reading the archive workbook for each comparison. On a dashboard with eight tiles that is the difference between one read and eight of the same file.

Conclusion

Compute the tiles as data first, then draw them from a single function that takes a starting column and a width — that keeps the layout adjustable and the numbers testable. Use a three-section number format so the arrow follows the sign without conditional formatting, apply borders to every cell of each merged range, and hide the gridlines. For a workbook that already exists, the same structure goes through openpyxl with merged ranges and style objects.

Frequently asked questions

Should the KPI values be formulas or literals? Write literals when the workbook is a snapshot that will be emailed and archived, and formulas when recipients will filter or edit the detail sheets and expect the headline to follow. Mixing them in one workbook is the arrangement that confuses people.

How do I show a change against the previous period? Compute both values in pandas and write the delta as its own cell, formatted with a custom number format that shows an up or down arrow. Excel's conditional formatting icon sets are the alternative and are harder to control from code.

Why do my merged KPI cells lose their border? Only the top-left cell of a merged range carries the value, but every cell in the range needs its own border to draw a complete box. Loop over the range and apply the border to each cell, or use xlsxwriter's merge_range, which handles it.

How wide should the KPI block be? Match it to the table below so the columns line up. Setting each tile to span two or three columns of a twelve-column grid keeps the dashboard aligned however many tiles there are.