At long last, I've been spending some time updating my gradebook template. I believe I last shared an Excel version 2.5 years ago---and the thing has been begging for a makeover ever after. Since then, I have posted several times about my interest in using conditional formatting (to create a "stoplight effect" for determining interventions) and sparklines to summarize data (see Data Viz: Part I for a refresher). I've now been making the time to put all these pieces in place. This sort of template is the number one request I get from teachers. It's time I had more than just good intentions.
I have a file ready for your beta testing. There are a couple of screen shots below, along with some explanation. The one thing I am still working on is a student progress report worksheet which automatically updates. (I am only a Yellow Belt and some of this work requires Ninja standing.) In the meantime, I am hoping that some of you might be willing to download the spreadsheet and play with it a bit. You will need Excel 2007...and will need to enable macros when you open the file.
Before I get into the nitty-gritty, I have to give major kudos and props to Fabrice over at the Sparklines blog for the original template file. He has done an amazing job producing this open-source (not to mention free!) add-in for Excel.
This is the template worksheet:
This shows the basic setup for tracking student data and the advantages of using Excel over most electronic gradebooks. The commands you would need in order to make changes are provided in red font.
The image below is of the "Sample Gradebook" worksheet. It's my pride and joy at the moment.
This puppy is fully loaded. Based on the previous template, it has sample student scores and then the various rules applied: conditional formatting, formulas, and various sparklines summarizing the data sets. You can change any of the numbers in the coloured squares and the worksheet will update for you. Heck, hide the numbers and just enjoy the graphs.
Update 3/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.