Saturday, September 24, 2011

Using Add-Ins: Sparklines

In the last post, Jennifer mentioned one of her favourite Excel add-ins: ASAP Utilities. If you don't know what an "add-in" is, it's a little program that works inside Excel. (There are add-ins for other Microsoft products, too.) Some are fee-based and others are free. When you find an add-in that you want, download and place the file(s) in a location you associate with Excel files. You can place the add-in files anywhere, but once you've told Excel to use them, it doesn't tolerate the add-in being moved around.

You will need to tell Excel to use the Add-in. In the Options menu, select "Add-ins":

You'll see a list of ones that can be accessed and used. If you don't see the one you downloaded, hit the "Go" button at the bottom of the window. This will bring up another dialog where you will be able to browse for the add-in and then use the checkboxes to tell Excel to use it.

And now, I'd like to introduce my favourite add-in for Excel: Sparklines for Excel. (It's free!) A sparkline is a "data-intense, design-simple, word-sized graphic," according to Edward Tufte, one of the godfathers of modern data visualization. The idea is that you don't always need a full-size chart or graph to illustrate a point. Small and simple is powerful.

Excel 2010 includes 3 sparkline options, but they don't hold a candle to what the freeware add-in can do. You have the-sky's-the-limit options in terms of using colours and marks. Here's a quick overview of the types of visualizations you can do with your data:

Scales and Performance

I haven't used the Scales very much, but the Performance graphs are amazing. Bullet graphs are something every educator wants...and no one seems to have. These are fabulous for showing student progress. You can divide the block into regions (the sample on the left has dark, medium, and light blue bands) representing below, at, and above standard performance. The black bar in the center can show performance for the first/previous grading period, while the red marks growth (or lack thereof). What an awesome and simple way to communicate with parents. Perhaps a student isn't at standard yet, but we can still honour the progress they make.

Evolution and Comparison
I'm sure you're familiar with the Line graph shown in the Evolution section, but you might not have used Area and Horizon graphs. We'll have a look at these another time. As for the Comparison tools, you can imagine the variety of uses for these. They're great for summarizing student performance, either by examining individuals in a class or assignments across a standard.

Composition, Distribution, and Correlation
This section has some more advanced styles of graphs. You might not have had much call for Treemaps and Heatmaps in your classroom, but you might have used a Box and Whiskers or Scatter Plot. In the coming weeks, we'll spend more time with these types of graphs (along with their full-size counterparts).

Fabrice (the author of the add-in) also has a user manual and colour design manual you can download. All for free (with opportunity for donation). The add-in is also available for Excel 2003 and 2007, with the occasional Mac option. If you've got Excel, chances are there's a version of the add-in for you.

Here is one example of what you can do with this add-in. Take a sample of scores:
We have student names, formative and summative (bold) assignments, and scores. But this doesn't give us a good handle on what's happening in the class. What if our gradebooks looked more like this?

We get a picture of each student's performance. We can look for patterns and have a very concise view of what is going on. Notice that each of the line graphs are about the size of a student's name. This is what sparklines do: they condense a lot of data into one bite (byte?) sized container.

At the bottom of the list is a bar graph. This graph summarizes the performance of the entire class. The median for each assignment has been derived and "stop light" coding applied so a teacher can easily see how the class is doing. This format would take some getting used to, but what an awesome option to include for teachers.

The Downside of Add-Ins
If you use an Add-In, keep in mind that in order for other people to use the workbook, either they have to have the add-in or you will have to save the workbook as a macro-enabled workbook (it's one of the options you have). The other downside isn't so much one associated with add-ins as it is with freeware: your options for support are pretty minimal. In other words, you get what you pay for. When it comes to sparklines, there are commercial options. So, if you like the idea, but are nervous about being on your own, you might want to check out Microcharts. This is one of the reasons why I built the graphs in the "Roll Your Own Gradebook" series as full-size before minimizing. I am totally sold, so to speak, on the freeware sparklines. But they don't auto-update and can sometimes be quirky as you close/re-open a workbook. Just something to consider as you work with various projects.

We'll explore more charts, graphs, and add-ins in future posts. Is there something you'd like to see? Leave your suggestions in the comments.

1 comment:

  1. Also see this guide for excel sparklines. Im only sharing this for the video that is contained in teh other guide, since the information in this blog is perfectly fine. But a video can show people like me (lets be nice and say im a slow thinker :P) more exactly what to do.