Skip to main content

How to Add Sparklines to Excel in Python

 Sales reports, inventory sheets, and operational dashboards often contain data that changes over time. Although the values can be compared directly, it may still be difficult to identify growth, decline, or unusual fluctuations across many rows.

A standard Excel chart can make trends easier to understand, but creating a separate chart for every product, department, or project would take up too much space.

Excel sparklines provide a more compact alternative. A sparkline is a small chart displayed inside a single cell, allowing readers to view the trend of a data series without changing the overall worksheet layout.

This article demonstrates how to use Python to:

  • Create an Excel workbook containing monthly sales data
  • Add line sparklines to multiple rows
  • Change sparklines to column or win/loss types
  • Configure sparkline and high-point colors
  • Add sparklines to an existing Excel workbook


What Are Excel Sparklines?

Unlike standard Excel charts, a sparkline is usually displayed inside one cell.

For example, consider the following monthly sales data:

ProductJanFebMarAprTrend
Product A120135128160Sparkline
Product B95110108126Sparkline

A sparkline can be placed in the cell next to each row, allowing readers to quickly identify whether the values are increasing, decreasing, or fluctuating.

Excel supports three common sparkline types:

  • Line sparklines show continuous changes over time, such as sales, website visits, or revenue.
  • Column sparklines make it easier to compare the relative size of individual values.
  • Win/loss sparklines emphasize positive and negative results rather than exact values.

Install the Excel Library

The examples below use Spire.XLS for Python to create and edit Excel workbooks.

Install the package with pip:

pip install Spire.XLS

Then import the required modules:

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

Create Sample Excel Data in Python

The following example creates a worksheet containing monthly sales data for four products.

The data is stored in the range A1:N5:

  • Column A contains product names
  • Columns B through M contain sales data from January to December
  • Column N is reserved for sparklines
from spire.xls import *
from spire.xls.common import *

# Create a workbook
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]
sheet.Name = "Monthly Sales"

# Define the headers
headers = [
    "Product",
    "Jan", "Feb", "Mar", "Apr", "May", "Jun",
    "Jul", "Aug", "Sep", "Oct", "Nov", "Dec",
    "Trend"
]

# Define monthly sales data
sales_data = [
    ["Product A", 120, 135, 128, 160, 172, 168, 190, 205, 198, 220, 235, 248],
    ["Product B", 95, 108, 115, 110, 126, 140, 138, 150, 163, 158, 172, 185],
    ["Product C", 180, 172, 165, 170, 158, 150, 162, 155, 148, 142, 150, 138],
    ["Product D", 75, 92, 88, 105, 98, 115, 121, 118, 132, 140, 137, 152]
]

# Write the headers
for column_index, header in enumerate(headers, start=1):
    sheet.Range[1, column_index].Text = header

# Write the product names and sales values
for row_index, row_data in enumerate(sales_data, start=2):
    sheet.Range[row_index, 1].Text = row_data[0]

    for column_index, value in enumerate(row_data[1:], start=2):
        sheet.Range[row_index, column_index].NumberValue = value

# Make the header row bold
sheet.Range["A1:N1"].Style.Font.IsBold = True

# Automatically adjust the column widths
sheet.Range["A1:N5"].AutoFitColumns()

At this stage, the worksheet contains the complete sales data, but no sparklines have been added yet.

Add Line Sparklines to Excel in Python

To add sparklines, first create a sparkline group and specify its chart type.

The following code adds line sparklines to cells N2:N5. Each sparkline uses the 12 monthly values from the corresponding row.

# Create a sparkline group
sparkline_group = sheet.SparklineGroups.AddGroup()

# Set the sparkline type to line
sparkline_group.SparklineType = SparklineType.Line

# Set the sparkline color
sparkline_group.SparklineColor = Color.get_DarkBlue()

# Set the color of the highest point
sparkline_group.HighPointColor = Color.get_Red()

# Create a sparkline collection
sparklines = sparkline_group.Add()

# Add one sparkline for each product row
for row in range(2, 6):
    data_range = sheet.Range[f"B{row}:M{row}"]
    location = sheet.Range[f"N{row}"]

    sparklines.Add(data_range, location)

