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.
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
Feel free to copy the below code to replicate the sample dataset we use in this tutorial. The below code does the following:
- Create an empty workbook by calling wb=Workbook(), note we’ll worry about file naming when we save the Excel file
- The data is saved in a list of tuples call rows
- Then we write to Excel file that we just created (in memory), starting from cell A1
- 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:
- For values, we have to tell the constructor the bounding coordinates of the source data range
- 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.
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