How to Create Charts in Excel with Python openpyxl

Sharing is caring!

Last Updated on July 14, 2022 by Jay

In this tutorial, we are going to learn how to create charts in Excel using the openpyxl Python library. As a regular Excel user, it’s very common to create a quick chart for the data in Excel, so it would be nice to automate charting as well.

Library

This is part of the openpyxl tutorial series. The only required library is openpyxl, make sure you install it to follow along. To install the library, bring up a command prompt, then type:

pip install openpyxl

Sample Dataset

Feel free to copy the below code to replicate the sample dataset we use in this tutorial. The below code does the following:

1. Create an empty workbook by calling wb=Workbook(), note we’ll worry about file naming when we save the Excel file
2. The data is saved in a list of tuples call rows
3. Then we write to Excel file that we just created (in memory), starting from cell A1
4. The last line wb.save() will save the wb to disk. Uncomment this line if you want to see how it looks like in Excel, this is optional

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = 'chart'

rows = [
    ('Month', 'Apple Sales', 'Banana Sales'),
    ('Jan', 100, 200),
    ('Feb', 200, 300),
    ('Mar', 300, 400),
    ('Apr', 50, 20),
    ('May', 500, 600),
    ('Jun', 100, 200),
]

for row in rows:
    ws.append(row)

#wb.save('chart_eg.xlsx')

Use Python openpyxl To Create Excel Charts

We’ll need to import a few other things to create Excel charts using openpyxl:

from openpyxl.chart import Reference, LineChart

Step 1 – Select A Data Source / Reference

When creating a chart manually in Excel, the first thing we do is to select a range of cells as the data source. In openpyxl, we take the same approach by creating a Reference object that contains the cell ranges for the data we want to plot.

Note from the above Excel screenshot, our data:

• starts from column B (column 2), row 1
• ends at column C (column 3), row 7

There are two ways to construct a Reference data source object. See below code for the differences:

1. For values, we have to tell the constructor the bounding coordinates of the source data range
2. For x_values, all we need is to pass in the cell range (as a String) that contains the data for x-axis labels

values = Reference(ws, min_col= 2, min_row=1, max_col = 3, max_row = 7)
x_values = Reference(ws, range_string="chart!A2:A7")

Step 2 – Select A Chart Type

Once we have data selected, the next thing is to decide on a chart type. We’ll go with a line chart:

#initialize LineChart object
chart = LineChart()
#add data to the LineChart object
chart.add_data(values, titles_from_data = True)
#set x-axis
chart.set_categories(x_values)

##cosmetics
chart.title = 'Fruit Sales'
chart.x_axis.title = 'Month'
chart.y_axis.title = 'Fruit Sales (USD Mil)'
chart.legend.position = 'b'

The above code should be self-explanatory. In case you are wondering why the heck is setting the x-axis called set_categories(). Go into Excel chart menu and you’ll find the answer…

Step 3 – Place The Chart On A Worksheet

The chart we just “created” is still inside our computer’s memory, and we need to put it on a worksheet to see it. Inside the ws.add_chart() method, the first argument is the chart object itself, and the second argument is the starting cell location for our chart.

ws.add_chart(chart, 'H1')
wb.save('chart_eg.xlsx')

In the next tutorial, we’ll explore some advanced techniques on how to customize the chart to our likings.

Additional Resource

Customizing Excel Chart Settings with Python openpyxl

How to Use Python to Read Excel Formula

How to Work with Excel Named Range in Python

Work with Excel Named Range in Python

Write Data to Excel using Python