Customizing Excel Chart Settings with Python openpyxl

Sharing is caring!

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

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:

1. Remove the cumulative data series from the first chart
2. Then create a new chart2 object with the cumulative data like normal
3. Set the y_axis.axId for chart2 to 200
4. 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
5. 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!

Additional Resources

How to Create Charts in Excel with Python openpyxl

How to Use Python to Read Excel Formula

Work with Excel Named Range in Python

Write Data to Excel using Python