Adding and Customizing Sparklines in Excel Using Python

In a report where each product occupies one row of monthly figures, the reader has to compare numbers line by line to see the direction. A sparkline turns that row into a small chart that lives inside a cell — a line or a set of bars, taking no extra space, yet showing rises, falls, and outliers at a glance.

Excel has supported sparklines since the 2010 release. They are not ordinary chart objects but "sparkline groups" attached to a worksheet: a set of sparklines sharing one style. When a report is generated by a script, sparklines can be written in bulk just like cell formatting. This article uses Spire.XLS for Python to create line, column, and win/loss sparklines, and to configure colours, markers, axes, and how empty cells are handled.

Setting Up the Environment

Install Spire.XLS and import the modules:

pip install Spire.XLS

from spire.xls import *
from spire.xls.common import *

Sparklines are an Excel 2010 feature, so the workbook version has to be set explicitly; otherwise accessing SparklineGroups raises an error. Every example below starts with workbook.Version = ExcelVersion.Version2013.

Creating Sparkline Groups

Sparklines use two levels of objects: a sparkline group is created on the worksheet with SparklineGroups.AddGroup, then data ranges are bound to display cells inside that group. The example below builds a five-row product table and puts line and column sparklines in columns H and I:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.Version = ExcelVersion.Version2013
workbook.CreateEmptySheets(1)
sheet = workbook.Worksheets[0]

months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun"]
products = {
    "Alpha": [10, 12, 9, 15, 18, 21],
    "Beta": [8, 9, 7, 10, -2, 12],
    "Gamma": [20, 18, 22, 25, 23, 26],
    "Delta": [5, 7, 6, 9, 8, 11],
    "Epsilon": [15, 14, 16, 13, 17, 19],
}

sheet.Range["A1"].Value = "Product"
for i, month in enumerate(months):
    sheet.Range[1, i + 2].Value = month
sheet.Range["H1"].Value = "Trend"
sheet.Range["I1"].Value = "Bars"

for r, (name, values) in enumerate(products.items(), start=2):
    sheet.Range[r, 1].Value = name
    for c, value in enumerate(values):
        sheet.Range[r, c + 2].NumberValue = value

# Line sparklines in column H
line_group = sheet.SparklineGroups.AddGroup(SparklineType.Line)
line_sparklines = line_group.Add()
for r in range(2, 7):
    line_sparklines.Add(sheet.Range[r, 2, r, 7], sheet.Range[r, 8])

# Column sparklines in column I
column_group = sheet.SparklineGroups.AddGroup(SparklineType.Column)
column_sparklines = column_group.Add()
for r in range(2, 7):
    column_sparklines.Add(sheet.Range[r, 2, r, 7], sheet.Range[r, 9])

sheet.SetColumnWidth(8, 14)
sheet.SetColumnWidth(9, 12)

