Saturday, September 3, 2011

Excel Gradebook for Beginners, Lesson One: Organizing Data

This is the first in a series of posts (also available over at What It's Like on the Inside) on building your own gradebook. 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 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.

In the next session, I'll show you how to create a dropdown list of student names. The selection from the list will be used to auto-fill many of the cells in the reporting tool using a formula with INDEX and MATCH functions. Sound like it's over your head? Come back and give it a try. I think you'll be surprised at just how easy it is.


  1. Very well done. I have hacked at this stuff for years and it is nice to see someone make a clean presentation about it.

  2. I realize this is probably the dumbest question ever, and I've been looking through your posts trying to find the answer. How do you go about representing the data in your 4 point system as a traditional percentage? I hate traditional grades, but unfortunately my district hasn't moved forward in this regard. I still have one spot on the gradecard where I am supposed to cram all of my data with a (seemingly) arbitrary letter grade. How do you tackle this issue using the data presented in a 4 point system?

    1. This is NOT a dumb question. It is, however, the $20M if you figure out the best answer (as there is no "right" one), you'll be able to retire with it. :)

      My answer is here. Basically, each student and I sat down together and figured out what to put on the report card. I understand that this approach doesn't work well in a time where parents can log in and watch student grades like a stock market report. And yes, it's a squishy way to do things...but oddly enough, students always said they knew why had a particular grade in my class vs. their other classes where there was calculation involved.

      Other teachers have other systems and answers for number crunching. I recommend taking a look at what Jason Buell and Frank Noschese have to offer.