Openpyxl - Plotting Bubble Charts in Excel

Prerequisites

1. Reading Excel data with Openpyxl
2. Writing to Excel with Openpyxl

A Bubble chart is similar a Scatter chart except, the data points are replaced with bubbles and an additional dimension of the data is represented in the size of the bubbles.

Consider the data shown in the below image.

Let’s plot the Total Cost per product with cost per unit as the size of the Bubble.

Code

import openpyxl
from openpyxl.chart import Reference, BubbleChart, Series

wb = openpyxl.load_workbook('wb1.xlsx')
sheet = wb.active

# Data for plotting
# Choose the product column
xvalues = Reference(sheet,
                   min_col=2,
                   max_col=2,
                   min_row=2,
                   max_row=11)

# Choose the Total Cost Column
yvalues = Reference(sheet,
                   min_col=5,
                   max_col=5,
                   min_row=2,
                   max_row=11)

# Choose the Cost per Unit column
size = Reference(sheet,
                 min_col=3,
                 max_col=3,
                 min_row=2,
                 max_row=11)

# Create object of BubbleChart class
chart = BubbleChart()
series = Series(values=yvalues, xvalues=xvalues, zvalues=size)
chart.series.append(series)
# set the title of the chart
chart.title = "Cost Analysis of Products"
chart.style = 18 # use a preset style
# set the title of the x-axis
chart.x_axis.title = "Products"

# set the title of the y-axis
chart.y_axis.title = "Total Cost Per Product"

# the top-left corner of the chart
# is anchored to cell F2 .
sheet.add_chart(chart,"F2")

# save the file 
wb.save("wb1.xlsx")

Output

Since the type of x and y axis is declared NumericType in the BubbleChart class, you cannot set the product name (or any Text based label) for any axis.