Using a gradebook with alternative grading, part 1: The basics

How to set up a spreadsheet to record your alternative grades

Photo by Kimberly Farmer on Unsplash

An important practical question that every new alternative grader should ask is: How do I record these weird marks? This week, we’ll dive deep into the nuts-and-bolts of recording grades in an alternatively graded class, starting with the basics: How do you enter and summarize marks? Next week, we’ll look at how to use this data to determine final grades. Along the way, I’ll give some tips and tricks and identify pitfalls to avoid.

The first and biggest change that I recommend is to use a spreadsheet, either as your primary gradebook, or as a way to store and summarize data before it’s uploaded to a Learning Management System (LMS) gradebook. That’s because it’s much easier to make a spreadsheet work the way you need — LMSs are famously inflexible when it comes to alternative grading. However, the ideas in this post will help you think about how to use any kind of gradebook more effectively with alternative grading, including LMSs.

The specific approach I’ll illustrate here is based on many years of experience. While there are certainly other ways to use a spreadsheet with alternative grading, I think that this is one of the most effective.

In this post, I’ll focus mostly on Standards-Based Grading, since SBG most clearly illustrates the changes in thinking and organization that you’ll need to make. The ideas from this post can apply to any type of alternative grading, and I’ll mention some other options — specifically ungrading — at the end.

In order to record grades in an alternative gradebook, you’ll need to change your mindset about a couple of important ways that data in a spreadsheet or LMS work. Each section below dives into one of these important changes.

Recording marks

In a traditional gradebook, each column represents one assignment. The column contains a number for each student indicating the number of points (or percentage) they earned.

In Standards-Based Grading, students won’t earn points — they’ll earn marks. And they will likely earn several different marks on an assignment, one for each standard that was assessed. So, when I construct a gradebook for an SBG class, I think of each assignment as a group of columns. Each column records a student’s mark for one standard that was assessed on that assignment.

Here’s an example of how my gradebook looks in a standards-based class:

This is a real gradebook that I use for one of my SBG classes, but of course I’ve made up student names and grades.

Notice that there are four assignments (Quizzes 1 through 5). The first few each cover two standards, which have short “codes” like L.1 and L.2.¹ So, each assignment (like Quiz 1) actually consists of a couple of columns, one per standard. Quiz 5 was a chance for students to make new attempts at past standards, so it has a bunch of columns under it.²

Below each standard, I’ve entered the mark that that student earned for that standard on that assessment. I enter these marks as a number from 1 to 3, which might be surprising: Haven’t we made a big deal about how marks should indicate progress, and definitely shouldn’t be numbers?

Yes! On all student-facing work, you should use descriptive marks that indicate progress. Here are mine:

• S: Satisfactory

• R: Revisable

• N: Not yet (or Needs new attempt)

But spreadsheets work best with numbers. So when recording marks in a private gradebook — which students never see — you can and should record them as numbers, which will make things easier for you when using spreadsheet commands and tools. For me, S = 3, R = 2, N = 1, and a blank indicates that a student didn’t attempt the relevant questions. These numbers are never used as numbers (e.g. I never average or sum them), and when I do anything with them in the spreadsheet, I treat them as the categorical data that they really are.

If you’re using Specifications grading, you probably need just one column per assignment. It will hold a single 1 or 0 to indicate if the student has met all specifications satisfactorily, or not yet.

Because you’re never going to add or average these numbers, you can easily add other columns to record other data as well. Here’s an example from the same class, showing an assignment that uses both specifications (for writing and communication) and standards (for the discrete skills addressed in the writing):

Here the “Proof 1” assignment has a bunch of columns. From right to left, there are columns for standards that could be met within this assignment (E.1, N.2, N.1, and P.1); the column labeled “C” indicates whether or not students met our Communications Specifications (I use 3 for “satisfactory” and 0 for “not yet”), and “rev” is a short note to myself that indicates where students are in the revision process (“d” means a draft is submitted, “w” means a written revision followed the draft). As a side note, I only give feedback, not marks, on drafts, which is why Chris has no marks.

You can tweak this general approach to fit your needs, and the final section of this post gives some alternatives. One important warning: Based on the correct thought that what really matters is whether students have met standards, you may be tempted to have only one column per standard, and just update the number (or mark) in that column each time a student makes a new attempt at that standard. While this is very simple, it has a few key problems. First: You’ll lose your record of a student’s marks on past assignments, which can be very useful to know for office hour conversations and advising. Second, it’s more prone to errors. You’ll typically have to enter several marks at a time in non-adjacent columns, which can easily lead to entering a mark in the wrong place. This can be quite hard to undo when you discover it later on.

In the next section, we’ll see how you can instead let the spreadsheet do this work for you, by summarizing a student’s progress on each standard.

Summarizing progress

Now that you have a way to record marks in a gradebook, how do you use them? Given that you won’t be summing or averaging numbers like in a traditional gradebook, what do you do?

This is the second key mindset change that you need to make when working with an alternative gradebook. Most often, you want to count marks to see how many (or which) standards a student has met, and how many times they’ve demonstrated their understanding of each standard.

As we saw above, your spreadsheet will have a bunch of columns, labeled with different standards:

Some columns have the same standards as others. Here, I’ve highlighted columns G and U, which both represent standard L.1. This represents two different times I’ve assessed L.1. To know how a student is doing on L.1, I need to “zoom in” on only these columns. Depending on how the class works, I might want to identify the student’s best mark (the highest number), or count how many times a student has earned a “Satisfactory” mark.

For example, focusing just on the red boxes, we can see that Alice and Bob have both earned “Satisfactory” on L.1 twice, but Chris is struggling, earning “Not yet” on both attempts. Wouldn’t it be nice to see that information in a simpler way?

