Python Excel AutomationPractical guides for developers
Start learning
Guide
Formatting And Charting Excel Reports With PythonDeep dive

Add Sparklines to an Excel Report with xlsxwriter

Put a trend in every row: xlsxwriter sparklines — line, column and win/loss types, high and low markers, shared axes across rows, styling, and the openpyxl caveat.

A table of twelve monthly columns tells a reader what happened; a sparkline in a thirteenth column tells them the shape of it at a glance. Sparklines are tiny in-cell charts, and xlsxwriter writes them directly, which makes them one of the cheapest additions to a generated report — a single call per row, no images, no chart objects, no extra file size to speak of. This guide adds all three sparkline types, shares scales so rows are comparable, and covers the openpyxl limitation that decides where in your pipeline they belong. It extends Building Excel Reports with xlsxwriter.

The three sparkline types on the same row of data A line sparkline shows the trend's shape, a column sparkline emphasises each period's magnitude, and a win-loss sparkline reduces every period to above or below zero. Same twelve numbers, three questions answered line what shape is the trend? column how big was each period? win / loss which periods missed target?

Prerequisites

Bash
pip install xlsxwriter pandas

Sparklines are a write-time feature, so the workbook must be created by xlsxwriter — you cannot add them to an existing file with openpyxl.

Add a line sparkline per row

Python
"""A monthly table with a trend column."""
import xlsxwriter

months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun"]
rows = [
    ("North", [120, 138, 131, 152, 149, 168]),
    ("South", [98, 94, 102, 88, 91, 84]),
    ("East",  [61, 66, 72, 70, 79, 88]),
]

with xlsxwriter.Workbook("trends.xlsx") as book:
    ws = book.add_worksheet("Trends")
    bold = book.add_format({"bold": True})

    ws.write_row(0, 0, ["Region"] + months + ["Trend"], bold)
    for r, (region, values) in enumerate(rows, start=1):
        ws.write(r, 0, region)
        ws.write_row(r, 1, values)
        ws.add_sparkline(r, len(months) + 1, {
            "range": f"Trends!B{r + 1}:G{r + 1}",
            "type": "line",
            "markers": True,
            "high_point": True,
            "low_point": True,
        })

    ws.set_column(0, 0, 14)
    ws.set_column(len(months) + 1, len(months) + 1, 16)
    ws.set_row(0, 20)

range is a worksheet-qualified A1 range as a string, so the row number is one greater than the zero-based r used for add_sparkline. Getting that off by one is the usual reason a sparkline shows the wrong row's shape.

Share a scale so rows are comparable

By default each sparkline scales to its own values, which makes a region with a range of 60–90 look identical to one ranging 900–1400. If the rows are meant to be compared, share the axis:

Python
ws.add_sparkline(r, 7, {
    "range": f"Trends!B{r + 1}:G{r + 1}",
    "type": "line",
    "max": "group",
    "min": "group",
})

"group" means "the largest and smallest across every sparkline in this group", so a flat row reads as flat rather than as dramatic. Use a fixed number instead — "max": 200 — when successive months' reports must be comparable with each other, not just internally.

Column and win/loss types

A column sparkline emphasises magnitude; a win/loss sparkline throws magnitude away and shows only direction, which is exactly right for variance against target:

Python
ws.add_sparkline(r, 8, {
    "range": f"Trends!B{r + 1}:G{r + 1}",
    "type": "column",
    "style": 12,
})

ws.add_sparkline(r, 9, {
    "range": f"Variance!B{r + 1}:G{r + 1}",     # values above/below zero
    "type": "win_loss",
    "negative_points": True,
})

For win/loss to mean anything, the underlying cells must be signed — the difference from target, not the raw figure. Compute that column in pandas before writing.

Independent scaling versus a shared group scale With per-row scaling a small region's minor wobble looks identical to a large region's real growth, while a shared group scale renders both against the same axis. each row scaled alone max/min = "group" North: 120 to 168 East: 61 to 88, on the same axis

Style them to match the report

Python
ws.add_sparkline(r, 7, {
    "range": f"Trends!B{r + 1}:G{r + 1}",
    "type": "line",
    "series_color": "#5B5CF0",
    "high_point": True,
    "first_point": True,
    "last_point": True,
    "weight": 1.25,
})

Marking the first and last points gives a reader the endpoints without a numeric axis, which is most of what a sparkline is for. Keep the palette the same as the rest of the workbook — the shared-theme approach in Apply a reusable style theme across an Excel report.

Generate from a DataFrame

In a real report the monthly columns come from a pivot, so write the frame and add one sparkline per data row:

Python
import pandas as pd

pivot = df.pivot_table(index="region", columns="month", values="revenue", aggfunc="sum")
pivot = pivot.reindex(columns=months).fillna(0).round(0)

with pd.ExcelWriter("trends.xlsx", engine="xlsxwriter") as writer:
    pivot.to_excel(writer, sheet_name="Trends")
    ws = writer.sheets["Trends"]
    last_col = len(pivot.columns)
    for r in range(1, len(pivot) + 1):
        ws.add_sparkline(r, last_col + 1, {
            "range": f"Trends!B{r + 1}:{chr(65 + last_col)}{r + 1}",
            "type": "line", "max": "group", "min": "group", "markers": True,
        })
    ws.set_column(last_col + 1, last_col + 1, 16)

