How can you control the relative bubble size in a Google Sheets Bubble Chart?

Question

When I make a Bubble Chart in Google Sheets, the bubbles are not the correct size relative to one another, leading to false impression about the data. How can I make it so the area of each bubble is proportional to the size of the data it represents?

Answer 1

Google Sheets doesn't give direct control over bubble sizes in a bubble chart. As near as I can tell, bubble sizing is determined in the following way:

Find the largest item, set this to display at the maximum bubble size (displays with a diameter of about 62 pixels). Find the smallest item, set this to display at the minimum bubble size (displays with a diameter of about 12 pixels, or ~1/5th the diameter largest bubble, or ~20%). To find the area scale factor, we square the scale factor, getting ~1/25th the diameter, or ~4%, This means that effectively, the visual spread between the smallest and largest bubbles is always a factor of 25 (unless all values in the bubble chart are the same, in which case they are all shown at the max size). Next, all in-between values are given a bubble with a radius that is scaled linearly between the largest and smallest elements.

This leads to various problems:

1. We can't set the maximum bubble size
2. We can't set the minimum bubble size
3. Bubble sizes are not correct relative to one another.

To illustrate that last point, if your data set is as follows, compare the expected radius with the actual radius, and the actual frequency with the displayed area:

Data Set 1

data	freq	expected radius as % of max data point	displayed radius as % of max data point	displayed area as % of max bubble
(0,0)	0	0	20%	4%
(1,0)	1	10%	20.8%	4.3%
(2,0)	4	20%	23.2%	5.4%
(3,0)	9	30%	27.2%	7.4%
(4,0)	16	40%	32.8%	10.8%
(5,0)	25	50%	40%	16%
(6,0)	36	60%	48.8%	23.8%
(7,0)	49	70%	59.2%	35.0%
(8,0)	64	80%	71.2%	50.7%
(9,0)	81	90%	84.8%	71.9%
(10,0)	100	100%	100%	100%

Some areas are too large, and some are too small, but it might not be very obvious with this example.

Data Set 2

The situation is made much worse when your minimum data point is larger relative to the maximum data point. Consider the same set of data from above, but removing data points 0-4. Naïvely, one might expect bubbles 5-10 to be the same size as bubbles 5-10 above. While bubble 10 stays the same size, look at what happened to bubble 5!

data	freq	expected radius as % of max data point	actual radius as % of max data point	displayed area as % of max bubble
(5,0)	25	50%	20%	4%
(6,0)	36	60%	31.7%	10.1%
(7,0)	49	70%	45.6%	20.8%
(8,0)	64	80%	61.6%	37.9%
(9,0)	81	90%	79.7%	63.6%
(10,0)	100	100%	100%	100%

Because the ratio between the area of the largest and smallest point is always 25, if your data has a smaller ratio between max and min, you can only get a correct visualization by introducing a fake max or min. If your data's spread is larger, you have to either remove some of the data, or cap the display size of some of the largest or smallest data points. Even if your spread randomly happens to be exactly 25, the in-between points will still have the wrong values.

Here's what we can do to fix this, assuming our data is structured as follows:

	A	B	C
1	x	y	freq
2	5	0	25
3	6	0	36
4	7	0	49
5	8	0	64
...	...	...	...

First, insert a 0 data point as an anchor for the minimum size:

	A	B	C
1	x	y	freq
2	0	0	0
3	5	0	25
4	6	0	36
5	7	0	49
6	8	0	64
...	...	...	...

Second, add a re-scaling formula to column D (any values below the 4% threshold we will display at the minimum size):

=(MAX(sqrt(C2/max(C$2:C))-0.2,0)/0.8)^2

	A	B	C	D
1	x	y	freq	bubble size
2	0	0	0	Formula
...	...	...	...	...

Next, add a category column to column E:

=if(C2=0," ",if(C2=0,"<=",">")&maxifs(C$2:C,D$2:D,0))

	A	B	C	D	E
1	data	x	y	bubble size	category
2	0	0	0	Formula	Formula
...	...	...	...	...	...

Then when you create your chart, set the category to column E, and the bubble size to column D.

Data Set 1:

Data Set 2:

This leaves an extra bubble in the legend and on the graph, so lastly, while we can't totally get rid of it, we can change the series color of your anchor point to white. If you want, you can change the colors of the other two series as well.

Data Set 1:

Data Set 2:

In this chart, the red bubbles are larger than they should be, and are set to the minimum allowable size. The blue bubbles are all correctly scaled relative to one another.