The solution to create summary columns. These are columns that use spreadsheet calculations to summarize a student’s progress on each standard. Or, if you’re using Specifications, they summarize the number of assignments a student has met the specifications on. Generally, these columns automatically calculate the number of times a student has earned a “Satisfactory” mark on a standard. You can then use that data to decide if they’ve earned credit for that standard (for example, if you require students to earn multiple “Satisfactory” marks per standard), or count up how many standards they’ve met.

Here is the same spreadsheet as above, scrolled sideways to reveal my “Standard Summary” columns. I place these far off to the right, beyond the columns I expect to need for entering quiz data:

In this example, Alice has earned “Satisfactory” once on N.1 and N.2, and twice on L.1 (as we discussed above), but she hasn’t earned “Satisfactory” at all on L.3.

How do I calculate these summary columns? This is where — don’t run away! — we need to look at a spreadsheet command. There’s no need to get into serious spreadsheet programming, but there is one really useful command for alternative graders: COUNTIFS. The COUNTIFS command, well, counts things (such as marks) — but only if some other criteria are met.

Here’s the general form of the COUNTIFS command that you’ll enter in a summary column:

=COUNTIFS(range1, condition1, range2, condition2, ...)

Each “range” is a range of cells, and the following “condition” is a description of the condition that the cells in that range must meet in order to be counted. COUNTIFS tells you the number of cells that meet all of the conditions.³

Here’s the code for cell AC3, which contains a “1” for Alice in the N.1 summary column:

=COUNTIFS($G$2:$Z$2,AC$2,$G3:$Z3,">=3")

Let’s pull this apart one piece at a time. Here’s a picture of the quiz gradebook with each of these cell ranges highlighted:

The first range, $G$2:$Z$2, is the red box at the top: It’s the list of standards. The condition that these cells must meet is $AC$2, that is, the cells must contain the same text as cell $AC$2 (you can see that cell in the picture above this one). That text happens to be “N.1”. So this first part is basically saying “Only count cells in the Standard row that have N.1 in them”.

The second range, $G3:$Z3, is the blue box next to Alice’s name.⁴ It’s her list of marks on each quiz. The condition that these cells must meet is ">=3", that is, only cells with a 3 or larger will be counted. This second part is basically saying “only count cells that have a 3, which means ‘Satisfactory’.”

Together the two parts of the command say: “Find the columns that contain marks for standard N.1. Then look at Alice’s entries in those columns, and count up how many are 3 or better.” Only the columns that fit all of these criteria will be counted.

This command puts a “1” in cell AC3, because there is only one N.1 column where Alice has a 3. (It’s in Quiz 2.)

Once you have some good summary columns, you can use them to do many other things. For example:

• Most LMSs let you create custom columns in their gradebooks. If you want to use your LMS gradebook, you could periodically upload just the summary columns to give students a quick snapshot of their progress on each standard.

• You could add another column — after the summaries — that totals up how many standards a student has met at least one time, or two times, etc. We’ll see how this can be used to report grade progress in a future post.

Other options

What I’ve described here is a gradebook setup that I’ve refined and simplified over the course of many years. It is far from the only way to do things! I’ve also omitted many details and subtleties in the interest of providing a clear example.

For a completely different take on how to use spreadsheets with Standards-Based Grading, check out Tom Mahoney’s playlist of videos, where he demonstrates how to use “pivot tables” to record and summarize student progress in an extremely powerful and flexible way.

What if you’re ungrading, eliminating marks entirely? Unless you have an amazing memory, you’ll still need to jot down notes about student progress and a summary of feedback you’ve given. You can do this in many ways, and a spreadsheet isn’t required — a simple text file with each student’s name, and a blank spot for text comments below it, might be enough. I still use a spreadsheet even in my ungraded classes, because it provides an easy way to organize my comments. I have a column for each assignment, and I enter text notes that summarize my key feedback. For example, here’s a real comment from a past ungraded Geometry class:

3: A few details. 2: Better, but still has disagreement in angle names, circular definition of D. 1: Diagram & text disagree; WLOG issues

The numbers refer to revisions, with “1” being the first submission of the work. By the end, the student didn’t have a perfect solution, but only “a few details” remained, which was my shorthand for “nothing important”. I realize that these only make sense to me, but that’s OK — that’s all I need to review when looking at a student’s final portfolio.

Now that the gradebook is set up, in a future post we’ll see how to use this data to determine final grades.

1. The syllabus has a complete list of standards, including the codes, and I always write out the full standard on any assignment. I use only the codes in the gradebook to save space.

2. This also illustrates one way to record reassessments. Another option, especially if students can revise previous work, is to simply replace the previous number with a new one. This has the disadvantage of destroying your record of a student’s previous work, but it has the advantage of being quite simple.

3. The COUNTIFS command exists and works fundamentally the same way in all spreadsheets that I know of, including Excel and Google Sheets.

4. The dollar signs in some of these cell ranges have to do with “locking” the cell ranges if you are copying them into multiple cells, so that they don’t change. In the first example, $G$2:$Z$2 means that this range will always be exactly the same, no matter where this formula is copied. That’s what we want, because the location of the “standards” row never changes. In the second part of the command, $G3:$Z3 means that the columns (G and Z) will always remain the same due to the dollar signs, but the row (the 3’s) will change if you copy this command elsewhere since there are no dollar signs on the 3’s. That’s also what we want, since an individual student’s marks are always stored between columns G and Z, but each student has their own row. If we copied this formula to Chris’s row, the 3’s should automatically become 5’s.