Free Practice WorkbookBubble charts let you compare three different variables in a single chart. You can display relationships between values using the X-axis, Y-axis, and the size of each bubble. In this article, you will learn how to create a bubble chart with 3 variables on a chart.
Key Takeaways:
- A bubble chart is ideal for displaying relationships among three variables.
- X and Y axes are two variables of the bubble chart.
- The size of the bubble is the third variable.
- It is a great tool to compare datasets and visualize relationships.
- Add a label and formatting to easily interpret the chart.
Table of Contents
What is a Bubble Chart?
Bubble Chart is used to visualize data with three dimensions. Instead of plotting just two variables (x and y) in a traditional chart, Bubble Chart lets you add a third variable as well. The first variables are visualized as coordinates, the third as the size of the bubble.
This chart can be used when you have a third value that can be used to determine the relative size of the bubble. Higher the value, the bigger will be the size of the bubble.
For example, if you have a Scatter Chart that shows the relationship between the age of a house and its proximity to the city and want to add the value of the house (the 3rd variable), then a Bubble Chart will get you there.
How to Create a Bubble Chart?
Example 1:
In the table, you have the age of the house, proximity to the city (in km) and the value of the house. So, using Bubble Chart with 3 variables, you can plot the age of the house on the X-axis, the proximity to the city is on the Y-axis and the value of the house (the 3rd variable) as the size of the bubble.
STEP 1: Select the table on where we want to create the chart.
STEP 2: Go to Insert > Insert Scatter Chart or Bubble Chart > Bubble.
STEP 3: Click on Series1 and Click Delete to remove it.
The horizontal axis contains negative values as well. Since X-axis represents the age of the house, it cannot be negative. Formatting this axis to restrict negatives value is a good idea to avoid confusion.
STEP 4: Right Click on the axis and select Format Axis to open the Format Axis Panel.
STEP 5: Under Axis Options > Minimum >Set the value to 0.
This is how the chart will look:
Example 2:
In this data table, you have project-wise data including cost, profit, and probability of success.
STEP 1: Select the table on where we want to create the chart.
STEP 2: Go to Insert > Insert Scatter Chart or Bubble Chart > 3D Bubble.
STEP 3: Add the horizontal and vertical axis title.
As you can interpret from this chart that even though Project 2 has the maximum profit the chance of success is low. So, it is better to opt for Project 5 which has a high probability of success and high profits as well.
Formatting a Bubble Chart
Add Axis Title to the Bubble Chart
STEP 1: Select the Chart
STEP 2: Go to Chart Design > Add Chart Element >Axis Titles > Primary Horizontal
STEP 3: Type Age of House.
STEP 4: Go to Chart Design > Add Chart Element >Axis Titles > Go to Primary Vertical.
STEP 5: Type KMs to City.
Add Colors to the Bubble Chart
STEP 1: Right-click on a bubble and click on Format Data Series.
STEP 2: In the Format Series Panel, Select the Fill icon.
STEP 3: Check Vary colors by point.
STEP 4: Your desired Bubble Chart with 3 variables is ready!
Add Data Labels to Bubble Chart
STEP 1: Select the Chart
STEP 2: Go to Chart Options > Add Chart Elements > Data Labels > More Data Label Options
STEP 3: From the Format Label Panel, Check Value from Cell
STEP 4: Select the column Project
STEP 5: Uncheck Y value.
This is how the chart will look:
Pros and Cons of using Bubble Chart
Advantages:
- It lets you add a third dimension to the data set.
- It can be used to depict the relationship between the data sets.
- It can be used to convey a lot of information at once.
Disadvantages:
- Too many bubbles can make the graph unreadable.
- Overlapping bubbles may create a hindrance.
FAQs
What is a Bubble Chart in Excel?
A Bubble Chart displays three variables using the X-axis, Y-axis, and bubble size. It is used to compare three different variables on a single chart.
What does the bubble size represent?
The bubble size represents the third variable in the dataset.
When should I use a Bubble Chart?
You can use the bubble chart to compare three related values and identify patterns easily.
Can I add labels to the bubbles?
Yes, you can add data labels to show names or values for each bubble.
What is the main drawback of a Bubble Chart?
Too many or overlapping bubbles can make the chart difficult to read.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
