XLSTAT: Bar Chart Race in Excel

 

Background

To visualize values of a variable for different groups (categories), we often user bar charts. The height of each bar represents the value of the measured variable for a given group. However, if the variable is measured at different times, the classical bar chart cannot be used to show the evolution over time. This is where the bar chart race comes in handy. It allows to visualize the evolution of a variable over time on a single dynamic chart.

 

Dataset for Example

The (attached) dataset represents the Grand Slam victories par tennis player and per year since 1970. The Number of Grand Slam victories variable gives the total number of Grand Slam titles the player has won. The Date variable gives the date of the tournament and the Name variable gives the winning player.

Due to a lack of data, some players who won Grand Slam tournaments before 1970 were not included. For example, Margaret Court, who holds the record for the number of Grand Slam victories (24 titles), does not appear here.

The goal here is to show the evolution of Grand Slam winners through a dynamic chart over time for the given period (1970-2019).

 

Setting Up

• Open XLSTAT.

• Click on the Visualizing data menu and select the Bar chart race feature:

• The Bar chart race dialog box appears.

In the General tab:

• Select the B column which corresponds to the variable Number of Grand Slam victories in the Data field.

• Select the C column in the Time field.

• Select the A column, containing the names of players, in the Groups field.

In the Options tab:

• Choose to display the bars in a way that the smallest bar is at the bottom of the graph at the end of the animation.

• Choose to color the bars by checking the color scale field.

• Display the raw data and not the cumulated data, as the original data represents the total number of Grand Slam titles held by player. 

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Configuring the Animation

The Bar chart race below was paused in 1996 and shows the results per player for this year.

The scrollbar at the bottom of the chart can be used to change the value of the Time variable and thus display the positions of the bars at a specific date.

At the top left of the chart four buttons are available. You can move forward or backward over time using the play and back buttons, and stop the animation using the pause button. The fourth button on the right allows you to change the speed of the animation (1 very slow - 10 very fast) over time.

 

Interpreting the Results

This animation shows the evolution of the most titled players over time. For example, we see the domination of Björn Borg during the 1970s. Between the mid-1980s and the mid-1990s, Steffi Graf emerged. Finally, at the beginning of the 2000s Serena Williams and Roger Federer appear as the tennis players with most titles with these two being often neck and neck. From 2015, Serena Williams takes the lead of the classification.