Grading with Excel

Downloading the Rosters

1. Go to Rosters Via the Web: registrar.ucdavis.edu/rosters/. (As this is a place that you will likely return to again and again, I recommend bookmarking it (CTRL+D)).

2. After signing in with your user name and password, choose the way you would like to access your roster (CRN or Course Section Number).

3. Then enter the information required, and -- this is important -- choose the comma delimited (Excel) option.

4. You will be asked where you want to store your roster (I usually choose the desktop; it is more difficult to lose if you store it there).

5. Then, you can open it and save it as an Excel file.

6. Then, you can manipulate the information pretty freely. I usually delete everything but the students’ names (and a portion of their ID numbers if I think I might want to post the grades). Then I add a row up top for the assignments.

The spreadsheet should look like your gradebook: the names of students will be listed in a column on the left; the assignments will be listed across the top; the grades will be entered in table fashion.

7. I also add a header that lists my name, the course number and section, and the quarter taught. You can simply type these into some of the cells at the top of your worksheet, or, if you wish to be a bit more formal, you can add the header by selecting Page Setup under File.

Grading

I put letter grades on the students’ papers, but I translate those grades into numbers when I record them in my grade book. It doesn't matter what scale you use as long as you are consistent.

I usually type my grades into the computer as the quarter goes along. Although I don't usually actually do this, it would be wise, after recording grades for each paper, to print out a copy of the spreadsheet so that in the event of a techno disaster, you will have hard copies of grades.

When you are ready to calculate, put your cursor in the first blank cell after the first student's last grade and then type in this formula:

=(____* B2+_____*C2+_____*D2+______*E2, etc.)/100

The blanks above represent the percentage (5%, 10%, whatever the assignment was worth. Just type the number, and not the % symbol); the B2, C2, D2 etc. represent the cells in which the grades have been typed, the * is the symbol for multiplying, and the /100 divides the whole number by 100 so that the grade is rendered as a percentage of 100.

If you prefer, you can make this formula even simpler by simply multiplying each of the students grades by the percentage it is worth, e.g. 10%=10/100=1/10=.1; 20%=20/100=2/10=.2, etc. As arithmetic rules specify "multiplication and division first," this eliminates the need for parentheses, dividing the entire sum, etc. Here, obviously, make sure that the fractions you use as multipliers add up to 1 or 100%.

= .1*B2 + .2*C2 + .2*D2...etc.

So...now you have a final grade computed, and you are thus in a good position, as once you have the final grade for the first student on the list you essentially have them all. You do not have to type in the formula again 24 more times. Like a word processor, Excel has copy and paste functions that make copying the contents of cells easy.

Put the cursor on the first student's final grade, then select the whole column below.

Go up to Edit and select "Fill". Then select "Down" (the direction you want to fill in). Then each student should have a percentage out of 100. If you are worried, you may want to spot check using your calculator, handy arithmetic, or however you would figure out a grade. But, usually it is immediately obvious if you've left something out-- you have accidentally transformed your best student's "A" into a "D", or some such.

If this happens, take a look again at your formula and the grades you entered and check to make sure that you've done everything correctly. Most of these errors are easy to fix. Like Word, Excel is pretty forgiving, and it is not usually very difficult to track down errors.

Computing Average Grades

Although at this point, you know all you need to compute your grades, if you want to find an average grade for a particular assignment or for your final grade, here's what you do:

Put the cursor in the cell underneath the last grade in the list.

Type:

=AVERAGE(

or

=AVG(

Then highlight all the cells in that column that you would like to have averaged. (If you would like to leave some cells out of the average, press CONTROL as you are highlighting, then begin highlighting at the bottom of the column, stop highlighting when you get to a blank cell or a cell you don't want to count in your average, then, when you get past the unwanted cells, resume highlighting). Then type the closed parentheses and hit enter.

or

Simply add up the column/row you want to average and divide by the number of entries.

=(A1+A2+A3+A4...)/4

Any of these methods will give you the average.

 

Conclusion

Clearly, there's a great deal of flexibility here: there are many possible ways to do the same task. Most anything that you would want to do, I think you'll find that you can do, and once you get the hang of it, I think you will find that you will never calculate grades by hand again!

(Co-Authored by Anne Fleischmann and Michael Kolody)