Motion Bubble Chart & Scatter Plot – Free Excel Template

Data analysts use data to discover answers to questions and then communicate the findings to the audience effectively so that the message reaches the audience clearly, correctly and quickly, leading to actions that improve outcomes.

Motion Charts or Animated Charts allow us to express the data in ways that a static chart does not.

A motion chart allows us to show the direction and the speed of the change in variables.

Download

Video Demo

How to create motion bubble charts?

Enable macros

When you download the template and open, you may see this warning message. The template uses three simple macros (which are provided later in this article below) to animate the chart. Please click on ‘Enable Content’ to continue.

Define Variable names

In the Start sheet, rename the three variables as needed.

X axis variable will be the independent variable and Y axis variable is the dependent variable. Size variable represents the size of the bubble.

Enter Raw data

Raw data for three variables are entered in the three sheets X, Y and Size.

For each of the three sheets, column A represents the observation names. In this example, since each data point is a country, we enter country names here.

Column B to BI are the years (60 columns mx). You can enter fewer years if you need.

When copy pasting from other sources into this sheet, please make sure that you paste as values.

Please do not delete or insert columns in these sheets.

The order of the countries in the three sheets do not have to be the same. However, the spellings need to match.

Enter Series data

The template supports single series and multi-series bubble charts and scatter plots. You can enter up to 6 series of data.

Name each series as needed and enter the values in each series. These names should be present in the the X. Y and Size sheets.

This type of design allows us to explore the data by just modifying the series without touching the raw data in those three sheets.

Chart Sheet controls

Play, pause or reset animation with buttons

Control animation speed

Control motion manually with slider

Control Data Labels (X, Y, Size and Name)

Step through motion

If you would like the chart to animate 1 year at a time, or multiple, you can set it up here.

Switch to Scatter Plot

You can switch between a scatter plot and a bubble chart with a single click. When equal size is chosen, the chart becomes a scatter plot with all the bubbles equal size.

Formatting tips

Please watch this video on how to create a bubble chart and change formatting options in Excel.

Recommended Templates: