01 May 2010

Building a Better Monster

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.


Arch City Teaching said...

Your gradebook looks EXCELLENT! I found out about your blog and gradebook via Twitter and I would LOVE to download it and play with it. However, I must be doing something wrong? I have Excel 2007 and I tried extracting all files, but it's not popping up right. Am I doing something wrong? If you could let me know, that would be great! Thanks!


The Science Goddess said...

You should be able to do a "Save As..." The file has a different extension (.xlsm) from most excel files because it is macro-enabled.

When you first open the file, there should be a banner that appears just below the toolbar. The banner will have a security warning about the macro---just click it to enable the macro and things should run fine from there.

If you've tried all that and are still struggling, send me an email and I will attach the file to a reply.

Arch City Teaching said...

I got it! I used my own personal and trusty ol' Macbook. Darn work computers...
It looks great! Can't wait to play around with it! Thanks again!

Riley said...

This looks like a fantastic system. How has your record-keeping been going with it? Do you use this to report to students or parents?

The Science Goddess said...

I'm not in the classroom this year, however, I did use a file similar to this when I was in the classroom. There were no sparklines then, but I did use it to make lots of other graphs and charts. I also liked the ability to keep various spreadsheets of data in one place. I did use it for reports to students and parents.

JamiDanielle said...

OK, I am about to prove I'm too technology illiterate to attempt this system, but I loved how it looked in your blog...so I'll try anyway.

I downloaded the file which has four subfolders (_rels, customUI,docProps, xl) and a file called [Content_Types] but can't figure out how to proceed from there. None of the files seem to be opening as excel docs.

The Science Goddess said...

Hi, Jami,

Sounds like the file downloaded oddly---not your fault at all. Sorry you had some problems.

Send me a note to "the_science_goddess[at]yahoo[dot]com"

I'll email you the workbook directly and you should be good to go!

Unknown said...

I have tried to download this file, however it is not opening the way it should. Would it be to much to ask to send it as an attachment. I would really appreciate it. thanks for all you do.

The Science Goddess said...

Hi, William,

There are no more downloads on this page. If you will let me know which of the gradebooks you want (list posted here) and send me your email address, I can get it to you as an attachment.

Unknown said...

I'm sorry to be dense, and I know this post is old, so I don't anticipate a (super fast) response, but are you still offering the download? I see the tutorials on the link you posted in 2012, but am not seeing the file links.

Thank you!