I already wrote a large detailed article about ordinary static bubble charts, so I won’t dwell on the basics now. In short, the bubble chart (Bubble Chart) is, in its own way, a unique type of chart for displaying and detecting relationships (correlations) between several (3-4) parameters. A classic example is a chart showing the wealth of citizens (on the x-axis), life expectancy (on the y-axis), and population (ball size) for several countries.
Now our task is to show, using a bubble chart, the development of the situation over time, for example, from 2000 to 2014, i.e. to create, in fact, an interactive animation:
Such a chart looks very pretentious, but it is created (if you have Excel 2013-2016), literally, in a couple of minutes. Let’s go step by step.
Step 1. Prepare the data
To build, we need a table with data for each country, and of a certain type:
Note that each year is a separate line with the name of the country and the values of the three parameters (income, life expectancy, population). The sequence of columns and rows (sorting) does not play a role.
A common version of the table, where the years go in columns to build bubble charts, unfortunately, is fundamentally not suitable:
You can use a redesign crosstab macro or a pre-made tool from the PLEX add-on to convert such a table into a suitable look.
Step 2. Connect the Power View add-in
All the work of building such an interactive chart will be taken over by the new Power View add-in from the business intelligence toolkit (Business Intelligence = BI), which has appeared in Excel since the 2013 version. To check if you have such an add-on and if it is connected, go to File – Options – Add-ons, select at the bottom of the window in the drop-down list COM add-ins And click the About (File — Options — Add-Ins — COM Add-Ins — Go):
In the window that opens, check that there is a check mark next to power view.
In Excel 2013 after that on the tab Insert (Insert) button should appear:
In Excel 2016, for some reason, this button was removed from the ribbon (even with the checkmark in the list of COM add-ins), so you have to add it manually once:
- Right click on the ribbon, select command Customize the ribbon (Customize Ribbon).
- In the upper left part of the window that appears, select from the drop-down list All teams (All Commands) and find the icon power view.
- In the right half, select the tab Insert (Insert) and create a new group in it using the button To create a group (New Group). Enter any name, for example power view.
- Select the created group and add the found button to it from the left half of the window using the button Add (Add) in the middle of the window.
Step 3. Building a chart
If the add-in is connected, then building the chart itself will take only a few seconds:
- We put the active cell in the table with data and click on the button power view tab Insert (Insert) – A new Power View report sheet will be added to our workbook. Unlike a regular Excel sheet, it has no cells and looks more like a Power Point slide. By default, Excel will build on this slide something like a summary of our data. A panel should appear on the right Power View fields, where all the columns (fields) from our table will be listed.
- Uncheck all columns except Countries и Average annual income – The table automatically built on the Power View sheet should be updated to display only the selected data.
- On the Advanced tab Constructor (Design) click Another Chart – Scatter (Other Chart — Scatter).
The table should turn into a chart. Stretch it around the corner to fit the slide.
- Drag in the panel Power View fields: field Average annual income – to the region X valuefield Lifespan – In Y valuefield Population to the area Size, and the field Year в Playback axis:
That’s it – the diagram is ready!
It remains to enter the title, start the animation by clicking on the Play button in the lower left corner of the slide and enjoy the progress (in every sense).
- What is a bubble chart and how to build it in Excel
- Visualization of geodata on a map in Excel
- How to Create an Interactive Chart in Excel with Scrollbars and Toggles