Create Various Types of Charts in Excel with Python

15 min readMar 5, 2024

Charts in Excel are a powerful visual tool that allows us to represent and analyze data more effectively. They enable us to identify patterns, trends, and relationships in our data, making it easier to interpret and communicate information to others. Whether you are an analyst, a business professional, a student, or simply someone who wants to present data in a visually appealing manner, Excel provides a wide range of charting options that can cater to your needs. In this article, we will explore how to create various types of charts in Excel using Python.

We will discuss the creation of both the classic and newer chart types:

Create a Column Chart in Excel with Python
Create a Pie Chart in Excel with Python
Create a Line Chart in Excel with Python
Create a Bar Chart in Excel with Python
Create a Scatter (XY) Chart in Excel with Python
Create an Area Chart in Excel with Python
Create a Combo Chart in Excel with Python
Create a Waterfall Chart in Excel with Python
Create a Treemap Chart in Excel with Python
Create a Box and Whisker Chart in Excel with Python
Create a Sunburst Chart in Excel with Python
Create a Funnel Chart in Excel with Python
Create a Histogram Chart in Excel with Python
Create a Pareto Chart in Excel with Python

Python Library to Create Charts in Excel

To create charts in Excel files with Python, we can use the Spire.XLS for Python library.

Spire.XLS for Python is an easy-to-use and feature-rich library for creating, reading, editing, and converting Excel files within Python applications. It supports generating up to 81 types of Excel charts, including both the classic chart types (e.g. Column Chart, Pie Chart, Line Chart, Bar Chart, Scatter Chart, Area Chart, Surface Chart, Radar Chart, Stock Chart, and Combo Chart), as well as the newer chart types (e.g. Waterfall Chart, Treemap Chart, Box and Whisker Chart, Sunburst Chart, Funnel Chart, Histogram Chart and Pareto Chart).

You can install Spire.XLS for Python from PyPI by running the following commands in your terminal:

pip install Spire.Xls

For more detailed information about the installation, you can check this official documentation: How to Install Spire.XLS for Python in VS Code.

Create a Column Chart in Excel with Python

A column chart in Excel is a type of chart that uses vertical bars to represent data values. Each column corresponds to a specific category or group, and the height of the column indicates the value or magnitude of the data it represents. Column charts are commonly used to compare data across different categories or groups.

You can create many types of column charts with Spire.XLS for Python, such as clustered column charts, 3D clustered column charts, and stacked column charts. Here is a simple example that shows how to create a clustered column chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "ClusteredColumnChart"

# Add data to specific cells of the worksheet
sheet.Range["A1"].Value = "Product"
sheet.Range["A2"].Value = "Socks"
sheet.Range["A3"].Value = "Bib-Shorts"
sheet.Range["A4"].Value = "Shorts"
sheet.Range["A5"].Value = "Tights"
sheet.Range["B1"].Value = "July"
sheet.Range["B2"].NumberValue = 6000
sheet.Range["B3"].NumberValue = 8000
sheet.Range["B4"].NumberValue = 9000
sheet.Range["B5"].NumberValue = 8500
sheet.Range["C1"].Value = "Aug"
sheet.Range["C2"].NumberValue = 3000
sheet.Range["C3"].NumberValue = 2000
sheet.Range["C4"].NumberValue = 2300
sheet.Range["C5"].NumberValue = 4200

# Set cell style
sheet.Range["A1:C1"].RowHeight = 15
sheet.Range["A1:C1"].Style.Color = Color.get_DarkGray()
sheet.Range["A1:C1"].Style.Font.Color = Color.get_White()
sheet.Range["A1:C1"].Style.VerticalAlignment = VerticalAlignType.Center
sheet.Range["A1:C1"].Style.HorizontalAlignment = HorizontalAlignType.Center
sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0"

# Add a clustered column chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)
# Set the data range of the chart 
chart.DataRange = sheet.Range["A1:C5"]
chart.SeriesDataFromRange = False

