02 September 2011

Roll Your Own Gradebook: Setting Up

Welcome back to Ye Olde Blog. The welcome is for me, by the way. I wish I had some sort of exotic story to tell you about what I've been doing during this very long interim between posts, but all I can say is that sometimes, managing my personal life doesn't leave any headspace to think out loud here. Blogging is its own variety of habit or muscle memory. And while I may have let things gone to seed over the last few weeks, it's time to get back on the wagon. No more "real world" benders for me. I've learned my lesson. :)

I've been working on a couple of little project recently with people who don't know jack about Excel. This is not a personal fault. I still like them, even though they look askance at the program like it will get their dog pregnant or steal their souls while they sleep. There are plenty of data tools out there. Not everyone has to get to know Excel, but it is very difficult in education these days if you don't even understand the difference between "data" and "information" as a first step. Data literacy is becoming a must-have skill.

As I've been beefing up my Excel know-how over the last few weeks, I've been thinking about how to share that information here. If you're not into the whole gradebook idea, I'd encourage you to keep reading, anyway. The tips and formulas would be just as useful for whatever data set(s) you are managing. Are you an elementary teacher who DIBELs? In a school with MAP testing? A district with various benchmark or interim assessments where you want to look at performance by classroom or school? If you've got a list of students/teachers/schools that has data next to it, then, these ideas are for you, too.

One of the most frustrating things (for me) as I try to do new things with Excel is the lack of non-business examples. Most websites and YouTube videos assume that you are (a) always working with numerical data and (b) interested in some sort of angle about profit margin or losses. We really do need a bank of "how to's" that models for education. If you have seen some, please do share in the comments.

I've had a lot of you contact me over the summer asking about my Excel gradebook and any updates. I have been promising to post those...and now your wait is over. The video below will show you how to get set up. I also have a sample workbook you can download and use with the video. (But if you want to use your own data sources, that's cool, too.)

As you will see, the workbook has two worksheets: Scores and Report. This allows me to keep the raw data separate from the dashboard reporting too. Depending upon what you're working with, additional sheets can come in very handy. Perhaps you want them for qualitative data you collect, attendance, discipline, or other notes. If not, and you're anal-retentive about how your spreadsheet looks, then an extra sheet is very handy for stashing your formulas and ranges: It will keep your raw data looking fresh and clean. If you're hellbent on making things look pretty, stay tuned for later videos. You'll have the Miss America of dashboards when I'm done.

I populated the Scores worksheet with some names, assignments, and data. Even though the default color themes in Excel are awful, I'll demo them so you can see some basics about applying colour. I like to separate grading periods and different types of standards using colour. This makes it much quicker to find information. But I also apply conditional formatting to the spreadsheet so that I can more easily visualize what is happening with the scores.

Finally, I use a simple formula to determine the median and help summarize the scores. This is as far as Lesson 1 will take you. But I'll be back in a day or two to show you how to pull the data in to a dashboard reporting tool.

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.

1 comment:

Hugh O'Donnell said...


So many teachers complain about lack of gradebook control in the SBG world, and you are opening the portal to the future.

Districts don't need to invest megabucks in complex grading software, they just need to invest in professional development on how to use Excel.

And you are saving them the money!

This is a magnificent contribution, SG. Thank you!