Note that pivot.to_excel writes the index, so the data starts in column B — which is why the range begins there. The pivot itself is covered in Create a pivot table from Excel with pandas.

Pair each sparkline with one number

A trend line answers "which way is this going?" but not "by how much?". The combination that works in practice is a sparkline plus a single change figure beside it, so the reader gets direction and magnitude in one glance:

Python
import pandas as pd

def change_columns(values: list[float]) -> tuple[float, float]:
    """Absolute and percentage change from the first period to the last."""
    first, last = values[0], values[-1]
    delta = last - first
    pct = (delta / first) if first else 0.0
    return delta, pct

with xlsxwriter.Workbook("trends.xlsx") as book:
    ws = book.add_worksheet("Trends")
    up = book.add_format({"num_format": "+#,##0;-#,##0", "font_color": "#0B6157"})
    down = book.add_format({"num_format": "+#,##0;-#,##0", "font_color": "#9C0006"})
    pct_fmt = book.add_format({"num_format": "+0.0%;-0.0%"})

    for r, (region, values) in enumerate(rows, start=1):
        delta, pct = change_columns(values)
        ws.write(r, 0, region)
        ws.write_row(r, 1, values)
        ws.add_sparkline(r, 7, {"range": f"Trends!B{r + 1}:G{r + 1}",
                                 "type": "line", "max": "group", "min": "group"})
        ws.write_number(r, 8, delta, up if delta >= 0 else down)
        ws.write_number(r, 9, pct, pct_fmt)

The +#,##0;-#,##0 format shows the sign explicitly, which stops a reader having to work out whether −4 is a fall or a negative balance. Colouring the two cases differently is the smallest possible conditional format and needs no rule at all.

A row that answers both questions at once The monthly figures give detail, the sparkline gives shape, and the signed change column gives magnitude, so no reader has to compute anything. Detail, shape and magnitude in one row 120 138 131 152 168 +48 +40.0% the numbers the shape the change in context Each column answers a question the previous one raises

Group them so the whole column shares one definition

Adding a sparkline per row works and is easy to follow, but Excel also supports a group: one definition covering a block of rows, which is what the UI creates when you drag a sparkline down. xlsxwriter expresses it by passing lists instead of single ranges:

Python
ws.add_sparkline(1, 7, {
    "location": [f"H{r}" for r in range(2, len(rows) + 2)],
    "range":    [f"Trends!B{r}:G{r}" for r in range(2, len(rows) + 2)],
    "type": "line",
    "markers": True,
    "max": "group",
    "min": "group",
})

location and range must be the same length and in the same order — each location takes its data from the corresponding range. A group is slightly smaller in the file and, more usefully, appears in Excel as one object a reader can restyle in a single action rather than cell by cell. Keep the per-row form when the ranges are irregular, and the group form when a whole column follows one rule.

Common pitfalls and gotchas

  • Off-by-one ranges. add_sparkline takes zero-based row and column; the range string is one-based A1 notation.
  • Invisible sparklines. They fill the cell, so a default row height of 15 points makes a line barely visible. Set 18–24.
  • Misleading comparisons. Without max/min set to "group", rows scale independently.
  • Round-tripping through openpyxl. Re-saving can drop them; generate the final file with xlsxwriter last.
  • Empty cells in the range. They break the line; fill with zero or use show_hidden deliberately.

Performance and scale notes

Sparklines cost almost nothing — they are a small XML extension per group, not an image or a chart object — so a report with hundreds of them stays the same size as one without. The real limit is the reader's: a sheet with a thousand sparklines is slow to scroll in Excel and pointless to look at. Group rows into a summary of a few dozen and put the detail on another sheet. If you need trend visuals in a workbook that must be edited afterwards with openpyxl, generate a small image per row instead, as in Embed a matplotlib chart in an Excel report — heavier, but it survives a round trip.

Conclusion

One add_sparkline call per row turns a wide table of monthly figures into something a reader understands without reading a number. Choose line for shape, column for magnitude and win_loss for variance against target, share the scale with "group" whenever rows are meant to be compared, and give the rows enough height to see. Because openpyxl cannot add or reliably preserve them, make xlsxwriter the last step that writes the file.

Frequently asked questions

Can openpyxl add sparklines? Not through a supported API. Sparklines are an Excel 2010 extension stored outside the core worksheet schema, so xlsxwriter is the practical route — which means creating a new workbook rather than editing an existing one.

What is the difference between the three types?line shows the shape of a trend, column emphasises the size of each period, and win_loss reduces every value to above or below zero — ideal for variance against target.

Why do two rows with very different magnitudes look identical? Each sparkline scales to its own values by default. Set max="group" and min="group" so a set of rows shares one scale and can be compared honestly.

How do I make them bigger? A sparkline fills its cell, so increase the row height and column width. Around 18 to 24 points of row height reads well for a line sparkline.

Do sparklines survive being read back by openpyxl? Re-saving such a file with openpyxl can drop them, because they live in an extension part it does not preserve. Generate the workbook in one pass with xlsxwriter and do not round-trip it.