The main operations are:

  • AddGroup() creates a new sparkline group.
  • SparklineType.Line specifies a line sparkline.
  • SparklineColor sets the main sparkline color.
  • HighPointColor sets the color of the highest value.
  • sparklines.Add() defines the source data and destination cell.

Keeping similar sparklines in one group makes it easier to apply the same type and formatting to all of them.

Save the Excel Workbook

After adding the sparklines, save the workbook as an XLSX file:

# Save the workbook
workbook.SaveToFile(
    "sales_trends_with_sparklines.xlsx",
    ExcelVersion.Version2016
)

# Release resources
workbook.Dispose()

The output file is:

sales_trends_with_sparklines.xlsx

When the file is opened in Excel, column N displays a monthly trend sparkline for each product.

Complete Code Example

The following code combines workbook creation, data entry, sparkline generation, and file saving.

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

# Create a workbook
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]
sheet.Name = "Monthly Sales"

# Define the headers
headers = [
    "Product",
    "Jan", "Feb", "Mar", "Apr", "May", "Jun",
    "Jul", "Aug", "Sep", "Oct", "Nov", "Dec",
    "Trend"
]

# Define monthly sales data
sales_data = [
    ["Product A", 120, 135, 128, 160, 172, 168, 190, 205, 198, 220, 235, 248],
    ["Product B", 95, 108, 115, 110, 126, 140, 138, 150, 163, 158, 172, 185],
    ["Product C", 180, 172, 165, 170, 158, 150, 162, 155, 148, 142, 150, 138],
    ["Product D", 75, 92, 88, 105, 98, 115, 121, 118, 132, 140, 137, 152]
]

# Write the headers
for column_index, header in enumerate(headers, start=1):
    sheet.Range[1, column_index].Text = header

# Write the data
for row_index, row_data in enumerate(sales_data, start=2):
    # Write the product name
    sheet.Range[row_index, 1].Text = row_data[0]

    # Write the monthly sales values
    for column_index, value in enumerate(row_data[1:], start=2):
        sheet.Range[row_index, column_index].NumberValue = value

# Format the header row
sheet.Range["A1:N1"].Style.Font.IsBold = True

# Create a line sparkline group
sparkline_group = sheet.SparklineGroups.AddGroup()
sparkline_group.SparklineType = SparklineType.Line

# Configure sparkline colors
sparkline_group.SparklineColor = Color.get_DarkBlue()
sparkline_group.HighPointColor = Color.get_Red()

# Create a sparkline collection
sparklines = sparkline_group.Add()

# Add sparklines to cells N2:N5
for row in range(2, 6):
    sparklines.Add(
        sheet.Range[f"B{row}:M{row}"],
        sheet.Range[f"N{row}"]
    )

# Automatically adjust the column widths
sheet.Range["A1:N5"].AutoFitColumns()

# Save the result
workbook.SaveToFile(
    "sales_trends_with_sparklines.xlsx",
    ExcelVersion.Version2016
)

# Release resources
workbook.Dispose()

Add Column Sparklines

Column sparklines are useful when comparing the relative size of individual values is more important than showing a continuous trend.

Change this line:

sparkline_group.SparklineType = SparklineType.Line

to:

sparkline_group.SparklineType = SparklineType.Column

The rest of the code can remain unchanged.

Column sparklines work well for data such as:

  • Monthly order volumes
  • Inventory levels
  • Department expenses
  • Completion counts by stage

Add Win/Loss Sparklines

A win/loss sparkline is useful when the data represents positive and negative outcomes.

Set the sparkline type as follows:

sparkline_group.SparklineType = SparklineType.Stacked

For example, the following values may represent the difference between actual results and monthly targets:

performance_data = [
    12, -5, 8, 15, -3, -10,
    6, 9, -4, 11, 7, -2
]

In a win/loss sparkline, positive and negative values are shown in opposite directions. The chart emphasizes whether a result is above or below zero rather than the exact difference between values.

Typical use cases include:

  • Monthly profit and loss
  • Results above or below a target
  • Month-over-month growth and decline
  • Win and loss records

Add Sparklines to an Existing Excel Workbook

When the source data already exists in an Excel file, there is no need to recreate the worksheet. Load the workbook, locate the data range, and add the sparklines directly.

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

workbook = Workbook()

# Load an existing Excel workbook
workbook.LoadFromFile("monthly_sales.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Create a line sparkline group
sparkline_group = sheet.SparklineGroups.AddGroup()
sparkline_group.SparklineType = SparklineType.Line
sparkline_group.SparklineColor = Color.get_DarkBlue()

# Create a sparkline collection
sparklines = sparkline_group.Add()

# Add sparklines
for row in range(2, 6):
    sparklines.Add(
        sheet.Range[f"B{row}:M{row}"],
        sheet.Range[f"N{row}"]
    )

# Save the result as a new file
workbook.SaveToFile(
    "monthly_sales_with_sparklines.xlsx",
    ExcelVersion.Version2016
)

workbook.Dispose()

Saving the result as a new file is generally safer than overwriting the original workbook.

Practical Considerations

Match Each Data Range to the Correct Destination Cell

Every sparkline uses two ranges:

  • A source data range, such as B2:M2
  • A destination cell, such as N2

If the row references do not match, the sparkline may display data for a different product or record.

Sparklines in the Same Group Share Their Type and Formatting

All sparklines in one group generally use the same chart type and formatting.

If one section requires line sparklines and another section requires column sparklines, create separate sparkline groups.

Win/Loss Sparklines Focus on Positive and Negative Results

Win/loss sparklines do not emphasize the exact size of each value. They are designed to show whether values are positive or negative.

Use line or column sparklines when the magnitude and progression of the data matter.

Sparklines Do Not Replace Full Excel Charts

Sparklines are useful for compact trend visualization, but they usually do not include:

  • Axis titles
  • Data labels
  • Legends
  • Detailed scales

A standard Excel chart is more appropriate when a report needs precise values, several data series, or more complex comparisons.

Conclusion

Excel sparklines provide a compact way to display trends without taking up large areas of a worksheet.

With Python, you can:

  • Create an Excel workbook containing business data
  • Add sparklines to multiple rows
  • Create line, column, and win/loss sparklines
  • Configure sparkline colors
  • Add a trend column to an existing Excel report

For monthly sales, inventory, cost, and operational data, sparklines make it easier to identify growth, decline, and unusual changes while preserving the original table layout.

Comments

Post a Comment

Popular posts from this blog

3 Ways to Generate Word Documents from Templates in Java

A template is a document with pre-applied formatting like styles, tabs, line spacing and so on. You can quickly generate a batch of documents with the same structure based on the template. In this article, I am going to show you the different ways to generate Word documents from templates programmatically in Java using Free Spire.Doc for Java library. Prerequisite First of all, you need to add needed dependencies for including Free Spire.Doc for Java into your Java project. There are two ways to do that. If you use maven, you need to add the following code to your project’s pom.xml file. <repositories>               <repository>                   <id>com.e-iceblue</id>                   <name>e-iceblue</name>...
Post a Comment
Read more

Insert and Extract OLE objects in Word in Java

You can use OLE (Object Linking and Embedding) to include content from other programs, such as another Word document, an Excel or PowerPoint document to an existing Word document. This article demonstrates how to insert and extract embedded OLE objects in a Word document in Java by using Free Spire.Doc for Java API.   Add dependencies First of all, you need to add needed dependencies for including Free Spire.Doc for Java into your Java project. There are two ways to do that. If you use maven, you need to add the following code to your project’s pom.xml file.     <repositories>               <repository>                   <id>com.e-iceblue</id>                   <name>e-iceblue</name>    ...
Post a Comment
Read more

Simple Java Code to Convert Excel to PDF in Java

This article demonstrates a simple solution to convert an Excel file to PDF in Java by using free Excel API – Free Spire.XLS for Java . The following examples illustrate two possibilities to convert Excel to PDF:      Convert the whole Excel file to PDF     Convert a particular Excel Worksheet to PDF Before start with coding, you need to Download Free Spire.XLS for Java package , unzip it and import Spire.Xls.jar file from the lib folder in your project as a denpendency. 1. Convert the whole Excel file to PDF Spire.XLS for Java provides saveToFile method in Workbook class that enables us to easily save a whole Excel file to PDF. import com.spire.xls.FileFormat; import com.spire.xls.Workbook; public class ExcelToPDF {     public static void main(String[] args){         //Create a Workbook         Workbook workbook = new Workbook();   ...
Post a Comment
Read more