05 March 2011

Excel Dashboards for Educators

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.


Tatnall Physics said...

Cool stuff - I loooove Excel apps. Here's a tip to make this a little easier for you (and easier for you not to accidentally show one kid's grades to another while you're searching around for the right number): use data validation on the name column to choose the student name, use an offset to update everything else off of that.

Hugh O'Donnell said...

Thanks, SG!

With all the funding problems districts are facing, we may have to permanently defer the expense of monster reporting systems or gradebook programs for every teacher.

Everyone has Excel. Just have to learn to use it.

Once again, you're way out in front of the wave...

Peter Scott Hutchison said...

This is amazing excel work! Is there a Mac Sparkline? I have not been able to source anything yet and the Office for Mac doesn't work...yet.
I've struggled for years with not being able to visualize data for students until I set up a very basic spreadsheet I nicknamed 'performance signature'. This sheet had students tracking their scores (I know old school)alongside a median line. I set up and inputted the formulas and let them take charge from there. In a sense they were creating a larger 'sparkline' line graph of their performance. Many students gravitated to the picture, to the dips as places to focus and to their median.
I look forward to following your developments, thanks again.

The Science Goddess said...

Hi, Peter. Right now, there is just the add-ins for Excel. The Sparklines link in the post will take you to a site where you can download a free add-in for Mac. There are other paid versions out there, if you're so inclined.

The basic 2007 version I have posted (first link) should work just fine on a Mac. The graphs are unofficial sparklines---regular Excel graphs, just made tiny.

Glad you like what you see so far!

jsb16 said...

Dumb question: How difficult would it be to port this over to OpenOffice?

The Science Goddess said...

Not a dumb question at all---I think it's an excellent one.

Unfortunately, I don't know the answer...I haven't tried to pull it over to Open Office. Perhaps someone will take the plunge and report back?

Ms. Young said...

Your gradebook is incredible! I love all the features you have included. This makes it so easy to follow a student's progress. I was wondering if there is a way to assign percentages to how much each category (formative, summative, etc.) is worth. For ex., our summative needs to be 60% of their grade. Thanks!

jsb16 said...

Formative assessment shouldn't be any part of a student's grade. Work habits, effort, and attitude should be an explicit part of the grade, but there should be no penalty for not being the fastest student in the class. IMO.

Leanna said...

This is really awesome. I'm struggling with my current gradebook, and this would really help. Thanks!

Ben said...

SG - love what you are trying to do with this dashboard. Have you seen some of the Excel dashboard visualisation widgets (traffic lights, dials, up/down indicators, thermometers, trend widgets, dynamic dropdown lists) at www.ExcelDashboardWidgets.com?

The Science Goddess said...

Hi, Ben,

I have seen those, but don't use many of them because they aren't considered best practice for data visualization.