YOUR GRADE SHEETS TO STUDENTS
By Rich Cameron
By far, the worst
part of teaching has got to be grading. Unless you issue all A's these
days, someone is going to be unhappy. One way to stave off the complaints
is to make sure students are often reminded how they are doing in the
class and what scores you are using on which to base a grade.
But I disdain the
old method of posting grades on the wall, where students can compare
their grades with other anonymous students in the class. Even with student
ID numbers, unless you do some kind of sorting, students can determine
who is getting what grade. And I don't like posting EVERY score on EVERY
assignment for EVERY student to see. I prefer, instead, to give students
individual grade reports, with just their scores and evaluations. Students
should be comparing themselves against themselves, not other students.
But printing out individual grade reports on a regular basis is a terrible
waste of paper and time. Now that most students have e-mail, sending
out individual grades reports electronically is an outstanding alternative.
And with proper use of a spreadsheet program, such as Microsoft Excel,
sending weekly, individual grades reports can be made easy.
Of course, your
students must have e-mail for this to work. And students must be encouraged
to read their e-mail. At Cerritos College, like at many colleges, students
are eligible for a campus e-mail address when they enroll: They just
have to activate it. So in each of my classes the first assignment of
the semester is to activate their address and send me a short e-mail
message with their name and which class they are in. Those with external
addresses can forward their campus e-mail to them and I don't have to
worry about keeping track of addresses when people fail to pay their
bills or switch Internet providers.
From these messages
I build a database for each class of e-mail addresses. Even in today's
electronic information age, this often is the first Internet experience
for many students, so it is important to respond to the messages, even
if your response is to say, "I got your assignment."
To encourage use
of e-mail I send copies of relevant articles and weekly reports of what
we'll be doing in class. Students can pass the class without using e-mail,
but I try to enhance the course experience for those who DO read their
Once you've got
students using e-mail, you still face the challenge of creating individual
grade reports to send out. Creating an Excel grade sheet to do this
for you takes a bit of up-front work, but once built can be used from
semester to semester. The complexity of your grading system and your
ability to use spreadsheets will dictate how informative your grade
reports are, but this article will show you how to build a relatively
simple grade sheet. You can enhance them from there.
Grade sheets should
perform at least three functions: You must have an easy way to record
grades, a way to view the summary grades for the entire class, and a
way to generate individual reports automatically. Spreadsheet programs
that allow multiple-page worksheets, such as Excel, make this relatively
easy: You create a page to enter scores and have the summary pages and
the individual pages pull information from that page to create your
Let's start with
five students with five assignments each. Because this example is simple,
the enter data function and class summary can both exist on the same
page. If you have a variety of score generators, such as tests, homework,
papers, and projects, you might find it easier to create multiple enter
data sheets, one for each type of assignment. In such a case, you would
want to create a separate class summary sheet. See Figure 1.
ONE : THE SCORE SHEET
The scores in Row
Three, Columns E through I represent total points possible for each
assignment. Enter them only as assignments are graded and recorded.
The number in Cell B3 represents the total points possible for the five
assignments The formula is:
I've found it particularly
useful to name cells that contain totals like this. For instance, in
this example I have named B3 as "Ttl_points". E3 is "assign1", F3 is
"assign2", G3 is "assign3", etc. This makes calculations easier to understand
months from now and also reduces the chance of errors. Use the INSERT-NAME-DEFINE
option to create names. It is also wise to rename the sheet. While it
is not needed yet, once you create multiple pages you'll want help in
figuring out what each one represents. To rename the sheet use FORMAT-SHEET-RENAME.
Column B shows
each student's totals. The formula resembles the one used to total the
Column C shows
the each student's percentages. If you've named B3, then the formula
for C5 would simply be:
Column D shows
each student's letter grade. It is nice to have Excel determine this
for you, too. The formula makes use of if-then statements and looks
Note, there are
no empty spaces in this calculation. Unfortunately, the calculations
needed to create + and - grades are too many to work in a single simple
Next create the
student reports. Now, this can be time-consuming if done improperly.
After all, you need to create a page for each student in your class.
The trick is to create one page correctly so that you can simply duplicate
it and make minimal changes.
Go to Sheet Two
(see tabs at the bottom of the worksheet). If there are no extra tabs
at the bottom add a sheet by selecting INSERT-WORKSHEET. Let's rename
the page right away. I'm going to rename it as "5." The reason for this
is will become apparent soon. To rename the worksheet select FORMAT-WORKSHEET-NAME.
The first thing
we'll want on the page is the student's name. Rather than type in the
name, we want Excel to do it for us by using a calculation. We want
Excel to pull the name from the "Enter Scores" page. There is a simple
way to do this. Click in cell A1 and type an equal sign (the beginning
of a calculation). Now click on the tab at the bottom of the page that
takes us back to "Enter Scores." Click on cell A5 where John's name
is. Then click on the green check mark in the formula editing area at
the top of the screen. The following calculation is created:
and John's name
now appears in cell A1 of page 5. Here is where we start to make little
changes that are going to make recreation of our pages easier later.
Add dollar signs in front of the A and the 5, thusly:
More on why we
do this later.
Next we create
a layout to report the scores we have for student John. I've found that
vertical layouts work better than horizontal layouts for our ultimate
goal of e-mailing grade sheets. I've also found that students want the
bottom line first, so I report the semester percentage and the letter
grade first, then the individual scores. See Figure 2.
TWO. THE STUDENT REPORT
and letter grade are pulled from the "Enter Scores" page the same way
we got John's name. Likewise, the dollar signs have to be inserted into
B3 ='Enter Scores'!$C$5
C3 ='Enter Scores'!$D$5
are drawn from the "Enter Scores" page in like fashion, EXCEPT that
we do not want to add the dollar signs. Anything that is drawn from
the "Enter Scores" page that will ultimately be identical on every student
report does not use the dollar signs. Anything that has to be unique
to a student does. So draw the actual scores for the assignments from
the "Enter Scores" page, but DO add the dollar signs for cells C5 through
Creating a student
report can be that simple. But let's give the student more info. Students
may not know, for instance, the significance of the scores if they don't
remember how many points were possible on each assignment. We can use
a calculation to remind the students. Figure 3 reminds the students
what was possible.
FIGURE THREE. ADDING POSSIBLE SCORES
Excel uses the
ampersand to tie together two or more pieces of information in a calculation,
in this case contents from two different cells and a piece of text.
In addition, a slash has been added to separate the two scores. The
formulas for cells C5 through C9 would now look like this:
The slash is added
with &"/" and the possible score is added with &assign1. Remember, "assign1"
is the name we gave to a cell on the "Enter Scores" page (cell E3).
No dollar signs are needed with our additional material because the
information will be the same on all student pages.
We can help the
student understand the significance of the score by creating a calculation
to show the percentage that 22 points out of 25 represents. We'll do
this in cells D5 through D9 with the following formulas:
D5 ='Enter Scores'!$E$5/assign1
D6 ='Enter Scores'!$F$5/assign2
D7 ='Enter Scores'!$G$5/assign3
D8 ='Enter Scores'!$H$5/assign4
D9 ='Enter Scores'!$I$5/assign5
Note that the dollar
signs must be added to the E5, F5, G5, H5, I5 (See Figure 4). The numbers
in the cells have been formatted to show up as percentages rather than
decimals. You can customize the appearance of numbers by choosing FORMAT-CELLS-NUMBERS
and choosing an appropriate percentage format.
FOUR: ADDING PERCENTAGES
Our student report
is done and ready for e-mailing. Whenever you update the "Enter Scores"
page this page will automatically be updated. Before we learn how to
e-mail it to students, we need to create pages for other students in
Putting in all
of those dollar signs was a bother, but now is where it pays off. You
may have noticed that we had a lot of $5s on the page, and that "5"
was the name we gave to the page. That's because all of the unique information
for John was on row 5 of the "Enter Scores" page. All of the unique
information on Mary, our next student, is on row 6. All we have to do
is duplicate John's page and change all the $5s to $6s to create her
Select the entire
page for copying (EDIT-SELECT ALL) and copy it (EDIT-COPY). Click on
the tab at the bottom of the worksheet for Sheet Three (or add a new
sheet if necessary). Click in Cell A1 and paste the clipboard onto the
page (EDIT-PASTE). Let's let the program make the changes for us. Choose
EDIT-REPLACE.... In the "Find What" field type in $5. In the "Replace
With" field type $6. (See Figure 5). Click on the "Replace All" option
and, viola, Mary has her own page. Now, rename the page so that her
page is easy to find.
FIGURE FIVE: CREATING A SECOND STUDENT REPORT
steps to create pages for as many students as you have in a typical
class. If you are creating this worksheet before the start of a semester,
you may want to simply type in "Student 1," "Student 2," "Student 3,"
etc. for now. Note: While it would be nice to have the student's name
appear on the tab at the bottom of the page, there are two problems
is no way to automatically name a page based on the contents of a
cell, or at least I haven't found one. You would have to type in the
name manually, which would be time consuming and would require changes
to every page every semester.
Second, by the
time you add 20-50 pages to your worksheet you'll find long names,
even if only four or five characters, will spread out too much. Numbers
are short and easy to locate by paying attention to the rows that
contain a student's unique information.
We're ready to
mail the grade sheet. You could, of course, send grade reports as an
attachment to an e-mail message, but that would require the student
to have a spreadsheet program to read the attachment. What we've created
can be copied and pasted into a message itself.
You've got to have
both your spreadsheet open and your e-mail program open. Any e-mail
program that allows copy and paste will work. I imagine there must be
one to two out there that don't allow copy and paste, but this is a
pretty basic function for computers and most will allow it. Figure 6
uses an America Online window.
To mail the message,
first copy the individual report. (Select the area to be sent. In our
example it would be cells A1 through D9. Then choose EDIT-COPY.)
Switch to the e-mail
program and address the message to the student. You may want to create
a column on the "Enter Scores" page to record a student's e-mail address
and then create a calculation on the student's page to pull the address,
say to cell A3. That way the student's address is right in front of
you. Many e-mail programs also have built-in address books. A problem
with this, though, is that after each semester you either have to clean
out your address book or you'll end up with a lot of out-of-date, unneeded
You can even timestamp
your reports by including the following formula into a cell on the report
and using the FORMAT-CELLS-NUMBER-DATE
option to format it.
Paste the report
into the message window.
SIX: E-MAILING THE REPORT
Repeat the above
steps for each student. With a little practice, a class of 30-40 can
be done in less than 15 minutes.