Using Spreadsheets and Pivot Tables to Track Data. . .

So I've decided to give the Radical Nation their Christmas present a bit early this year---and it's a home-made gem that you might just find to be your favorite gift under the tree.  Here it is:

Download Sample_Spreadsheet

Now, I know what you're thinking:  "This guy is giving us an Excel spreadsheet for Christmas and he wants us to be happy about it?  Why didn't he just bust out a few hundred digital fruitcakes?"

Before you jump to any conclusions about the worthiness--or worthlessness---of my offering, though, be sure to give this spreadsheet a good once over.  What you'll find is that it's a pretty complex little data management document that you might just be able to take real advantage of.  I know my colleagues are pretty jazzed about the whole thing.  In fact, it's the first holiday gift that I ever truly made them happy with.

Here's how it works:  On our learning team, we were looking for a way to track individual student performance at the reading skill level.  That means we wanted to be able to quickly identify the students that we serve who struggled with author's purpose or main idea questions.  We wanted to know who needed extra help with summarizing or vocabulary.  And we wanted sorting these students to be easy.

So I sat down and did a bit of Excel magic, creating a spreadsheet with separate pages for each of the main reading skills that we teach.  Then, we started to ask one reading question a day connected with a regular current event lesson that we were already teaching in class.  (You can learn more about our current event lessons--and see the kinds of questions that we ask our students---by visiting this collaborative planning wiki.)

After each current event lesson, we add one new column to this Excel spreadsheet for the question that we ask in class.  Then, teachers record which students got the answer to the daily reading question right and which students got the answer wrong.  That's why the scores in the individual columns of our spreadsheet are either 100s or 0s.  Each "Test" is only one question. 

(How's that for keeping it simple.  We ain't stupid!)

The beauty of this spreadsheet, though, are the final two worksheets, labelled "Pivot Table" and "Summary Table."

 

When teachers visit the worksheet labelled Pivot Table, this is what they see:

       

Notice the drop down menus at the top of the screen?  They allow teachers to sort the data in our shared spreadsheet in a million different ways.  For example, if we wanted to know all of the students in our second block class who were below standard in identifying bias, we would switch the settings in the appropriate drop down menu like this:

 

The hamsters running around inside our computers then go and collect all of the names of the students struggling with bias in our second block classes, giving us a report that looks a little like this:

   

Not only can we see each student's score on the bias questions that we've asked over the course of the semester, but we can also see their scores in every other skill category that we're measuring.  This helps us to make better decisions about whether or not a student is struggling across the board or with just one small subset of skills.  And because we know that all of these students are in class during second block, we're able to provide remediation and/or enrichment across the entire hallway.  One teacher can work with struggling students in a focused mini-lesson while others are absorbing high performers for additional exploration.

How's that for hardcore?

What's crazier is that the "Summary Table" worksheet is about a thousand times cooler. 

 

Don't be fooled by its diminuative stature.  This may be the least complex looking table that you've ever seen in an Excel spreadsheet, but it does some wicked work.  Notice the box that is bold-faced in the image above?  It's telling us some simple--yet valuable--information, isn't it?  As a team, we know that there are 2 students in our second block classes that are "Below Standard" in "Overall Mastery." 

Wouldn't it be great to instantly know who those 2 students were? 

Then---in the true spirit of Christmas Past, Present and Future---ask and you shall receive:  Just double click inside the table and a new spreadsheet will launch including the name of the students who are struggling, the name of the teachers of those students, and the averages of those students in each of the individual subskills that we're tracking in our spreadsheet.  The report looks a little like this:

This spreadsheet has my team all worked up into a remediation and enrichment lather for the first time ever! 

We've got it posted on our school's shared network so that it can be accessed by any teacher on our hallway---or any education professional working in our building.  If our principals want to know the progress of our students on reading skills, all they have to do is open the spreadsheet and click a few sorting buttons.  If our special programs staff wants to look up the performance of individual students who are mainstreamed into the regular ed classrooms, if the academically gifted teacher is interested in apparent discrepancies between an identified student's performance and ability, or if guidance counselors want to to have more information before recommending a child for testing, this spreadsheet makes it possible.

