How to Create a Bubble Chart in Excel
Use a Bubble Chart to Analyze Data With Three Dimensions
A bubble chart is similar to a scatter plot. However, a bubble chart displays three dimensions of data instead of two. The third dimension is displayed by replacing data points with bubbles and using the size of the bubbles to represent the values in the third set of data. In the example below, calories is displayed on the X axis (2250 is plotted as 2.25), weight gained is displayed on the y axis and the size of the bubble represents age.
Bubble Chart Data
To Create a Bubble Chart in Excel
Just select your data, then click on the Insert tab, then Other Charts, then Bubble. There are two choices of bubble shapes.
Should I Use a Bubble Chart or Scatter Plot?
Bubble charts can be hard to read and are almost impossible to understand when you plot more than a handful of data points and/or the bubbles start to overlap each other. If we use the same dimensions above, but plot 14 data points instead of 5 data points, the bubble chart is difficult to read. In contrast, a scatter plot will clearly show the relationship between two dimensions, calories and weight gained, but does not display the dimension of age.
Use a bubble chart when: comparing three dimensions of data, you have a small # of data points, none of your values are negative.
Use a scatter plot when: you want to know the relationship between two data points, you have a lot of data points, some of your values are negative.
Bubble Chart With
More Than 10 Data Points
QI Macros Scatter Plot Comparing
Calories vs Weight Gained
QI Macros Makes It Easy to Create Bubble Charts and Scatter Charts
Scatter Charts can be created in Excel, however, you have to figure out how to add the trendline and Excel does not calculate R² for you. R² is the correlation of determination and tells you whether there is a strong relationship between your two data sets. If R² is greater than .80, there is a strong relationship between the two data sets. R² close to 1.0 means a perfect fit. QI Macros automatically draws the trendline for you and calculates R².