I've been interested in the idea of a dashboard reporting tool for the classroom for a long time. I stumbled across business versions three years ago when I first started thinking about how to build a gradebook I could use, then created a static mock-up a year and a half later. I have wanted to fine tune the darned thing ever since---getting to a point where the reporting tool would auto-update for individual students. Then, in my typical foolhardy manner, I proposed a workshop for data visualization which would include creating dashboards...even though I hadn't made one yet. However, a deadline can be a healthy thing. A little frustration leads to innovation...the night before the workshop. And then, I had my Colin Clive moment: It's alive! It's aliiiivve!
The starting point was the gradebook from my Building a Better Monster post. Plenty of student scores to play with. What I ended up with is something that looks like this:
Yes, it looks the same as my original mock-up...but it's not. The important difference is in cell A1:
See that little number there? If you change that number (there are 10 students in the gradebook example, so you can change the number in A1 to any whole number between 1 and 10), all of the names, scores, and graphs will automatically update to represent that student. ZOMG This is so supercool that even if you aren't an Excel lover, you should still download one of the files to play with. It is an awesome thing to change a single number and watch everything else update and bow to your whim.
This is accomplished by using OFFSET/CHOOSE formulas in each worksheet (one for the scores, one for the dashboard). The one in cell A1 tells the rest of the dashboard what to pay attention to. The ones in the remaining cells describe which data to pull. I'll provide some additional tips and tricks in a later post if you want to start from scratch.
I won't claim that everything is picture perfect. I would much prefer to use the Sparklines add-in to display the graphs. But while they work beautifully in the gradebook, they won't work in the dashboard. So, in the Excel 2007 versions, I had to use the regular graph options. In the 2010 version, I used the MS "sparklines" in the dashboard (and add-in for the gradebook). It's a start.
I hope you'll take these and make them your own. If you're an elementary teacher, sub in different subject areas. If you're an administrator, see what happens when you look at different classroom level data. I know you can purchase fancy-schmancy software that will do these things for you---but I firmly believe that you should have the choice about how your data is used and visualized. Enjoy!
Update 5/2012: Please visit my page on the Excel for Educators blog for the most recent versions of gradebook and reporting tools. Most have sample workbooks to download and instructional videos...and have moved beyond the first steps described in this post.