Gradebook...in...Space!

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.