Living Large with Small Multiples

In the poem Song of Myself, Walt Whitman writes "Do I contradict myself? Very well then, I contradict myself. I am large, I contain multiples." And while Excel might not be considered to be as sexy as Whitman's prose, they do have some things in common. They both can contain multiples.

Small multiples use a series of similarly scaled charts. The purpose is to allow for easy comparison across time or groups. When you use these charts, you are looking at the forest and not necessarily the trees. You don't want to focus on details as much as you search for larger patterns to investigate.

I built the example below this week. It's a series of scatter plots. Each tiny blue dot is a student, and their positions on the charts represent the point where their percent attendance and scale score on the state assessment intersect. An orange line shows the linear regression for the data set in the chart. The line tells us a couple of things. It provides a quick visual on the range, as well as the basic trend.

What kinds of things do we notice? Maybe it's how students who score in Level 1, regardless of grade level, don't have much of a discernible pattern. Level 3 students tend to clump---their rates of attendance and scores are very similar to one another. Maybe we have a conversation about those areas where the line slopes downward. How do we explain a trend where the more you come to school, the worse your do on the assessment? Or maybe even the overall picture isn't what we might predict. Even those trend lines that have an upward slope aren't very steep. Wouldn't we think that better attendance leads to better scores? And maybe we need to talk about what's happening when kids get to sixth grade and attendance starts to get a lot more worse for students at all score levels.

Because you likely can't read the itty-bitty labels, I will confess that I have broken a cardinal rule when building this: the y-axes are scaled identically for each grade level, but not among all the grade levels. Percent attendance is plotted along the x-axis and is the same for all of the charts. But the range for scores changes. The higher the grade level, the higher the possible score. I've tried to mitigate this by keeping the y for each grade at about 400 points. If I'd had to make in the entire score range identical for all grade levels, the information represented was too squeezed to make sense of things.

There are thousands of students represented on this single graphic. While focusing on an individual is critical to the daily work of the classroom, small multiples serve a different purpose. This time, it's about the herd.

What will school principals see when I show this to them in a couple of weeks? I'm not sure. I'll have to provide a little support in learning to read it, but I think they'll catch on quickly enough. The chart will be part of a larger conversation around student performance...one piece of a puzzle where they will apply context. As for me, I've enjoyed looking at this because I see something different every time.

Are you using small multiples in your work? How have they been useful?

Bonus Round
To build this, I organized the necessary data and then used a pivot table and slicers to pull attendance and scale scores by grade and score level. Dynamic ranges were used for the charts, allowing for expansion/contraction of the number of data points.

Each chart was pasted into PowerPoint. This allowed me to size and position all of the charts and labels, as well as easily share the document.

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.