# Adding and Customizing Excel Sparklines with 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:

```bash
pip install Spire.XLS
```

```python
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:

```python
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()
```

![Image description](https://dev-to-uploads.s3.us-east-2.amazonaws.com/uploads/articles/5dvgk8qb02bhsdnvjg85.png align="center")

`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:

```python
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()
```

![Image description](https://dev-to-uploads.s3.us-east-2.amazonaws.com/uploads/articles/sbioytvxdmw5c27lk1fr.png align="center")

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:

```python
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()
```

![Image description](https://dev-to-uploads.s3.us-east-2.amazonaws.com/uploads/articles/n7i5iblrsaly75ecs77e.png align="center")

`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:

```python
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:

```python
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.