# Set chart position
chart.LeftColumn = 5
chart.TopRow = 1
chart.RightColumn = 14
chart.BottomRow = 24

# Set chart title
chart.ChartTitle = "Monthly Sales for Clothing Category"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# Set chart axis
chart.PrimaryCategoryAxis.Title = "Product"
chart.PrimaryCategoryAxis.Font.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.Title = "Sales"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.MinValue = 1000
chart.PrimaryValueAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90

# Set format and data labels for chart series
for cs in chart.Series:
    cs.Format.Options.IsVaryColor = True
    cs.Format.Options.Overlap = -50
    cs.Format.Options.GapWidth = 350
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True

# Set the position of the chart legend
chart.Legend.Position = LegendPositionType.Top

# Save the result file
workbook.SaveToFile("ClusteredColumnChart.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Create Column Chart in Excel with Python

Create a Pie Chart in Excel with Python

A pie chart in Excel is a circular chart divided into slices, with each slice representing a proportion or percentage of a whole. The size of each slice is proportional to the value it represents, allowing for a visual representation of the relative contribution of different categories to a total. Pie charts are often used to illustrate the distribution or composition of a dataset.

You can create many types of pie charts with Spire.XLS for Python, such as pie charts, 3D pie charts, pie of pie charts, bar of pie charts, and doughnut charts. Here is a simple example that shows how to create a pie chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Template.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "PieChart"

# Add a pie chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.Pie)
# Set the data range of the chart 
chart.DataRange = sheet.Range["A1:D2"]
chart.SeriesDataFromRange = False

# Set chart position
chart.LeftColumn = 6
chart.TopRow = 1
chart.RightColumn = 12
chart.BottomRow = 17

# Set chart title
chart.ChartTitle = "Wildlife Population in 2017"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# Set category labels, values, and data labels for chart series
cs = chart.Series[0]
cs.CategoryLabels = sheet.Range["B1:D1"]
cs.Values = sheet.Range["B2:D2"]
cs.DataPoints.DefaultDataPoint.DataLabels.HasPercentage = True

chart.PlotArea.Fill.Visible = False

# Set the position of the chart legend
chart.Legend.Position = LegendPositionType.Bottom

# Save the result file
workbook.SaveToFile("PieChart.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Create a Line Chart in Excel with Python

A line chart in Excel is a type of graph that displays data as a series of points connected by lines. It is used to show trends, patterns, or changes over time.

You can create many types of line charts with Spire.XLS for Python, such as line charts, stacked line charts, and 3D line charts. Here is a simple example that shows how to create a line chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("LineTemplate.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "LineChart"

# Add a line chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.Line)
# Set the data range of the chart 
chart.DataRange = sheet.Range["A1:E5"]
chart.SeriesDataFromRange = False

# Set chart position
chart.LeftColumn = 1
chart.TopRow = 7
chart.RightColumn = 11
chart.BottomRow = 30

#Set chart title
chart.ChartTitle = "Monthly Sales for Countries"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# Set chart axis
chart.PrimaryCategoryAxis.Title = "Month"
chart.PrimaryCategoryAxis.Font.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.Title = "Sales"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90
chart.PrimaryValueAxis.MinValue = 1000
chart.PrimaryValueAxis.TitleArea.IsBold = True

# Set format and data labels for chart series
for cs in chart.Series:
    cs.Format.Options.IsVaryColor = True
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True

chart.PlotArea.Fill.Visible = False

# Set the position of chart legend
chart.Legend.Position = LegendPositionType.Bottom

# Save the result file
workbook.SaveToFile("LineChart.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Create a Bar Chart in Excel with Python

A bar chart is a commonly used data visualization tool for comparing data across different categories or groups. It is useful when you want to compare data side by side.

Here is a simple example that shows how to create a bar chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("BarTemplate.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "BarChart"

# Add a bar chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.BarClustered)
# Set the data range of the chart 
chart.DataRange = sheet.Range["A1:E5"]
chart.SeriesDataFromRange = False

# Set chart position
chart.LeftColumn = 7
chart.TopRow = 1
chart.RightColumn = 14
chart.BottomRow = 28

# Set chart axis
chart.PrimaryCategoryAxis.Title = "Country"
chart.PrimaryCategoryAxis.Font.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.TextRotationAngle = -90
chart.PrimaryValueAxis.Title = "Sales (in Dollars)"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.MinValue = 1000
chart.PrimaryValueAxis.TitleArea.IsBold = True

# Set data labels 
for cs in chart.Series:
    cs.Format.Options.IsVaryColor = True
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True

# Set the postion of chart legend
chart.Legend.Position = LegendPositionType.Bottom

# Save the result file
workbook.SaveToFile("BarChart.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Create a Scatter (XY) Chart in Excel with Python

A scatter or XY chart is used to display the relationship between two sets of numeric data. It is useful when you want to visualize correlations or patterns between variables.

Here is a simple example that shows how to create a scatter chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("XYTemplate.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "XYChart"

# Add a XY (scatter) chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.ScatterMarkers)
# Set the data range of the chart 
chart.DataRange = sheet.Range["B2:B6"]
chart.SeriesDataFromRange = False

# Set chart position
chart.LeftColumn = 4
chart.TopRow = 1
chart.RightColumn = 11
chart.BottomRow = 15

# Set category labels and values for the first data series
chart.Series[0].CategoryLabels = sheet.Range["A2:A6"]
chart.Series[0].Values = sheet.Range["B2:B6"]

# Set chart axis
chart.PrimaryCategoryAxis.Title = "Advertising"
chart.PrimaryValueAxis.Title = "Items Sold"

# Save the result file
workbook.SaveToFile("XYChart.xlsx", FileFormat.Version2016)
workbook.Dispose()

Create Scatter Chart in Excel with Python

Create an Area Chart in Excel with Python

An area chart is used to display the cumulative totals or proportions of multiple data series over time or categories. It is useful for illustrating trends and comparing the overall magnitude of different data sets.

Here is a simple example that shows how to create an area chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("AreaTemplate.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "AreaChart"

# Add a stacked area chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.AreaStacked)
# Set the data range of the chart 
chart.DataRange = sheet.Range["A1:D6"]
chart.SeriesDataFromRange = False

# Set chart position
chart.LeftColumn = 6
chart.TopRow = 1
chart.RightColumn = 13
chart.BottomRow = 15

# Set the postion of chart legend
chart.Legend.Position = LegendPositionType.Bottom

# Save the result file
workbook.SaveToFile("AreaChart.xlsx", FileFormat.Version2016)
workbook.Dispose()

Create a Combo Chart in Excel with Python

A combo chart in Excel combines two or more different chart types into a single chart. This allows users to display and compare multiple sets of data with different scales or units of measurement within the same visual representation.

Here is a simple example that shows how to create a combo chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ComboTemplate.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "ComboChart"

# Add a combo chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.CombinationChart)

# Set the data range of the chart 
chart.DataRange = sheet.Range["A1:C5"]
chart.SeriesDataFromRange = False

# Set chart position
chart.LeftColumn = 1
chart.TopRow = 7
chart.RightColumn = 6
chart.BottomRow = 20

# Set different chart types for different chart series
cs1 = chart.Series[0]
cs1.SerieType = ExcelChartType.ColumnClustered
cs2 = chart.Series[1]
cs2.SerieType = ExcelChartType.LineMarkers

# Add a secondary category axis to the chart 
chart.SecondaryCategoryAxis.IsMaxCross = True
cs2.UsePrimaryAxis = False

# Save the result file
workbook.SaveToFile("ComboChart.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Create a Waterfall Chart in Excel with Python

A waterfall chart in Excel is a specialized chart type primarily used to illustrate how values increase or decrease over time or between categories. It is commonly employed in financial analysis or project management to showcase the cumulative effect of positive and negative changes.

Here is a simple example that shows how to create a waterfall chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("WaterfallTemplate.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "WaterfallChart"

# Add a waterfall chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.WaterFall)
# Set data range of the chart  
chart.DataRange = sheet["A1:B5"]

# Set chart position
chart.TopRow = 1
chart.BottomRow = 19
chart.LeftColumn = 4
chart.RightColumn = 12

# Set chart title
chart.ChartTitle = "The Fourth Quarter"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# Set the 3rd and 5th data points as total
chart.Series[0].DataPoints[2].SetAsTotal = True
chart.Series[0].DataPoints[4].SetAsTotal = True

# Show the connector lines between data points
chart.Series[0].Format.ShowConnectorLines = True

# Set data labels and legend option
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.HasValue = True
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 8
chart.Legend.Position = LegendPositionType.Right

# Save the result file
workbook.SaveToFile("WaterfallChart.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Create Waterfall Chart in Excel with Python

Create a Treemap Chart in Excel with Python

A treemap chart in Excel provides a hierarchical visualization of data using nested rectangles. It is useful for displaying complex hierarchical structures or comparing proportions within categories.

Here is a simple example that shows how to create a treemap chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("TreemapTemplate.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "TreemapChart"

# Add a treemap chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.TreeMap)
# Set data range of the chart  
chart.DataRange = sheet["A1:C18"]

# Set chart position
chart.TopRow = 1
chart.BottomRow = 19
chart.LeftColumn = 5
chart.RightColumn = 13

# Set chart title
chart.ChartTitle = "Product Sales"

# Set label option
chart.Series[0].DataFormat.TreeMapLabelOption = ExcelTreeMapLabelOption.Banner

# Set data label size     
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 8

# Save the result file
workbook.SaveToFile("TreemapChart.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Create Treemap Chart in Excel with Python

Create a Box and Whisker Chart in Excel with Python

A box and whisker chart in Excel is commonly used in statistical analysis to display the distribution of data and identify key statistical measures.

Here is a simple example that shows how to create a box and whisker chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("BoxAndWhiskerTemplate.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "BoxAndWhiskerChart"

# Add a box and whisker chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.BoxAndWhisker)
# Set data range of the chart  
chart.DataRange = sheet["A1:D17"]

# Set chart position
chart.TopRow = 1
chart.BottomRow = 19
chart.LeftColumn = 6
chart.RightColumn = 14

# Set chart title
chart.ChartTitle = "Product Annual Sales"

# Set data format for chart series
seriesA = chart.Series[0]
seriesA.DataFormat.ShowInnerPoints = False
seriesA.DataFormat.ShowOutlierPoints = True
seriesA.DataFormat.ShowMeanMarkers = True
seriesA.DataFormat.ShowMeanLine = False
seriesA.DataFormat.QuartileCalculationType = ExcelQuartileCalculation.ExclusiveMedian

seriesB = chart.Series[1]
seriesB.DataFormat.ShowInnerPoints = False
seriesB.DataFormat.ShowOutlierPoints = True
seriesB.DataFormat.ShowMeanMarkers = True
seriesB.DataFormat.ShowMeanLine = False
seriesB.DataFormat.QuartileCalculationType = ExcelQuartileCalculation.InclusiveMedian
 
seriesC = chart.Series[2]
seriesC.DataFormat.ShowInnerPoints = False
seriesC.DataFormat.ShowOutlierPoints = True
seriesC.DataFormat.ShowMeanMarkers = True
seriesC.DataFormat.ShowMeanLine = False
seriesC.DataFormat.QuartileCalculationType = ExcelQuartileCalculation.ExclusiveMedian

# Save the result file
workbook.SaveToFile("BoxAndWhiskerChart.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Create Box and Whisker Chart in Excel with Python

Create a Sunburst Chart in Excel with Python

A sunburst chart in Excel is a radial chart that displays hierarchical data with multiple levels in a circular layout.

Here is a simple example that shows how to create a sunburst chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("SunburstTemplate.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "SunburstChart"

# Add a sunburst chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.SunBurst)
# Set data range of the chart  
chart.DataRange = sheet["A1:C10"]

# Set chart position
chart.TopRow = 1
chart.BottomRow = 19
chart.LeftColumn = 5
chart.RightColumn = 13

# Set chart title
chart.ChartTitle = "Region Sales by Category"

# Set data label size    
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 8

# Hide chart legend
chart.HasLegend = False

# Save the result file
workbook.SaveToFile("SunburstChart.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Create Sunburst Chart in Excel with Python

Create a Funnel Chart in Excel with Python

A funnel chart in Excel is often used in sales or marketing to visualize the progression of data through different stages, such as the sales pipeline or customer conversion funnel.

Here is a simple example that shows how to create a funnel chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("FunnelTemplate.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "FunnelChart"

# Add a funnel chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.Funnel)
# Set data range of the chart  
chart.DataRange = sheet["A1:B5"]

# Set chart position
chart.TopRow = 1
chart.BottomRow = 19
chart.LeftColumn = 4
chart.RightColumn = 12

# Set chart title
chart.ChartTitle = "Sales Funnel"

# Set data labels
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.HasValue = True
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 8

# Set gap width
chart.Series[0].Format.Options.GapWidth = 10

# Hide chart legend
chart.HasLegend = False

#save the file
workbook.SaveToFile("FunnelChart.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Create Funnel Chart in Excel with Python

Create a Histogram Chart in Excel with Python

A histogram chart in Excel is used to display the distribution of numerical data by dividing it into intervals or bins and representing the frequency or count of data points within each bin.

Here is a simple example that shows how to create a histogram chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("HistogramTemplate.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "HistogramChart"

# Add a histogram chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.Histogram)
# Set data range of the chart  
chart.DataRange = sheet["A1:B12"]

# Set chart position
chart.TopRow = 1
chart.BottomRow = 17
chart.LeftColumn = 4
chart.RightColumn = 12

# Set chart title
chart.ChartTitle = "Test Score"

# Set bin width       
chart.PrimaryCategoryAxis.BinWidth = 6

# Set gap width
chart.Series[0].DataFormat.Options.GapWidth = 6

# Set axis title
chart.PrimaryValueAxis.Title = "Number of students"
chart.PrimaryCategoryAxis.Title = "Score"

# Hide chart legend
chart.HasLegend = False

# Save the result file
workbook.SaveToFile("HistogramChart.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Create Histogram Chart in Excel with Python

Create a Pareto Chart in Excel with Python

A pareto chart in Excel is a type of chart that combines a bar graph and a line graph to display and prioritize data in descending order. It is based on the Pareto principle, also known as the 80/20 rule, which states that roughly 80% of the effects come from 20% of the causes. A Pareto Chart is useful in situations where you want to identify and prioritize the most significant factors or issues contributing to a problem or outcome.

Here is a simple example that shows how to create a pareto chart in Excel using Python and Spire.XLS for Python:

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ParetoTemplate.xlsx")

# Get the first worksheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "ParetoChart"

# Add a pareto chart to the worksheet
chart = sheet.Charts.Add(ExcelChartType.Pareto)
# Set data range of the chart  
chart.DataRange = sheet["A2:B7"]

# Set chart position
chart.TopRow = 1
chart.BottomRow = 19
chart.LeftColumn = 4
chart.RightColumn = 12

# Set chart title
chart.ChartTitle = "Expenses"

# Set category axis
chart.PrimaryCategoryAxis.IsBinningByCategory = True
chart.PrimaryCategoryAxis.OverflowBinValue = 5
chart.PrimaryCategoryAxis.UnderflowBinValue = 1

# Set color for pareto line      
chart.Series[0].ParetoLineFormat.LineProperties.Color = Color.get_Red()

# Set gap width
chart.Series[0].DataFormat.Options.GapWidth = 6

# Hide chart legend
chart.HasLegend = False

# Save the result file
workbook.SaveToFile("ParetoChart.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Create Pareto Chart in Excel with Python

Conclusion

This article demonstrated how to create the classic and newer types of charts in Excel using Python. We hope you find it helpful.