Indzara

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.

Motion Bubble Chart and Scatter Plot

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.

Enable Macros
Enable Macros

Define Variable names

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

Rename field names if 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.

X axis variable

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

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

Features of Motion Bubble Chart

Play, pause or reset animation with buttons

Animation Controls
Animation Controls

Control animation speed

Animation Speed control of motion bubble chart
Animation Speed control of motion bubble chart

Control motion manually with slider

Manual slider control of Bubble charts
Manual slider control of Bubble charts

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

Data labels toggle
Data labels toggle

Step through motion

Increase step control (years)
Increase step control (years)

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

Switch between Bubble Chart and Scatter Plot
Switch between Bubble Chart and 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:

5 Comments

  • I tried your bubble chart which looks and works great, however, above each bubble i get [CELLRANGE] instead of the country. I think there is something amiss with the data labels, I have been unable to ascertain where in the script this occurs. Any help would be greatly appreciated.
    thanks
    Joe

    Reply
  • Hi, this is a very useful page and template! Thank you for taking the time to make such an elaborate tutorial.
    To add on, is there any way for us to add trendline (for each series) on top of the motion chart for easier comparison over the years?

    Reply
    • Thank you for showing interest in our template and sharing your valuable feedback.

      I understand your concern but adding a trend line for all the data will be too complicated.

      Best wishes.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *