XLSTAT: Motion Charts in Excel

 

Background

In Excel, 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 allows you to explore the evolution of several variables (up to 3) measured on different observations over time on a single dynamic chart.

 

Dataset for Example

The (attached) 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.

For the example, we will visualize the evolution of our three variables over time for each country.

 

Setting Up

• Open XLSTAT.

• Click on the Visualizing data menu and select the Motion Chart feature.

• 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 the 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.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Playing with Motion Charts

For more readability, the chart displayed 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 the Results

You can see on this animation below the three variables evolving for each country over time. We see that for the five countries, from 1960 to 2011, the fertility rate decreases over time while the life expectancy increases.

By looking at the size of points, we can see the steadiness of the population size in each country.