Customizing Excel Chart Settings with Python openpyxl

Last Updated on July 14, 2022 by Jay

We can use openpyxl to customize Excel chart settings such as the color, pattern/style, or even adding a secondary axis.

This is part 2 and a follow-up to a previous tutorial that you can find here: How to Create Charts in Excel with Python openpyxl. Make sure you understand what we did in the first part to create the chart.

Code To Create The Basic Chart

Below is the code we used from the last tutorial. We modified the data a little bit to include a cumulative sum for both Apple and Banana sales. Most of the new code we’ll add is right before where we add the chart to a worksheet.

from openpyxl import Workbook
from openpyxl.chart import Reference, LineChart

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

rows = [
    ('Month', 'Apple Sales', 'Banana Sales', 'Apple Cumulative', 'Banana Cumulative'),
    ('Jan', 100, 200, 100, 200),
    ('Feb', 200, 300, 300, 500),
    ('Mar', 300, 400, 600, 900),
    ('Apr', 50, 20, 650, 920),
    ('May', 500, 600, 1150, 1520),
    ('Jun', 100, 200, 1250, 1720),
]

for row in rows:
    ws.append(row)

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

#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'

###################
NEW
CODE
HERE
###################

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

openpyxl charts — Excel file created by openpyxl

Changing The Charts Color And Pattern In openpyxl

We know that in Excel, the underlying data for a chart is called “Series”. It appears the openpyxl developers have adopted that name as well. There are 2 series in our example.

In Python, we can access these series objects like the following:

s1 = chart.series[0]
s2 = chart.series[1]
s3 = chart.series[2]
s4 = chart.series[3]

chart.series[0] refers to the Apple Sales series
chart.series[1] refers to the Banana Sales series
And chart.series[2] and chart.series[3] refer to the Apple and Banana cumulative sales, respectively

These series objects have a bunch of parameters we can modify.

For example, let’s change the Apple Sales series to cyan, which has a RGB hex value of “00FFFF”, then we’ll change it from a solid line to a dash line. Note you have to save the Excel file to view the changes.

s1.graphicalProperties.line.solidFill = "00FFFF"
s1.graphicalProperties.line.dashStyle = "dash"

Let’s then change the Banana Sales series. Instead of a line, let’s do stars with red color “FF0000”.

s2.marker.symbol = "star" #change line to start
s2.graphicalProperties.line.noFill = True #hide line

s2.marker.graphicalProperties.line.solidFill = "00FF00" #Marker color

Add A Secondary Y Axis

Sometimes we add a secondary y-axis to the chart so the graph scaling looks better. Let’s put the cumulative sales on a secondary y-axis. We need to take the following steps to do this:

Remove the cumulative data series from the first chart
Then create a new chart2 object with the cumulative data like normal
Set the y_axis.axId for chart2 to 200
Set the chart2.y_axis.crosses to max. This will display y-axis of the secondary chart on the far right end on the x-axis
Combine the two charts

#remove the cumulative data series from the first chart
values = Reference(ws, min_col= 2, min_row=1, max_col = 3, max_row = 7)

#create 2nd chart
values2 = Reference(ws, min_col= 4, min_row=1, max_col = 5, max_row = 7)
chart2 = LineChart()
chart2.add_data(values2, titles_from_data=True)

chart2.y_axis.title = 'Cumultaive Sales'

chart2.y_axis.axId = 200 #secondary y-axis ID
chart2.y_axis.crosses = 'max' #display y-axis of the 2nd chart on the far right end on the x-axis
chart += chart2

Now the cumulative sales data is on a separate axis, and we can modify the color or patterns like usual. Feel free to try this on your own as an exercise!