Saturday, March 3, 2012!

When I'm out and about with educators, I always like to ask what they need their data to do. Shiny software can be great, but all too often, I hear from teachers and administrators that they're stuck with pre-packaged analysis and don't have the flexibility to do what they want. Using Excel might not fill in all of the gaps, but I like to think about the ways it can answer the questions educators have.

After prompting a discussion about what they'd like to see, a teacher talked about how she'd really like to overlay her gradebook onto the student seating chart. I thought that was a great idea. We often look at classroom data through the lens of time, but we don't often see it in space.

This exchange happened a couple of years ago, and I finally sat down to develop the idea. In this post, I'll share the most basic version; but there are plenty of additional options to add on. I started by modifying the Beginner's Gradebook to include 30 students, then drew a very traditional seating chart on another worksheet. I filled in students names below each "desk." Keep in mind that you could make any arrangement that you want with the spreadsheet: a different number of desks, four students at a table, horseshoe arrangement, the remaining room set up (with the one kid you always need to have next to your desk). Lots of possibilities.

Next, I highlighted the range of assessments on the Grades sheet. I named the range "Assessments." I'm creative like that.

On the sheet with the seating chart, I created a data validation list using the Assessments range. This way, I could display different data on the seating chart. (Need a refresher on creating the data validation? Watch here.)

Then, I added an INDEX/MATCH equation in the "desk" cell for every student. Here is the one for Cathy Andrews: =INDEX(Grades!C8:AJ8,MATCH(T3,Assessments,0))  The formula tells Excel to look on the spreadsheet with the Grades, in the range of assessments (columns C - AJ) for Cathy Andrews (row 8), and match the score for the assessment on the Grades sheet with the one selected in the dropdown list (T3).

I would like to find a different way to do that part. Having to individually list which kid matches each row of data is not particularly friendly. And, when you move kids around in the room, you'll need to remember to move their equations with them. Since you can't have the formula MATCH on two variables (name of student, title of assessment), I've done it the cleanest way I can think of...but I'll keep hunting. If you have a solution, I'd love to hear it and hope you'll share in the comments of this post.

Finally, I added some conditional formatting to the "desk" cells.  The end result is a heatmap'ish thing like this:
It's kind of fun to play with. I did manipulate a few things in the data as discussion points. Does Dorothy Gale's performance second quarter catch your eye? Would you call home to find out? Are there some areas of the room outperforming others? Do you think something would change if you spent more time there or should you regroup students?

You can download the sample gradebook here. I'd love to hear your ideas about how to use this type of tool. I'm thinking about ways to incorporate attendance data...or graphs. What would you want to see from a birdseye view of your classroom?

Bonus Round
Are you attending the ISTE conference in June? Come hang out in my workshop. We'll play with Excel and other tools...and I'd love to hear more of your ideas about what would be useful data analysis for you.


  1. First off, I *love* this site. I'm seriously considering printing it off in chronological order for the staff at my school who have asked for some data handling training. Major kudos, it's great.

    Just on this point, I really like the idea, but to me I could see it informing my seating plan (hot spots with two kids not working together well needing splitting up, for example). I haven't had a play with it yet, but how would you suggest working that? Building in seating moves without breaking the whole thing?

    Also, and this is more just out of curiosity, but is there a reason you avoided the green to red colour scale for the "heat mapping"? It feels a little more flexible to me, and I'd likely throw in a normaliser as I'm renowned for testing to 13 one week, 8 the next... not everyone is a scatterbrained floozy like me, though :)

  2. Hi, James---glad you're enjoying the site. I'm having a lot of fun with it and hope that more teachers will take control of their own data with Excel.

    I'm really hoping to write a simple, non-VBA formula so that you could move kids just by swapping names on the chart. But until that comes along, there are a couple of options...neither of them is particularly elegant:
    1) Look at the row numbers for the kids you want to move and just swap them out in the formulas. For example, Cathy Andrews is currently "8" in the formula. If I want to switch her with Donald Duck, I just need to replace the "8" with "19."
    2) You can also select the "desk" and student name, then cut and paste it elsewhere on the spreadsheet to temporarily create a space. Then, cut and paste another student into the first spot...cut and paste (again) the first student into the final location.

    You can make the conditional formatting be whatever colours you like. I don't have an issue with the typical "stoplight" format. Some data viz'ers, however, recommend avoiding it because it creates problems for those who are colour blind (up to 9% of the population has some variety of colour blindness). If the data is just for me, then it doesn't matter what the colours are. If the data is going to be shared, then using a modified palette might be better. My thought here was to show a bit more range (even though there is a dark green at the high end) since I like to give readers a sense of the possibilities. However, I am a big proponent of people adapting these ideas to whatever works best in their classroom. I'd love to see how others use the tools!

  3. Another way to move kids around would be to use an array formula. I posted about using MATCH with two properties here.