Animated Graphs in PowerPoint

Charts and graphs are used to tell a story to an audience. And as with any good tale, you may want to parcel out one chapter at a time. There are lots of tools to help you get the job done, but today we're just going to look at moving your graphs into PowerPoint.

Last year, one of the presentations I was giving was about using cell phones in the classroom. At the beginning of the presentation, I was sharing some data about how students access the Internet. I had pulled data from two different Pew Internet studies. One had data comparing access to computers and cell phones, based on family income. The other had data reporting how teens used cell phones to access the Internet, also based on family income. There is a bit of a surprise in the data, so although I wanted to show the big picture, I wanted to portion it out so people would get the "A-ha!".

The easiest way to do this is to build three different slides: one for the first data set, one for the second, and one for both. But why do it the easy way? Instead, you can use some simple animation to reveal each piece of the puzzle.

Start by building the graph. I prefer to do this in Excel and then copy it into PowerPoint. Here is the full meal deal slide:

Now, I'm ready to add animation. At the beginning, I don't want the blue bars to show. The orange and green show what you might expect. As household income increases, so does access to a computer. Access to a cell phone is similar, but not as dramatic. To make the orange and green bars show first, select them and go to the animation tab. Then, select "By Series" under "Effect Options." I don't recommend fancy animations. Just because you can add spins and flashes doesn't mean you should. A simple fade is plenty.

This will make the first view people have of the slide look like this:

Then, "Add Animation" for the next series (the blue one) to appear...and for the orange series to disappear. Ah, now we can see something interesting: Students from lower income families use their phones the most for going online. (PS---don't you love how the green bars don't look like they're the same shade as the ones above? They're the exact same, but it's a good example of how colour is influenced by context.)

Finally, bring the orange series back for the final comparison.

If you want to see the finished product, you can use the embedded view below. The vagaries of SkyDrive mean that you will need to choose the full screen option to make the animations work. Once it opens, just use your right arrow or page down key to view the results.

Google Motion Graphs

Many of you may be familiar with the work of Hans Rosling---perhaps one of his TED talks or perhaps the Gapminder tool, a product of the foundation he helps to lead.

If you haven't seen the tool, it is a graph which allows you to plot five variables (one of which is "time") and then set the graph in motion. It can give you a very powerful look at the interaction between different factors. Gapminder does provide a desktop format you can use to view some of their selected visualizations.

But what if you want to build your own, using your own data? You're in luck. You can create your own motion graphs using Google Docs. Let's look at an example using state level science test results for the past 8 years.

You will need to organize your data in a specific way. First of all, you must include some sort of time period. This means that your main category, such as the name of a school district, will appear multiple times. The time period does not have to be a year---it can be any length of time you specify. In this example, however, testing is done annually. Next, you need at least three additional columns of data. The motion graph will plot up to four. The graphic at the right does not show all of the points I included, but here's a list:

• Number Tested: Number of Grade 5 students tested for that year
• % Met Standard: Percent of Grade 5 students who "passed" the test
• % Level 1, % Level 2, % Level 3, % Level 4: Percent of students who scored in each of the four evaluation categories (Below Basic, Basic, Proficient, Advanced). Levels 3 and 4 should equal the percent who met the standard
• Enrollment: Total number of students in the district
• % Non-white, % White
• % Male, % Female
• % SPED: Percent of students (district-wide) receiving special education services
• % F/R Lunch: Percent of students receiving free or reduced lunch; used as a measure of poverty
• % Teachers Post-bac: Percent of teachers (district-wide) with a masters degree (or higher)

 Once you have your data organized, you will choose "Insert" and then "Gadget" from the toolbar. Then, select the "Motion Chart."

You can choose to place the chart on the same page as the spreadsheet or on its own page. Now, you're ready to rock and roll. Here is one configuration:

All of the circles you see represent school districts in Washington state. Not every district is shown. Some districts have such a small enrollment that it violates privacy laws to publicly report their data. But most districts---ones with a consistent enrollment of ~150 students---are plotted. On the x-axis, we have the variable for meeting the standard on the Grade 5 science test. On the y-axis, we see the % of students in each district qualifying for free or reduced price meals. Already, we don't see a lot of surprises. The "richer" the population, the better they perform on the test. The colour of the circle relates to the percent of non-white students. Here again, not too many surprises: districts with a fewer minority students do better. The size of the circle relates to enrollment. In the sample above, I've hovered over Seattle, the largest district in the state. Note that the figures associated with each variable also appear: 32.8% met the standard; 41% F/R Lunch; 59% non-white; 47,546 students enrolled.

When I press the play button, I can watch the graph change over time. What do things look like in 2011?

First of all, we're a little more spread out in terms of scores, but most districts are performing better. Seattle is about the same size with the same % F/R Lunch, but has increased its percent of non-white students. In fact, you could make that same comment about ethnicity changes for most of the state.

Motion graphs can be very interesting tools, especially for data sets that need a big picture. This isn't to say that it wouldn't be intriguing to plot student grades in one, but some of the organization required means it's not an everyday tool.

But hey, why don't you play with one for yourself? Use this link to go directly to the Google Spreadsheet with this motion graph (then make a copy to your own account, if you'd like to edit it). Or, use the embedded version below (not available via RSS).