Bubbles

Like my blog on x y scatter graphs, I have posted today because I came across a problem that has taken me some time to solve and I hope you will save time by reading this.

A chart with bubbles to show the size of a market can be a very impactful way of showing data especially if combined with a map (click any of the pictures to enlarge them).

My difficulty arose from the following question: by how much do you adjust the width of a bubble. If you want to show a different sized market.

The answer is to use the following formula which you can cut and paste into excel.

= (diameter/2*factor for the % change)*2

Steps

The diameter is the width of your bubble. If you are using Powerpoint left click on the bubble, select "format shape", then "size" and this will give you the width. This is equal to the diameter.

Work out how much bigger or smaller you want your bubble to be in percentage terms.

Then select the relevant “factor for the % change” in the table below.

Two Examples

I have a bubble with a width of 10cm. Don’t worry about the maths but this has an area of 78.55 cm2

I want to create a bubble that is 20% bigger

Therefore I put the width diameter into the formula as follow

=10/2* factor for the % change

I then look at the table and see that if I want a bubble 20% bigger the factor I need to use is 1.095445

=(10/2*1.095445)*2

The width of your new circle should be

10.95445

The area of this circle will be 94.26 cm2, which means it is 20% bigger.

Another example goes to prove the method

I have a bubble with a width of 10cm. I want to create a bubble that is 40% small

=10/2*factor for the % change

I then look at the table and see that if I want a bubble 40% smaller the factor I need to use is 0.774597

=(10/2*0.774597)*2

The width of your new circle should be

7.74597

The area of this circle will be 47.13 cm2, which means it is 60% the size of the 10cm with (78.55 cm area) bubble.

If you don’t want to do the maths you could also just plug in the number to the attached spreadsheet.

Use this to do the maths - You do not have permission to view this object.

I hope this helps! As normal if you have any suggestions for improvement to the method please don’t hesitate to get in touch.

The "factor for the % change" table is below: