Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Create a Bubble Chart in Excel with openpyxl

Plot three measures at once — margin across, growth up, revenue as bubble size — to see a product portfolio or customer list at a glance. Build a native Excel bubble chart with openpyxl, add one series per group, size bubbles by area, scale them, label each bubble with its name, and keep axis labels clear of negative values.
Charts & imagesopenpyxl

A scatter chart shows how two measures relate; a bubble chart adds a third as the size of each point. That makes it the classic portfolio view: products placed by margin and growth, sized by revenue, so the large, profitable, growing products stand out from the small and declining ones in one glance. The same shape works for customers (order frequency, average order value, lifetime revenue) or projects (cost, benefit, risk). This page, part of Creating Charts in Excel with openpyxl, builds a native Excel bubble chart with openpyxl, with bubbles grouped by colour and labelled by name.

Three measures, three visual channels A table showing how each measure maps to the chart. Margin percent is the horizontal position, passed as xvalues. Growth percent is the vertical position, passed as values. Revenue is the bubble size, passed as zvalues. Region is the colour, one series per region. Channel Series argument Margin % across (x) xvalues Growth % up (y) values Revenue bubble size zvalues Region colour one series each

Prerequisites

Bash
pip install openpyxl

The example portfolio has six products in two regions, each with a margin, a year-on-year growth rate and a revenue figure.

Step 1 — Lay out the data and add one series per group

A bubble series needs three columns: x positions, y positions and sizes. Keep each group's rows together so each series can reference one contiguous block:

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

rows = [("Brackets", "North", 32, 8, 480), ("Hinges", "North", 24, 15, 220), ("Panels", "North", 18, -4, 610),
        ("Frames", "South", 28, 22, 150), ("Latches", "South", 35, 3, 90), ("Rails", "South", 12, 11, 330)]
wb = Workbook(); ws = wb.active; ws.title = "Portfolio"
ws.append(["Product", "Region", "Margin %", "Growth %", "Revenue (k)"])
for r in sorted(rows, key=lambda r: r[1]):
    ws.append(r)

def block(region):
    found = [i for i in range(2, ws.max_row + 1) if ws.cell(row=i, column=2).value == region]
    return found[0], found[-1]

chart = BubbleChart()
chart.title = "Product portfolio — margin, growth and revenue"
for region in ["North", "South"]:
    first, last = block(region)
    chart.series.append(Series(values=Reference(ws, min_col=4, min_row=first, max_row=last),
                               xvalues=Reference(ws, min_col=3, min_row=first, max_row=last),
                               zvalues=Reference(ws, min_col=5, min_row=first, max_row=last),
                               title=region))
print([(s.tx.v, s.xVal.numRef.f, s.bubbleSize.numRef.f) for s in chart.series])

values is the vertical position, xvalues the horizontal and zvalues the size — the order is easy to mix up, and a chart with growth across and margin up still renders without complaint. Sorting the rows by region before writing keeps each group contiguous; with pandas, sort_values("Region") does the same.

Step 2 — Size bubbles by area and set the scale

Two settings control the bubbles. sizeRepresents = "area" makes a bubble's area proportional to its value, which matches how people judge size; the alternative, "w", sizes by width, so a value twice as large looks four times as big. bubbleScale shrinks or enlarges every bubble as a percentage of the default:

Python
chart.sizeRepresents = "area"
chart.bubbleScale = 60
chart.x_axis.title = "Margin %"
chart.y_axis.title = "Growth % (year on year)"
chart.x_axis.scaling.min, chart.x_axis.scaling.max = 0, 40
chart.y_axis.scaling.min, chart.y_axis.scaling.max = -10, 30
chart.x_axis.delete = False
chart.y_axis.delete = False
chart.x_axis.tickLblPos = "low"
chart.width, chart.height = 20, 12
ws.add_chart(chart, "G2")
wb.save("portfolio_bubbles.xlsx")
print(chart.sizeRepresents, chart.bubbleScale)

Fixed axis ranges keep bubbles near the edges from being clipped and make the chart comparable from one report to the next. Because Panels has negative growth, the horizontal axis would cross the plot at zero with its labels in the middle; tickLblPos = "low" moves the labels to the bottom edge. The delete = False lines keep both axes visible in Excel — see Format Chart Axes with openpyxl.

Sizing bubbles by area or by width Two panels comparing Panels, with revenue 610, and Latches, with revenue 90, a ratio of about 6.8. Sized by area, the Panels bubble has 6.8 times the area of Latches, which looks right. Sized by width, its width is 6.8 times as large and its area about 46 times as large, which exaggerates the difference. sizeRepresents = "area" 610 vs 90 → 6.8× the area matches how size is judged Excel's default sizeRepresents = "w" 610 vs 90 → 6.8× the width about 46× the area big values exaggerated

Step 3 — Label every bubble with its name

A bubble chart without names makes readers match colours and sizes back to a table. openpyxl cannot label points with arbitrary cell text, but it can show a series' name as its label — so give each product its own series, coloured by region, and turn on series-name labels:

Python
from openpyxl.chart.label import DataLabelList

REGION_COLOURS = {"North": "0B6157", "South": "B4740A"}
labelled = BubbleChart()
labelled.title = "Product portfolio — labelled"
for i in range(2, ws.max_row + 1):
    name, region = ws.cell(row=i, column=1).value, ws.cell(row=i, column=2).value
    series = Series(values=Reference(ws, min_col=4, min_row=i), xvalues=Reference(ws, min_col=3, min_row=i),
                    zvalues=Reference(ws, min_col=5, min_row=i), title=name)
    series.graphicalProperties.solidFill = REGION_COLOURS[region]
    series.graphicalProperties.line.solidFill = "FFFFFF"
    series.dLbls = DataLabelList(showSerName=True, showVal=False)
    labelled.series.append(series)
labelled.sizeRepresents = "area"
labelled.bubbleScale = 60
labelled.legend = None
labelled.x_axis.title, labelled.y_axis.title = "Margin %", "Growth % (year on year)"
labelled.x_axis.scaling.min, labelled.x_axis.scaling.max = 0, 40
labelled.y_axis.scaling.min, labelled.y_axis.scaling.max = -10, 30
labelled.x_axis.delete = labelled.y_axis.delete = False
labelled.x_axis.tickLblPos = "low"
labelled.width, labelled.height = 20, 12
ws.add_chart(labelled, "G27")
wb.save("portfolio_bubbles.xlsx")
print(len(labelled.series), "series, one per product:", [s.tx.v for s in labelled.series])

Each bubble now carries its product name, and colours still show the region; the legend is dropped because it would list six products rather than two regions — say what the colours mean in the title or a note beside the chart. showVal=False stops the label also printing the y value. Beyond a dozen or so bubbles, labels start to collide, and labelling only the largest few is clearer.

The portfolio data behind the chart The Portfolio sheet sorted by region. North: Brackets margin 32, growth 8, revenue 480; Hinges 24, 15, 220; Panels 18, minus 4, 610. South: Frames 28, 22, 150; Latches 35, 3, 90; Rails 12, 11, 330. Panels is the largest bubble but the only product shrinking. Portfolio A B C D E 1 Product Region Margin % Growth % Revenue (k) 2 Brackets North 32 8 480 3 Hinges North 24 15 220 4 Panels North 18 -4 610 5 Frames South 28 22 150 6 Latches South 35 3 90 7 Rails South 12 11 330

Reading the chart

The layout invites a four-quadrant reading. Top right — high margin and growing — are the products to invest in; here Frames stands out, small but growing fastest at a good margin. Bottom left would be low margin and shrinking. The biggest bubble, Panels, sits low and to the left: the largest revenue line has the thinnest margin and is shrinking, which is the kind of finding the chart exists to surface. Add reference lines at the average margin and growth by charting two extra series with two points each, or simply mention the averages in the title.

Keep bubble sizes comparable between reports

Excel sizes bubbles relative to the largest value in the chart: the biggest bubble always gets the same diameter, and the rest are scaled against it. That is fine within one chart, but it means sizes cannot be compared between two charts — last quarter's 610 and this quarter's 900 would both be drawn as the same largest bubble. When a report is produced every month and readers compare the charts side by side, add a reference point with a fixed size larger than any real value, such as 1,000, placed outside the visible axis range or in a corner with a label like "1,000 k". Every chart then scales against the same reference, and a bubble of the same revenue has the same size in every edition. The same idea helps when one chart shows several groups whose largest members differ greatly: the reference keeps the small group from being scaled up to look as important as the large one. Mention the reference in the chart title or a note, so that nobody mistakes it for a real product.

Common pitfalls

  • Swapped arguments. values is vertical, xvalues horizontal, zvalues size.
  • Sizing by width. Use sizeRepresents = "area" so large values are not exaggerated.
  • Non-contiguous groups. Sort rows by group so each series references one block.
  • Negative bubble sizes. Sizes must be positive; Excel hides or misdraws bubbles with negative size values.
  • Missing axes in Excel. Set delete = False on both axes.
  • Too many labelled bubbles. Label only the few that matter when there are many points.

Performance notes

Each series is a small XML element; a chart with a series per product is fine for dozens of products. Hundreds of bubbles are unreadable before they are slow — aggregate to groups, or use a scatter chart with small markers.

Conclusion

Lay out one column each for the x position, the y position and the size, sorted so each group is contiguous, and add a Series(values=..., xvalues=..., zvalues=..., title=...) per group to a BubbleChart. Size bubbles by area, adjust bubbleScale, fix the axis ranges, keep axes visible with delete = False, and move labels to the bottom when values go negative. To name every bubble, give each item its own series, colour it by group and show series-name data labels.

Frequently asked questions

How do I make a bubble chart with openpyxl? Create a BubbleChart and append Series objects built with Series(values=y_ref, xvalues=x_ref, zvalues=size_ref, title=...), where each argument is a Reference to a column of cells. Place the chart with ws.add_chart and set both axes' delete to False.

Should bubble size represent area or width? Area. With sizeRepresents = "area", a value twice as large gets a bubble with twice the area, which is how people perceive size. Sizing by width makes large values look far bigger than they are, because the area grows with the square.

How do I label each bubble with its name? openpyxl cannot label points with text from cells. Put each item in its own series titled with its name, and turn on data labels that show the series name. With a handful of bubbles this reads well; with many, labels overlap.

How do I colour bubbles by group? Add one series per group — region, category, segment. Each series gets its own colour and legend entry.

More on charts & images

More on charts & images →