A generic template to create Motion Charts in Microsoft Excel and 2 examples to animate La Linea episodes in Excel
A preliminary note
In the previous article I published my three entries for Tableau’s current “Viz as Art” contest.
With pride and humility I announce that one of my entries (my replica of Curtis Steiner’s 1,000 blocks) made the cut and is among the 10 finalists:
Destination Data—Viz as Art contest finalists & voting
The voting is open now through Friday, August 29, 2014, 5pm (PST) and takes place on Twitter. So, if you have a Twitter account, please have a look and vote with a tweet.
To be crystal clear: I am not asking you to necessarily vote for my entry. Have a look, see for yourself and decide which entry you like most. It goes without saying that it is your decision who to vote for, but please do vote. Thank you!
So much for the preliminary note, now on to today’s content:
As mentioned above, I submitted three entries, but truth be told, I had a favorite child: the La Linea workbook. Maybe because it was the only one I haven’t published before, maybe because it reminds me of my childhood, I don’t know.
Anyway. As soon as you have your data, it is very easy to create this animation in Tableau. However, this kind of motion chart is possible in Microsoft Excel, too. So I thought it might be interesting to publish an Excel replica of my La Linea Tableau workbook.
Today’s post provides an Excel version of my La Linea Tableau workbook, a generic template to create motion charts in Excel and also an Excel workbook to animate a whole episode of La Linea. Of course, all workbooks are provided for free download.
Motion charts dynamically animate data series in a chronological order, very often as bubble charts. They allow an interactive exploration and visualization of multivariate data sources over time. The prime example of motion charts are the Gapminder visualizations presented e.g. by Hans Rosling in his famous TED talk:
Hans Rosling shows the best stats you have ever seen
The Pages Shelf in Tableau enables you to easily create fantastic motion charts. However, motion charts can be done in Excel as well. A certain set up in the worksheet and some VBA code necessary, but no rocket science.
The Template and the Implementation
The heart of a motion chart in Excel is one single cell, containing the current value of the chronological measure, i.e. usually the year or month. This value is incrementally increased or decreased during the animation by some very simple VBA code snippets.
Based on the current value, the data displayed on the motion chart (whatever type it may be) can easily be consolidated with some simple formulas like INDEX, OFFSET, SUMIF, etc. depending on the structure of your data source.
I created a very simple generic template which can be used as a basis for a motion chart. The workbook contains only one worksheet:
Here are the main components of this template:
First, what is on the worksheet?
- A named range for the chronological value called myCurrentValue
- Two named ranges (myMin and myMax) to define the minimum and maximum value of the chronological value, i.e. where to start and stop the animation. In a business-like use case, this would usually be the first and last year in the data
- A named range (myDelay) defining the time in milliseconds the VBA code waits before it increases or decreases the current value
- A Boolean variable (named range: myLoop) defines if the animation shall loop or not. Looping means if the current value reaches the maximum, the animation doesn’t stop, but starts over again at the minimum value
- 7 play button icons:
From left to right; go to start, fast backward, backward, stop/pause, forward, fast forward, go to end. Different VBA subs will be executed when one of the icons is clicked.
Next, the VBA code.
- A global Boolean variable defines if the animation is running or stopped
- The 7 subs assigned to the 7 play buttons have only one or 2 lines of code, either increasing or decreasing the current value, stopping the animation and setting the current value to the minimum or maximum value.
- The heart of the VBA code are the private subs IncreaseCounter and DecreaseCounter performing the loop of the animation. Both subs have only 15 lines of code each, so I think I don’t have to go into the details.
That’s it.
With this template, you have an easy-to-use generic basis to implement any kind of motion charts in Excel:
Download Animation with Excel Template (Microsoft Excel 2007-2013, 82.9K)
A Fun Example: Animating a short Extract from La Linea 211
Here is the Excel replica of my Tableau “Vis as Art” contest entry based on the template provided above:
- The animation control parameter is the number of the image
- The chart is a simple standard XY scatter chart, minimized to the maximum, i.e. no axes, no title, no legend, no gridlines. Just the data points and a blue background
- The number of data points per image vary, so we need a way to dynamically change not only the data points, but also the size of the data ranges to be displayed. The table on the data worksheet is sorted ascending by image.
This way, we can calculate the starting point of the current image with a simple MATCH formula. The size of the data range (the number of rows) to be displayed is defined by a COUNTIF function. See the cells “myStartRow” and “myRows” on the Control worksheet - Based on the results of “myStartRow” and “myRows” we can easily create a dynamic range for the X and Y values to be displayed depending on the current image. See the two named formulas “myXPlot” and “myYPlot”, used as the data source of the XY scatter chart
That’s it. With a few simple changes, we built this short animated sequence of La Linea 211 based on the Excel animation template described above.
Here is the workbook for free download:
Download La Linea 211 (Microsoft Excel 2007-2013, 4.5 MB)
How about a full Episode? Is this possible, too?
The 56 images of the extract of La Linea 211 (see above) already need a data source of almost 300,000 rows on the data worksheet. At first sight there seem to be no way to animate a full episode (usually 2 to 3 minutes long) in Excel.
Truth be told, there is actually no way of holding all the data in a stand-alone Excel file. At least not with a reasonable file size. However, you can hook up the Excel workbook to an underlying database (Microsoft Access, for instance) and with a little extra VBA code, you can make Excel load the images on the fly, i.e. one by one:
La Sexilinea is one of the longer La Linea episodes (4:45). I transferred the whole episode to a database which can be displayed on an XY scatter chart and ended up with almost 2,500 images and more than 14 million rows. As I said, no reasonable way to hold the entire data directly in Excel. Thus, I used Microsoft Access as the data source and let Excel fetch the data image by image.
On the Excel worksheets, there are only 2 changes necessary: two additional named ranges on the Control worksheet:
- The cell “myDatabase” contains the name of the Access file (including the extension, i.e. .mdb or .accdb).
- The cell “mySQLQuery” has the SQL statement to retrieve the current image data from Access. This is the formula to create the SQL statement:
="SELECT X, Y FROM la_sexilinea WHERE Image="&myCurrentValue
where “la_sexilinea” is the name of the table with all the data in Access.
So far, so good. All we need now is a VBA code snippet which fires this SQL statement to Access and retrieves the data for the current image. 20 lines of code, so not much to explain here. If you are interested, download the Excel workbook and have a look at the module modGetData. The sub NextImage of this module is then called in the subs of the module modAnimation each time the current image changes.
Here is the Excel workbook for free download
Download La Sexilinea (Microsoft Excel 2007-2013, 381K)
The underlying Microsoft Access database with 14 million rows has 366 MB and I didn’t want to post this file for download for obvious reasons. Instead, I exported the data into a text file. This is the zipped version of the file, but please still mind the file size:
Download CSV Data Source La Sexilinea (zipped text file, 21.3MB)
If you want to see the whole episode in Excel, you have to execute the following steps:
- Download the Excel workbook and the zipped text file to a folder on your computer
- Unzip the zipped text file to the same folder
- Create a new Access database in this folder and import the text file (field separator is a semicolon) into a table called “la_sexilinea”
- Open the Excel workbook and insert the full name (including the file extension) of the Access database in cell C14 of the Control worksheet (named range “myDatabase”) without the path of the file
That’s it. If you now go to the first sheet in the workbook, you should be able to watch the episode using the play buttons. Since the workbook retrieves the images one by one, the animation will be slower than in the workbook of La Linea 211. You may want to decrease the delay parameter or use the fast forward / fast backward buttons.
Acknowledgements
I did this before in the previous post, but I do want to thank Roberto Mensa of the FrankensTeam again for his idea of visualizing a short La Linea sequence.
In any case, I highly recommend to check out the Excel Charts Gallery site of the FrankensTeam.
They have amazing Excel Charts for free download there. Not all of them are directly applicable to business life requirements, but all of them provide a variety of very innovative and interesting ideas. A treasure room of Excel Chart workbooks. Please go and have a look!
What’s next?
The next article will put this template to some real use: a replica of the Gapminder visualization in Microsoft Excel. As always, including the workbook for free download.
Stay tuned.