workbook.SaveToFile("SparklineDashboard.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

sheet.Range[r, 2, r, 7] is the data range from columns B to G on row r, and sheet.Range[r, 8] is the cell in column H where that sparkline sits. group.Add() creates a sparkline collection, and Add(dataRange, location) binds each row to its display cell. All sparklines in one group share a style, so configuring a group is less work than configuring cells one by one.

Setting Line Colours and Markers

Load the file just created and take the first group by index — the line group — to set the line colour, weight, and marker colours:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("SparklineDashboard.xlsx")
sheet = workbook.Worksheets[0]

# Style the line sparkline group
group = sheet.SparklineGroups[0]
group.SparklineColor = Color.get_Blue()
group.LineWeight = 1.5
group.ShowMarkers = True
group.MarkersColor = Color.get_Red()
group.ShowHighPoint = True
group.HighPointColor = Color.get_Green()
group.ShowLowPoint = True
group.LowPointColor = Color.get_Red()

workbook.SaveToFile("SparklineStyles.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The common properties:

• SparklineColor is the colour of the line or bars, and LineWeight controls its thickness.
• ShowMarkers turns on the data point markers, with MarkersColor setting their colour.
• ShowHighPoint and ShowLowPoint highlight the highest and lowest points, using HighPointColor and LowPointColor.

High and low points are hidden by default and must be enabled. That suits reports: the line stays quiet, and the extremes stand out.

Highlighting Negative Values, End Points, and the Axis

The second group is the column sparkline group. Bars make month-to-month movement easier to read, and negative values are drawn downward, so they can be coloured separately:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("SparklineDashboard.xlsx")
sheet = workbook.Worksheets[0]

# Style the column sparkline group
bars = sheet.SparklineGroups[1]
bars.SparklineColor = Color.get_Blue()
bars.ShowNegativePoint = True
bars.NegativePointColor = Color.get_Red()
bars.ShowFirstPoint = True
bars.FirstPointColor = Color.get_Orange()
bars.ShowLastPoint = True
bars.LastPointColor = Color.get_Purple()
bars.ShowHorizontalAxis = True
bars.HorizontalAxisColor = Color.get_Gray()

workbook.SaveToFile("SparklinePoints.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

ShowNegativePoint marks negative bars in red, useful for refunds, losses, or falling months. ShowFirstPoint and ShowLastPoint mark the start and the latest value, so readers can see where things stand now. ShowHorizontalAxis draws a line at zero, which makes the boundary between positive and negative bars clearer.

Handling Empty Cells

Real report data has missing months. When a sparkline meets an empty cell it can either break or connect across the gap; the group's EmptyCellsType decides which:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.Version = ExcelVersion.Version2013
workbook.CreateEmptySheets(1)
sheet = workbook.Worksheets[0]

sheet.Range["A1"].Value = "Series"
for i, month in enumerate(["Jan", "Feb", "Mar", "Apr", "May", "Jun"]):
    sheet.Range[1, i + 2].Value = month
sheet.Range["H1"].Value = "Gaps"
sheet.Range["I1"].Value = "Line"

# Two rows with missing months
rows = {
    "A": [5, None, 7, None, 9, 11],
    "B": [3, 4, None, 6, 7, 8],
    "C": [10, 9, 8, None, 6, 5],
}
for r, (name, values) in enumerate(rows.items(), start=2):
    sheet.Range[r, 1].Value = name
    for c, value in enumerate(values):
        if value is not None:
            sheet.Range[r, c + 2].NumberValue = value

# Group 1: leave a gap where a cell is empty
gaps = sheet.SparklineGroups.AddGroup(SparklineType.Line)
gaps.EmptyCellsType = SparklineEmptyCells.Gaps
gaps_sl = gaps.Add()
for r in range(2, 5):
    gaps_sl.Add(sheet.Range[r, 2, r, 7], sheet.Range[r, 8])

# Group 2: connect across empty cells
line = sheet.SparklineGroups.AddGroup(SparklineType.Line)
line.EmptyCellsType = SparklineEmptyCells.Line
line_sl = line.Add()
for r in range(2, 5):
    line_sl.Add(sheet.Range[r, 2, r, 7], sheet.Range[r, 9])

workbook.SaveToFile("SparklineEmptyCells.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

SparklineEmptyCells.Gaps leaves a break at the missing data, which makes the gap visible. SparklineEmptyCells.Line joins the points on either side, so the trend looks continuous — at the cost of hiding the omission. Which one is right depends on what the report should communicate, and both can be shown side by side when generated by script.

Win/Loss Sparklines

SparklineType.Stacked draws win/loss bars: positive values go up and negative values go down, which suits month-by-month comparisons of wins and losses or income and spending. With NegativePointColor, losing months can be tinted red:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.Version = ExcelVersion.Version2013
workbook.CreateEmptySheets(1)
sheet = workbook.Worksheets[0]

sheet.Range["A1"].Value = "Team"
for i, month in enumerate(["Jan", "Feb", "Mar", "Apr", "May", "Jun"]):
    sheet.Range[1, i + 2].Value = month
sheet.Range["H1"].Value = "Win/Loss"

results = {
    "North": [2, -1, 3, -2, 4, 1],
    "South": [-3, 2, 1, -1, 2, -4],
    "West": [1, 3, -2, 2, -1, 5],
}
for r, (name, values) in enumerate(results.items(), start=2):
    sheet.Range[r, 1].Value = name
    for c, value in enumerate(values):
        sheet.Range[r, c + 2].NumberValue = value

group = sheet.SparklineGroups.AddGroup(SparklineType.Stacked)
group.SparklineColor = Color.get_Blue()
group.NegativePointColor = Color.get_Red()
sparklines = group.Add()
for r in range(2, 5):
    sparklines.Add(sheet.Range[r, 2, r, 7], sheet.Range[r, 8])

workbook.SaveToFile("SparklineStacked.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Practical Tips

• Set the workbook version first: workbook.Version = ExcelVersion.Version2013 must run before SparklineGroups is accessed.
• Style by group: sparklines in one group share colours and marker settings, so one group per style avoids per-cell work.
• Access groups by index: sheet.SparklineGroups[0] and [1] return groups in creation order.
• Match data and location: both arguments of Add(dataRange, location) are single-row ranges; bind rows one to one.
• Choose an empty-cell policy deliberately: Gaps and Line only change display, not cell contents.
• Colour negatives separately: ShowNegativePoint with NegativePointColor makes unusual months stand out.
• Release resources: call Dispose() on each workbook when done.

Conclusion

This article showed how to create and customise sparklines in Excel from Python: build a group for line, column, or win/loss sparklines with SparklineGroups.AddGroup(), bind each row with Add(dataRange, location), control appearance with SparklineColor, LineWeight, ShowMarkers and the marker colours, highlight key values with ShowNegativePoint, ShowFirstPoint, ShowLastPoint, and ShowHorizontalAxis, and decide how gaps are drawn with EmptyCellsType. All of these settings are stored in the workbook, so a report can be generated in one pass and read as soon as it opens.