Locked lesson.
About this lesson
Create a bubble chart in Excel to display three dimensions of data.
Exercise files
Download this lesson’s related exercise files.
4.07 bubble-charts - Exercise.docx167.2 KB 4.07 bubble-charts - Exercise solution.docx
5.9 MB Exercise - Bubble Chart.xlsx
9.9 KB Exercise - Bubble Chart - Solution.xlsx
17.3 KB Workbook - Bubble Charts.xlsx
14.3 KB Workbook - Bubble Charts - Complete.xlsx
21.5 KB
Quick reference
Bubble Charts
Create a bubble chart based on three dimensions of data.
When to use
We use a bubble chart whenever we want to represent 3 series of data in a chart, for example, income vs population and life expectancy.
Instructions
A bubble chart in Excel is a type of chart that displays three dimensions of data. Each point (or bubble) on the chart represents an observation, with the x and y coordinates representing two dimensions of data and the size of the bubble representing the third dimension. This chart is useful for comparing and visualizing the relationships among three variables.
Create a Bubble Chart
- Select the data in columns D, E, and F only.
- From the Insert tab, in the Charts group, click the drop-down next to Scatter Plot.
- Click Bubble.
Notice the horizontal axis is not quite correct.
- Click on the horizontal axis and press CTRL+1.
- Expand Axis Options.
- In the Bounds area, change the minimum to 0 and the maximum to 75000.
- Click on the vertical axis and press CTRl+1.
- Expand Axis Options.
- In the Bounds area, change the minimum to 40 and the maximum to 100.
Format the Bubble Chart
Add Axis Titles
- Click the plus to the right of the chart.
- Click Axis Titles.
- Type 'Income (Per capita)' into the horizontal axis text box.
- Type 'Life expectancy' into the vertical axis text box.
Add Data Labels
The size of the bubble represents the population of each country. Let's add some data labels.
- Right-click on the bubbles.
- Click Add Data Labels.
By default, Excel will show us the life expectancy as the data label however, we want this to show the country name instead.
- Click on the Data Labels and press CTRL+1 to open the Format Data Labels pane.
- From Label Options, check the box next to Value from Cells.
- In the Data Label Range dialog box, choose the range of cells that contains the country names.
- Click OK.
- From Label Options, deselect the Y Value checkbox.
- Reposition the data labels so each one is visible and does not overlap another chart element.
Change the Color of the Bubbles
Let's change the color of the bubbles according to the continent each country is in.
- Click on the first bubble TWICE.
- From the Format tab, click the Shape Fill drop-down and choose a color.
Repeat this process for all bubbles ensuring that countries that are part of the same continent are the same color.
We can add a Shape Outline around the bubbles if desired.
Hints & tips
- If you cannot see lines joining the country name data label to its corresponding bubble, ensure you have Show leader lines selected in Label Options.
Sorry, we don’t have a transcript for this lesson yet.
Lesson notes are only available for subscribers.