Active and Cooperative Learning

Grade Record Keeping

Students always want to know how they are doing, and giving them feedback is a great encouragement. I use a computer spreadsheet to keep track of my grades. I have set up an area in the spreadsheet where group grades are entered. I then use the lookup function to have those grades "distributed" to the area for the individual students. I also create a macro that will automatically print out the information for one student at a time. This is done by hiding the information for all the other students and then printing the spreadsheet. This process is fully automated so that I can with one command print a sheet for each student showing the scores I have on record for them. I usually distribute these to the students after each exam. Each student get a sheet that shows their information only (for confidentiality) and also has information on average scores, the high and low scores, and the student's rank in the class.

I have a sample copy of the spreadsheet that I use for grade recording on-line (link below). All names have been removed and grades that are recorded are purely for illustration and do not relate to actual grades that my students have received.

There are a couple of things to note. There are a lot of formulas in the file. Those that total exercises and quizes assume a set value for each item of the type it relates to (e.g. 5 for every exercise, 10 for every quiz). Others use a lookup function to distribute group grades to individuals. If a student is absent with out a valid excuse, the lookup formula gets replaced by a zero. If the student is absent with a valid excuse, the formula gets replaced by an "x." The total functions will count valid grades (those not an "x"), and then prorate up to the number of items done so far. This way, students who have excused absences are neither punished with a zero nor do they receive credit for work they have not done.

In areas where work is dependent on group work, the grades can be entered in the upper area, and the spreadsheet automatically distributes them among the individual student records. This is accomplished with a hidden row that lists the column offset numbers. If you need to add or remove columns from this spreadsheet, these numbers will need to be updated. If you look closely, you will see where the rows are hidden near the top of each of the three sections.

There is also a macro that I have developed that will allow you to print out the information you have stored for each student on a separate page. I will usually do this about 2-3 times a semester. This lets the students verify I have the correct grades recorded and also gives them feedback on how well they are doing. This macro hides group information as well as information on other students. Based on student requests, it hides the rank in class information as well. (Students complained that if they were at the bottom of the class this information was very discouraging.) If you have more or fewer students, or have different sets of grades, this macro will need adjusting. There are comments in the macro that will hopefully make changes easier. Any changes will have to be done in connection with the location of items in the spreadsheet itself.

(Right click on the following links and choose "Save link target as..." in Netscape and "Save target as..." in Explorer.)
grade record spreadsheet
macro to print student feedback sheets

Peer Evaluation
My Active and Cooperative Learning Publications

All opinions represented on this page are mine. No assumption should be made based on these statements about Canisius College, the Canisius College Computer Science Department, or the Canisius College faculty, staff, and students.

send comments to

Jeffrey J. McConnell's home page

Canisius College Computer Science Department Home Page

return to Canisius College Home Page

Last updated December 22, 2003 by Dr. Jeffrey J. McConnell