Generating and Customizing Excel Scatter Charts with Python

Scatter charts are a commonly used chart type in data analysis for displaying relationships and correlations between two variables. In statistical analysis, scientific research, and business decision-making, scatter charts help us quickly identify data patterns, trends, and outliers.

Python provides convenient ways to automatically generate Excel scatter charts, which is particularly suitable for scenarios requiring batch data processing or dynamic visualization report generation. By creating charts programmatically, we can integrate data analysis and visualization into automated workflows, improving work efficiency.

This article introduces how to create scatter charts in Excel using Python and demonstrates customization operations such as adding trendlines, setting axis labels, and formatting data labels.

Environment Setup

First, install the Spire.XLS for Python library:

pip install Spire.XLS

This library provides a complete Excel file operation API, supporting creation, reading, and modification of Excel documents, including generation and customization of various chart types.

Creating a Basic Scatter Chart

The basic process of creating a scatter chart includes preparing data, creating a chart object, setting the data source, and adjusting the chart position. The following code demonstrates how to create a scatter chart showing the relationship between salary and car prices:

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

# Create workbook
workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.Name = "Scatter Chart"

# Prepare sample data
sheet.Range["A1"].Value = "Y(Salary)"
sheet.Range["A2"].Value = "42763"
sheet.Range["A3"].Value = "195387"
sheet.Range["A4"].Value = "35672"
sheet.Range["A5"].Value = "217637"
sheet.Range["A6"].Value = "74734"
sheet.Range["A7"].Value = "130550"
sheet.Range["A8"].Value = "42976"
sheet.Range["A9"].Value = "15132"
sheet.Range["A10"].Value = "54936"

sheet.Range["B1"].Value = "X(Car Price)"
sheet.Range["B2"].Value = "19455"
sheet.Range["B3"].Value = "93965"
sheet.Range["B4"].Value = "20858"
sheet.Range["B5"].Value = "107164"
sheet.Range["B6"].Value = "34036"
sheet.Range["B7"].Value = "87806"
sheet.Range["B8"].Value = "17927"
sheet.Range["B9"].Value = "61518"
sheet.Range["B10"].Value = "29479"

# Set data format
sheet.Range["A1:B1"].ColumnWidth = 12
sheet.Range["A1:B1"].RowHeight = 15
sheet.Range["A1:B1"].Style.Color = Color.get_DarkGray()
sheet.Range["A1:B1"].Style.Font.Color = Color.get_White()
sheet.Range["A1:B1"].Style.VerticalAlignment = VerticalAlignType.Center
sheet.Range["A1:B1"].Style.HorizontalAlignment = HorizontalAlignType.Center
sheet.Range["A2:B10"].Style.NumberFormat = "\"$\"#,##0"

# Create scatter chart
chart = sheet.Charts.Add(ExcelChartType.ScatterMarkers)
chart.DataRange = sheet.Range["B2:B10"]
chart.SeriesDataFromRange = False

# Set chart position
chart.LeftColumn = 1
chart.TopRow = 11
chart.RightColumn = 10
chart.BottomRow = 28

# Set chart title
chart.ChartTitle = "Salary vs Car Price Analysis"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# Configure data series
chart.Series[0].CategoryLabels = sheet.Range["A2:A10"]
chart.Series[0].Values = sheet.Range["B2:B10"]

# Save file
workbook.SaveToFile("ScatterChart.xlsx", FileFormat.Version2010)
workbook.Dispose()

The core steps of this code include:

Create workbook and worksheet: Use Workbook() to create a new Excel document and get the first worksheet through Worksheets[0]
Fill data: Write X-axis and Y-axis data to specified cell ranges
Create chart object: Add a scatter chart through Charts.Add(ExcelChartType.ScatterMarkers), where ScatterMarkers indicates a scatter chart type using marker points
Bind data source: Set DataRange to specify the chart's data range, and set SeriesDataFromRange to False to manually configure data series
Configure chart properties: Set title, position, and category labels and values for data series

Adding Trendlines

