Motion charts tutorial in Excel

This tutorial shows how to create a motion chart in Excel, in the shape of a gif format, using the XLSTAT software.

Dataset to create a motion chart

The data corresponds to three variables, namely the life expectancy, fertility rate and the population size, measured in five European countries (France, Spain, Italy, Great Britain, Belgium). Data have been collected each year from 1960 to 2011.

Usually, when one wants to visualize the evolution of a variable over time, the value of the variable is plotted on the Y axis as a function of time on the X axis. With this type of representation, you are limited to the evolution of only one variable at a time. If more than one variable on different observations are to be explored, several charts are needed. The XLSTAT Motion Chart solves this issue by allowing you to explore the evolution of several variables (up to 3) measured on different observations over time on a single dynamic chart.

In our tutorial example, we will visualize the evolution of our three variables, the life expectancy, the fertility rate and the size of population over time for each country.

Setting up a Motion Chart

Select the XLSTAT / Visualizing data / Motion Chart command, or click on the corresponding button of the Visualizing Data toolbar (see below). Once you have clicked on the button, the Motion Chart dialog box appears. In the General tab, select the data on the Excel sheet as following: - X: Fertility Rate;

• Y: Life Expectancy (in year);
• Time: Year;
• Groups: Country
• Size: Population (in millions)

The option Interpolate missing points allows to estimate coordinates of missing points as the mean of previous and next coordinates. For example, you can see on rows 8 and 9 that there is no data for France in 1970. If the Interpolate missing points option is activated, the 1970 coordinates for all the variables will be the mean of 1969 and 1971 coordinates. On the contrary, if the option is disabled, no point is displayed.

The option Smooth allows to display intermediate positions of points between two consecutive dates in order to produce a smoother dynamic evolution between time steps.

The option Legend displays a legend with the minimum and maximum value of Size according the size of points.

Playing with Motion Charts in XLSTAT

After you have clicked on the OK button, a chart is displayed on the new sheet (because the option sheet was selected in the Range option for outputs). For more readability, this chart has been obtained by setting options of chart size to 600 x 250 in the XLSTAT options.

Bubble size is proportional to the Size variable (Population in our case) and is a way to represent a third dimension on our two-dimensional plot. The time dimension is represented by changing dynamically position of points over time. In order to do that, you can use different buttons. The scrollbar at the bottom of the chart allows to change values of the time variable and thus display positions of points at different years.

At the top left of the chart you can see four buttons. They are used to automatically adjust values of time and see points moving over time. You can move forward or backward with the play and back buttons, and you can stop time by clicking on the pause button. The fourth button on the right allows you to set the speed of movement (from 1 very slow to 10 very fast) of the points over time.

Interpreting a Motion Chart

You can see on this animation the three variables evolving for each country over time. We clearly see that for the five countries, from 1960 to 2011, the fertility rate decreases over time while the life expectancy increases. Furthermore, by looking at the size of points, we can see the steadiness of the population size in each country.