26 July 2008

Advancing the Gradebook

One of the things ed research has told us is that when teachers have a way to graphically represent the grades they collect (and then reflect on the what they see), student achievement can rise by 26%. In terms of the type of "feedback" teachers are involved in, only one other form increases achievement more (when a teacher has formed a pre-existing rule about when to apply interventions based on data...and then uses it). Considering this, shouldn't we think about gradebooks differently? (For more on types of feedback and their effectiveness, have a look at Chapter One of Marzano's Classroom Assessment and Grading That Works.)

The problem with most gradebook programs is that they don't offer a way to graph the data. I used Excel this year and was happy with it. I liked the flexibility of being able to apply different formulas, hide different data sets, have different worksheets for different pieces of information about kids, and more---including the ability to have the program draw graphs for me. I admit, however, that I used the graphs to look at general classroom trends rather than keeping tabs on individual kids.

But some new options for Excel have me thinking that there could be some very exciting new ways for teachers to think about grades.

First up are Microcharts. This is not a Microsoft product, but is compatible with Excel. The idea here is that you can create a chart within a single cell in your spreadsheet. Here are some examples of the kinds of microcharts that can be made:

As with any data set, different graphs are better than others for communicating information. I think the bullet graphs and bar charts would be most useful. You could even colour code things as red, yellow, green to show the RTI levels of "Intensive, Strategic, Benchmark" to help get a quick glance at where to place your interventions in a classroom, depending upon the standard being measured.

The only drawback (for me) for Microcharts is the price. I might try the free download for a month and see if it really is the answer to classroom needs before I pony up for the full meal deal. Even then, the cost is a bit steep. (Wonder if I could get an educator's discount?)

There are other ways, however, to achieve the same look. Pointy Haired Dilbert recently posted on how to create Bullet Graphs in Excel. One result is shown below. You'll have to imagine student names in the lefthand column and various scores in the middle with the graph on the right; but I think you'll get the idea.

The XLCubed blog showed another way to create In-Cell Variance Charts using a formula. The result is a simple bar chart (as shown below), but could certainly have value for communication. Imagine using these for a progress report or end of term report card. I like this one as a way to actually show growth over time---where a student started at the beginning of the term and what growth has occurred. The bullet chart above is better for showing overall progress toward a goal.

The more I think about all of this, the more I'm getting geeked out about building a better gradebook in Excel. I'd like to think that someone in the software industry is looking at these same things and seeing the same potential for educators that I am---perhaps even writing code for something new. A 26% increase in student achievement is nothing to sneeze at---and if we can get there by having access to pictures of grades, isn't that worth exploring?

If anyone has seen a gradebook program with microcharting features already built in, please do chime in in the comments.

P.S. If I were an administrator or someone in charge of programs, I would definitely look into building a dashboard to integrate the massive amounts of information that regularly flood in. As a principal, what a great way to keep your pulse on what's happening with student learning across a school. The sample below is another business one---but it's not hard to imagine the applications for education.

Update 5/2012: Please visit my page on Excel for Educators for the most recent versions of gradebooks and reporting tools.


Joe said...

I am with you in the desire for something better. Every grade book program I have tried has simply been a digital record book. I've always thought it could have been much better and providing decent, easy-to-use data analysis. I'm still looking.

ms-teacher said...

Are there tutorials on how to set up an Excel gradebook?

The Science Goddess said...

I haven't seen any specific tutorials; however, you're welcome to download miine and play with it to your heart's content.

andrewcknoll said...


I can't afford Microcharts, but I do share your affection geeky gradebooks.

Conditional formatting is a super data-analysis tool. (e.g. if score is less than 2, make cell pattern blue.)

A quick glance at the blue highlights and you'll know which skills (and/or students) need reteaching.

I also love how easy it is to print student progress reports from Excel. (Hide all student rows except one.)

Your post gave me the idea to include a bar graph on each student progress report. Just mocked up a sample & it works great!

Below-proficient scores are highlighted in blue and the bar graph shows standards progress at a glance.

Thanks for the post.

-- Andrew

@ms-teacher Search for excel template. If you want assignments & averages, try this tutorial. http://office.microsoft.com/en-us/excel/HA100690701033.aspx

The Science Goddess said...

I like the idea of using conditional formatting---much less labor intensive than other options. Glad you got inspired, too!