Trendlines are an important supplement to scatter charts, helping to observe overall data trends and change patterns. Spire.XLS supports multiple trendline types, including linear, exponential, logarithmic, and moving average.

The following code demonstrates how to add an exponential trendline to an existing scatter chart:

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

# Load Excel file containing scatter chart
workbook = Workbook()
workbook.LoadFromFile("ScatterChart.xlsx")
sheet = workbook.Worksheets[0]

# Get chart and add trendline
chart = sheet.Charts[0]

# Add exponential trendline
trendline = chart.Series[0].TrendLines.Add(TrendLineType.Exponential)

# Save file
workbook.SaveToFile("ScatterChartWithTrendline.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The TrendLineType enumeration provides multiple trendline options:

Linear: Linear trendline, suitable for scenarios where data changes in a straight line
Exponential: Exponential trendline, suitable for situations where data grows or decays exponentially
Logarithmic: Logarithmic trendline, suitable for scenarios where data changes rapidly and then stabilizes
Moving_Average: Moving average trendline, used to smooth data fluctuations

Choosing the appropriate trendline type depends on actual data characteristics and analysis purposes.

Setting Axis Labels

Clear axis labels are crucial for chart readability. By setting titles for primary and secondary axes, readers can quickly understand what the chart expresses.

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

workbook = Workbook()
workbook.LoadFromFile("ScatterChart.xlsx")
sheet = workbook.Worksheets[0]
chart = sheet.Charts[0]

# Set axis titles
chart.PrimaryValueAxis.Title = "Salary (Yuan)"
chart.PrimaryCategoryAxis.Title = "Car Price (Yuan)"

# Set axis title font styles
chart.PrimaryValueAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.TitleArea.Size = 10
chart.PrimaryCategoryAxis.TitleArea.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.Size = 10

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

PrimaryValueAxis corresponds to the Y-axis (value axis), and PrimaryCategoryAxis corresponds to the X-axis (category axis). The TitleArea property allows further customization of title font, size, color, and other styles.

Customizing Data Labels

Data labels can directly display data point values on the chart, improving information density and readability. The following code demonstrates how to add and format data labels for a line chart:

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

workbook = Workbook()
workbook.CreateEmptySheets(1)
sheet = workbook.Worksheets[0]
sheet.Name = "Demo"

# Prepare data
sheet.Range["A1"].Value = "Month"
sheet.Range["A2"].Value = "Jan"
sheet.Range["A3"].Value = "Feb"
sheet.Range["A4"].Value = "Mar"
sheet.Range["A5"].Value = "Apr"
sheet.Range["A6"].Value = "May"
sheet.Range["A7"].Value = "Jun"

sheet.Range["B1"].Value = "Sales"
sheet.Range["B2"].NumberValue = 25
sheet.Range["B3"].NumberValue = 18
sheet.Range["B4"].NumberValue = 8
sheet.Range["B5"].NumberValue = 13
sheet.Range["B6"].NumberValue = 22
sheet.Range["B7"].NumberValue = 28

# Create line chart with markers
chart = sheet.Charts.Add(ExcelChartType.LineMarkers)
chart.DataRange = sheet.Range["B1:B7"]
chart.SeriesDataFromRange = False

# Set chart position
chart.TopRow = 5
chart.BottomRow = 26
chart.LeftColumn = 2
chart.RightColumn = 11

# Set chart title
chart.ChartTitle = "Sales Data Trend"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# Configure data series
cs1 = chart.Series[0]
cs1.CategoryLabels = sheet.Range["A2:A7"]

# Set data labels
cs1.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
cs1.DataPoints.DefaultDataPoint.DataLabels.HasLegendKey = False
cs1.DataPoints.DefaultDataPoint.DataLabels.HasPercentage = False
cs1.DataPoints.DefaultDataPoint.DataLabels.HasSeriesName = True
cs1.DataPoints.DefaultDataPoint.DataLabels.HasCategoryName = True
cs1.DataPoints.DefaultDataPoint.DataLabels.Delimiter = ". "
cs1.DataPoints.DefaultDataPoint.DataLabels.Size = 9
cs1.DataPoints.DefaultDataPoint.DataLabels.Color = Color.get_Red()
cs1.DataPoints.DefaultDataPoint.DataLabels.FontName = "Calibri"
cs1.DataPoints.DefaultDataPoint.DataLabels.Position = DataLabelPositionType.Center

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

Data label configuration options include:

HasValue: Display data point values
HasSeriesName: Display series name
HasCategoryName: Display category name
HasPercentage: Display percentage (applicable to pie charts, etc.)
Delimiter: Set delimiter between multiple label elements
Position: Set label position relative to data point

Comprehensive Application Example

In practical applications, we usually need to combine the above functions to create informative and visually appealing scatter charts. The following is a complete example:

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

# Create workbook
workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.Name = "Data Analysis"

# Prepare data
data = [
    ("Product A", 100, 200),
    ("Product B", 150, 180),
    ("Product C", 200, 250),
    ("Product D", 120, 160),
    ("Product E", 180, 220),
    ("Product F", 90, 140),
    ("Product G", 250, 300),
    ("Product H", 160, 190),
]

sheet.Range["A1"].Value = "Product Name"
sheet.Range["B1"].Value = "Advertising Investment (10K Yuan)"
sheet.Range["C1"].Value = "Sales Revenue (10K Yuan)"

for i, (name, ads, sales) in enumerate(data, start=2):
    sheet.Range[f"A{i}"].Value = name
    sheet.Range[f"B{i}"].NumberValue = ads
    sheet.Range[f"C{i}"].NumberValue = sales

# Create scatter chart
chart = sheet.Charts.Add(ExcelChartType.ScatterMarkers)
chart.DataRange = sheet.Range["B2:C9"]
chart.SeriesDataFromRange = False

# Set chart position and size
chart.LeftColumn = 1
chart.TopRow = 12
chart.RightColumn = 11
chart.BottomRow = 30

# Set chart title
chart.ChartTitle = "Analysis of Advertising Investment vs Sales Revenue"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 14
chart.ChartTitleArea.FontName = "Microsoft YaHei"

# Configure data series
chart.Series[0].CategoryLabels = sheet.Range["A2:A9"]
chart.Series[0].Values = sheet.Range["C2:C9"]

# Add trendline
chart.Series[0].TrendLines.Add(TrendLineType.Linear)

# Set axis titles
chart.PrimaryValueAxis.Title = "Sales Revenue (10K Yuan)"
chart.PrimaryCategoryAxis.Title = "Advertising Investment (10K Yuan)"

# Set axis title styles
chart.PrimaryValueAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.TitleArea.Size = 11
chart.PrimaryCategoryAxis.TitleArea.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.Size = 11

# Save file
workbook.SaveToFile("ComprehensiveScatterChart.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

This comprehensive example demonstrates how to:

Batch write data from data structures to Excel cells
Create a scatter chart with complete configuration
Add linear trendline to show data trends
Set axis labels in mixed Chinese and English
Adjust chart size and position for optimal visual effect

Practical Tips

When creating scatter charts, the following tips can improve chart quality:

Data Preprocessing: Ensure data cleanliness, handle missing values and outliers to avoid misleading results in charts.

Chart Type Selection: Choose appropriate scatter chart variants based on data characteristics:

ScatterMarkers: Display only data point markers
ScatterSmoothedLines: Display smooth curves connecting data points
ScatterLines: Display straight lines connecting data points

Color Scheme: Use contrasting colors to distinguish different data series and maintain visual consistency.

Legend Position: When there are multiple data series, place legends appropriately to avoid obscuring data points.

Export Optimization: Choose appropriate file format and resolution based on purpose; higher resolution is recommended for printing purposes.

Summary

This article introduced the complete process of creating and customizing scatter charts in Excel using Python, including core functions such as basic chart creation, trendline addition, axis label setting, and data label formatting.

By mastering these techniques, developers can integrate data visualization into automated data processing workflows, improving data analysis efficiency. Scatter charts combined with trendlines are particularly suitable for exploring correlations between variables and have wide applications in fields such as statistical analysis, market research, and scientific experiments.

Further expansion directions include: