How to Make a Bubble Chart in Excel With Three Measures

A bubble chart in Excel shows three measures in one picture: where a bubble sits across (X), how high it sits (Y) and how big it is. You need three columns of numbers, one click on the Insert tab, and a few settings for labels and size. This guide is for anyone comparing groups, such as regions, products or campaigns, on more than two numbers at once.

What a bubble chart shows#

A bubble chart is a scatter chart where each point also has a size. It works best when you compare a handful of groups on three measures and want to spot the outliers:

• X axis: one measure, such as ad spend.
• Y axis: a second measure, such as revenue.
• Bubble size: a third measure, such as the number of orders.

Use it for 3 to 15 bubbles. With more, they overlap and nobody can read them. If you only have two measures, a plain scatter plot is clearer. If you have one measure per group, a bar chart is easier to read than any bubble.

How to set up the data for a bubble chart#

Lay out one row per bubble and three number columns in the order X, Y, size. Keep the names in their own column to the left:

Three rules prevent most problems:

1. Numbers only in columns B to D. A number stored as text (often left-aligned) breaks the chart.
2. No totals row. A Total row becomes a giant bubble that dwarfs the rest.
3. Choose the size measure carefully. It should be positive and roughly comparable. A size that is just X times Y repeats information the chart already shows.

How to make a bubble chart in Excel, step by step#

Select the numbers, insert the chart, then check which column went where.

1. Select B2:D5: the three number columns, without the names or the headers.
2. Go to Insert > Insert Scatter (X, Y) or Bubble Chart and choose Bubble. The same button is on the Insert tab in Excel for Mac.
3. Excel uses the first column for X, the second for Y and the third for size.
4. If anything looks wrong, right-click the chart, choose Select Data, pick the series and click Edit. Set Series X values to B2:B5, Series Y values to C2:C5 and Series bubble size to D2:D5.
5. Add axis titles from the Chart Elements button (the + next to the chart on Windows, or Chart Design > Add Chart Element on both Windows and Mac). Label them "Ad spend" and "Revenue", and say in the chart title what the size means, for example "Bubble size = orders".

Avoid the 3-D bubble option. The shading makes sizes harder to compare. For general chart steps, such as moving and resizing, see Excel charts for beginners.

How to add labels to the bubbles#

Excel labels bubbles with numbers by default, but you usually want names. Use labels from cells:

1. Click the chart, then Chart Elements > Data Labels > More Options.
2. Under Label Contains, tick Value From Cells and select A2:A5.
3. Untick Y Value so only the region names show.
4. Pick a label position such as Center or Right.

Value From Cells is available in Excel 2013 and later, including Microsoft 365. Now the chart reads at a glance: East spends the most and earns the most, while West earns 47,000 from only 3,000 of spend but has the fewest orders.

How to control bubble size#

Right-click a bubble and choose Format Data Series to change how size is drawn:

Keep Area. With area, a region with twice the orders gets a bubble with twice the area, which matches how people judge size. With width, doubling the value doubles the width and makes the bubble look four times larger. If bubbles overlap, lower the scale to 50 or 60, or add some transparency to the fill so smaller bubbles show through.

Build the chart data from raw rows with SUMIFS and COUNTIFS#

Real data usually arrives as one row per order, not one row per region. Summarize it first, then chart the summary. With orders in a list where B holds the region and C the revenue:

On the seven orders in this page's exercise, West has 3 orders worth 2,800 in total, so the average order is about 933. Put the region names in G2:G4 and fill the formulas down. The $ signs keep the source ranges fixed. The SUMIFS function guide and the COUNTIFS function guide explain each argument, and how to sum by category shows other ways to build summary tables. Because the summary uses formulas, the bubbles update when new orders arrive.

Common bubble chart problems#

Most bubble charts go wrong in the data, not the chart settings:

• X values show as 1, 2, 3. You selected the text names as the first column. Excel can't use text as X values, so it numbers the points instead. Select only the numbers.
• One series per column. If Excel draws several series, it read the columns differently. Use Select Data to set X, Y and size for one series.
• Huge bubbles hide small ones. Lower the scale, add transparency, or sort the data so the largest bubbles come first and are drawn underneath.
• Different units on the axes. That's fine for a bubble chart, but if you really need two Y measures over time, use a combo chart with a secondary axis instead. For a single measure over time, a line chart is better.

Practise it#

A good bubble chart starts with a clean summary table. Practise building a pivot-style summary with formulas, then practise COUNTIFS and SUMIFS with several conditions. You type each formula and it checks it instantly. Or total the West revenue in the exercise on this page, the first number you'd need for its bubble.