As classroom teachers, we can spot trends in the performance of students across our hallway.  We can find colleagues who are especially effective at teaching individual skills, target our own professional development to areas of shared instructional weakness---and most importantly, begin to systematically identify students in need of remediation without having to sift through piles of paper. 

How can you take advantage of this spreadsheet?

If you teach language arts, it's no sweat!  Enter the names of your students, delete the existing scores, and begin adding the results of upcoming assessments.  If you ask a main idea question on your next classroom test, write down a list of the students who get the question wrong and give them a zero on the spreadsheet.  You're likely to have only a small handful of students getting any given question wrong, so data entry isn't an overwhelming challenge. 

Over time, add new questions to your spreadsheet and watch the pivot tables and summary tables grow in complexity.  All of the formatting is done on the spreadsheet, which means that you won't have to do any heavy lifting or formula writing.  Heck, even the colors for mastery will change automatically! (Our team decided that students scoring 75-85 were "At Standard."  Anything above and a student scores green.  Anything below and a student scores red.)

If you teach another subject, you can still use this spreadsheet---you've just got to make a few changes to the names of the worksheets.  After all, the chances are good that the math teachers are slightly less interested in testing Author's Purpose than us language arts folks!

Changing the names of the worksheets to match skills that you're interested in testing is a breeze, though.  Just double click on the name of the worksheet.  It will turn black, like this:

   

Then, type the name of the skill that you want to measure into the worksheet's title box.  The spreadsheet will take care of updating all of the appropriate formulas for you.  Nothing to worry about there.  You might have to change the titles of some of the columns in individual worksheets, but that's no sweat.

So whaddya' think of my gift NOW?!  Not bad, huh?

And think about this:  You can DEFINITELY find some regifting value out of this sucker, can't you?  Who DOESN'T know a teacher that wouldn't want a pre-formatted Excel spreadsheet that makes identifying students in need of remediation or enrichment this simple. 

You're going to be a hero. 

(By the way, many thanks to Scott McLeod over at Dangerously Irrelevant, whose writing on using data to drive instruction has been a primary motivator for my work in the past few months.  Scott is also the mastermind behind School Data Tutorials, a website that helped me to figure out Excel for the first time.  Very cool stuff, Scott.  Very cool indeed.)

Image credit:  My Wife's First Fruitcake by Randy Son of Robert, licensed Creative Commons: Attribution

Comments

[Sheryl Nussbaum-Beach]

So let me get this straight- you are using a pic of your wife's first fruitcake as an analogy for unwanted gifts.

You do love to live dangerously.

[Bill Ferriter]

Heck no, Sheryl!

I'm using a pic of someone ELSE's wife's first fruitcake as an analogy for unwanted gifts.

Let's just hope they don't live in the neighborhood : )

(It's a pretty amazing picture, though, isn't it? great lighting. I'd eat that fruitcake!)

Bill

[Charlie A. Roy]

@ Bill,
Thanks for sharing this looks great. This definitely beats coal.

All the best!

[John Ferriter Sr]

Yo Bill.... Having worked with these spreadsheets that are just the greatest tools; I have run into some negative feedback that only went away after spending dozens of hours troubleshooting someones claims that the sheet is not working. I would encourage you to suspect that someone got into the formulae and changed something; rendering the results ineffective. If you know the sheet is doing your work correctly, then suggest the naysayer download a clean copy and start again. You may also want to consider (if you haven't already) protecting the cells that are doing the calcs so they can't be inadvertantly altered.. Good luck....

[David Cohen]

Bill -

My district will not pay for computers with those cool hamsters inside. Any suggestions?

[rightwingprof]

Pivot tables are powerful. You may be interested in this:

http://rightwingnation.com/2007/08/28/stats-for-teachers-revisited/