# How Teachers Can Automate their Grade Book Using a Google Docs Spreadsheet

## An Automation Tool for Teachers

Rather than tackling a big issue in education, I decided use this Hub to share a tool that I have used for years in my capacity as an adjunct or part-time economics instructor at a local Community College. Holding down two jobs requires a large time commitment and if I am going to do a good job teaching and still have time to relax with my family it is important that I be efficient. The area which provides the greatest opportunities for finding efficiencies is the out of classroom paperwork of which grading is one of them.

As a teaching assistant while in graduate school in the days before personal computers, I had the experience of recording grades in a paper grade book with a pen and then using a calculator to total each student's grades convert them to a percent and record the corresponding letter grade.

## Google Docs are Free and Portable

However, the advent of the electronic spreadsheet changed all that
and when I began teaching part-time I decided that the job of tracking
and calculating grades could be done faster and more accurately on a
spreadsheet. I have used a variety of spreadsheet programs over the
years, beginning with the old *Lotus 1-2-3* for *DOS* and all of these work the same with only a slight variation in the way the calculations are written.

In recent years I have found the Spreadsheet in *Google Docs*
to be the best in terms of cost (it is free) and versatility - it is on
the Internet and thus accessible 24/7 from any computer with Internet
access. As a part-time instructor I don't have an office at school and
this having the grade book available in my Google Account on the
Internet allows me to access it from home, school or work at my day job.

## Begin With a Google Account and Add Google Docs to It

To get started you first need a Google account with the *Google Docs* feature.

This is free and can be obtained by going to *Google.com*, clicking on *Sign In* in the upper right and then clicking on *Create and Account Now* link. You do not have to use a *Gmail* address for your sign in as you can use any email address as your user sign-in.

Once you have created your account, simply click on *More* which appears in the upper left next to various Google services such as Gmail, Shopping, etc. and then click on *Documents* in the drop down box. This will add Documents to the Google services available on your account and from this point on simply click on *My Account* and select *Docs* to go into your documents and spreadsheets (spreadsheets are a part of documents).

To create a new document or spreadsheet, simply click on the New button at the top and then click on Spreadsheet in the drop down box and a blank spreadsheet will appear. It is a good idea to click the *Save* button at the top right and give the spreadsheet a name - once named, the spreadsheet will automatically save as you work in it.

## Step by Step Instructions for Creating Grade Book

For this example I created a simple spreadsheet with columns for five assignments and tests, one column for a student count, one for student names and three for grade calculation. Refer to the photo below for illustration. The instructions will continue below the photo.

- **STEP 1 - CREATE A TITLE LINE AT THE TOP**: Place the
mouse pointer in cell A1, click and hold the left mouse button and drag the
pointer across the first row of cells to cell J1. Release the mouse button and
the first ten cells at the top should be highlighted. Next, point to the merge
button (see Example 1 below) and left click once. This will cause the ten cells
to merge into a single large cell.

Repeat the Process in row 3. You now have two lines for a title and subtitle. You can center your titles by clicking on the cell in which you want to center the text and then click on the down arrow next to the Alignment button which is immediately to the left of the Merge button on the Formatting Bar and then click on the Centering Button.

To bold the title, move further left on the bar and click on the black
*B* button, and to change the font size go one more button to the left and
click on the down arrow in the Font Size button (which reads *10 pt* in the
example) and click on the desired font size.

- **STEP 2 - ADD COLUMN HEADINGS IN ROW 7**: In *Row
*7 I entered my column headings in the ten cells. You can bold and center
these within the cells using the same process.

** INSTRUCTIONS CONTINUE AFTER THE PHOTO
BELOW**

## Sum Scores for Each Student

**- STEP 3 - CREATE CALCULATIONS TO SUM STUDENT SCORES:** In
*Row 8* enter the maximum point score beneith each test and
assignment.

Click on cell *C8*, click on the *Sum* Button (last button
on right on Button Bar in photo below), select *Sum*, highlight cells *F8*
through *J8*, place a right parenthesis ")" after the *J8* in
cell *C8* and hit *ENTER*. This gives the total points for the
course.

Leaving the cursor in cell *C8*, click *Ctrl C* to copy
the contents of cell *C8* (you can also right click your mouse and select
*copy*).

Highlight cells *C10 to C19* and click *Ctrl V*
(you can also right click your mouse and select *paste*) to paste the
formula in cell *C8* into these cells. Of course, instead of summing
cells *F8* through *J8*, the formula in each cell will adjust to
sum the cells in columns *F* through *J* in its own row.

** INSTRUCTIONS CONTINUE AFTER THE PHOTO
BELOW**

## Calculating and Displaying Student;'s Percent Score

**- STEP 4 - CALCULATE PERCENT SCORE FOR EACH STUDENT -** In *Cell D10* type the formula: ** =C10/$C$8** (see photo below) which tells the computer to divide the number in

*Cell C*to its left by the number in cell

*C8*. Using the dollar signs

*($)*in front of the

*C*and the

*8*causes the computer to keep this cell constant in the formula and not adjust for each new row, as we want to divide a student's total points which are in the cell in

*Column C*immediately to the left of the calculation by the constant number that is in cell

*C8*.

** INSTRUCTIONS CONTINUE AFTER THE PHOTO BELOW**

## Change Decimals to Percentages

**- STEP 5 - DISPLAY DECIMALS IN COLUMN D AS PERCENT -** to
change the display of the numbers in Column D from decimal to percent
place the mouse pointer in Cell D10, click and hold the left mouse
button and drag the pointer to Cell D19. This will highlight the
column.

Next, place the mouse pointer on the down arrow to the left of the button labeled 123 on the Button Bar above (this button is located just below the word Format in the Title Bar).and click once to get the drop down box (see photo 2 below). Click on the 10.12% to select the un-rounded percent option.

By choosing the un-rounded percent display rather than the rounded display option above it you will cause the computer to display the student's exact percent score which is what the computer will use in calculating the letter grade in the next column. This becomes critical when the difference in a student's score is one half of one percent or less than the percent grade needed for the next higher letter grade.

For instance, if you choose the rounded percent display a student with a percent grade of 79.6% will display as 80% in Column D but Column E will display a letter grade of C because the computer will base the letter grade on the exact percent score and not the rounded score.

*( NOTE:
if you want to use the rounded score and have the letter grade match
that score you can accomplish this by lowering the threshold for each
letter grade by a half a point making an A equal to 89.5% or greater
rather than 90% or greater, a B equal to 79.5% or greater rather than
80% or greater, etc.) *

**INSTRUCTIONS CONTINUE AFTER THE PHOTO BELOW**

## Calculating the Letter Grade

**- STEP 6 - CALCULATE STUDENT'S LETTER GRADE IN COLUMN E ** - (see photo below) Place the mouse pointer in Cell E10 and click and enter the following formula in Cell E10. You have three options for doing this:

1 Click on the down arrow on the Sum button on the Button Bar above and select the *IF* statement and build the formula using the layout that comes with the function.

2 Type the formula below into Cell* E10* (NOTE: for the formula as it exists below to work properly your spreadsheet MUST be set up EXACTLY like my example cell for cell since the formula below references specific cells in the spreadsheet).

3 Highlight the formula below on your screen, key *Ctrl C* (or right click your mouse and select *Copy* from the drop down menu), go to your spreadsheet, point to Cell *E10* and key *Ctrl V* (or right click and select *Paste* from the drop down menu) to paste it into Cell *E10* on your spreadsheet (this worked on a Google spreadsheet when I tested it - I had previously set up the spreadsheet exactly like the one I have displayed in the example lacking only the formula in cell *E10*).

**=if(D10>=.90, " A",if(D10>=.82," B",if(D10>=.74, " C",if(D10>=.65," D"," F"))))**

Let me explain how the formula above has been constructed. The IF statement in all spreadsheets is basically logical TRUE/FALSE test in which we have the computer evaluate a statement and provide one answer if the statement matches our definition of True and a different answer if the statement is not True. In the grading case we want the computer to evaluate and select one of five rather than one of two options. We do this by having it first test for an A and return an A if the percent score is equal to or greater than the lowest numeric score for an A and, if the statement turns out not to be true (i.e., the numeric score is less than the threshold amount for an A) to come back and reevaluate the number to see if it meets the test for a B and so on until we reach the D score where it comes back with a D if true and an F if not true (i.e., the threshold score is less than that needed for a D).

In doing this it is imperative that we not only have the same number of right parenthesis ")" as left parenthesis "(" but also that each one be in the correct position.

Second it is imperative that we separate the end of the test, the true statement and the false statement each with a coma.

Third if we are using letters or alpha characters in out test or our answers that we identify them by putting double quotation marks on either side of them. Further, there can be no blank spaces in the formula and if we want a blank space (as I have done by placing one of them before each letter grade) that it be treated as an alpha character and set off with double quotation marks.

Finally, we have to use one of the following sign combinations for our evaluations:

**=** equal to

**>** greater than

**<** less than

**<>** not equal to

**>=** greater than or equal to

**<= **less than or equal to

When using these characters we must be very specific. Thus if we write that an A is =90 then only scores of 90 will receive an A, any other score, including 100 will NOT be an A. If we write that A is >90 then every number greater than, but NOT INCLUDING 90 is an A (90.1 is an A while 90 is not)

This formula is placed in Cell E10 and looks to the number found in the cell immediately to its left. Even though we have instructed to display the student's score in Cell D10 as a percent, the computer will still read it as a decimal so all of the numbers we write in the formula must be written as a decimal - thus an A is >=.90, NOT >=90% and NOT >=90.

Once you have completed writing and checking the formula in Cell E10 hit ENTER to save it in the cell.

Then place the mouse pointer in Cell E10, key Ctrl C to copy the cell, move the pointer to cell E11, click and hold the left mouse button and drag the pointer to Cell E19, release the left mouse button and key Ctrl V to paste the formula in each of the cells from E11 to E19. The computer will automatically change the cell reference number where it looks for the numeric score from D10 in the original formula in Cell E10 to the number of the cell immediately to the left of each of the cells in column E in which it was pasted (thus the formula in Cell E15 will reference cell D15).

## Finished and Ready for Work

The grade book is now complete and should look like the sample below except that yours will have your students' names and scores.

You can add scores for additional tests and assignments in the columns to the right - just be sure to adjust your formulas in Column C to include cell number of the last column you use (also don't forget to do the same for the total possible points in Cell C8 as well as to add the maximum score for each new test or assignment in row 8 of the column beneath the test or assignment name).

Since I teach college courses, I set up my spreadsheet with just one final score for each student in each course I teach. If you teach K-12 you may want to create a spreadsheet for each subject or class for each grading period.

Because it can take some time to create this spreadsheet, I have made a practice of re-using the same sheet by copying it. This can be done in one of two ways, either take the spreadsheet from the class just completed and save it under a new name and then deleting the students' names and scores (but leaving the headings and calculations in place) or create the sheet and rather than using it save it under a new name using the new sheet for the current class and saving the original as a template to make copies from in the future.

Finally, for back up you can export or download the spreadsheet from Google to your home and/or office computer. The downloaded copy can be in the form of a MS Excel file, an OpenOffice Spreadsheet file, a CSV file format or PDF format. When exported in an Excel or OpenOffice format you can continue to use it off line as a spreadsheet (however, it will not automatically update the online copy - however, you can always delete the online copy that hasn't been updated and then upload the current file from your desktop or laptop to Google Docs and continue working online).

**© 2009 Chuck Nugent**

## Comments

Thank you so much for posting this!!! This really helped me in making our requirement.

Great post..Google docs is really great ..Thanks

Very informative and useful hub for teachers. Nowadays, since technology is rapidly growing, people must also go with the flow and being a computer literate is a great foundation. But, it seems that teachers here in Philippines don't have such skill and that's one of the reasons I think, caused our country's poor education.Only the millennium graduates have proper knowledge to this. However, Philippine teachers here are doing the best they can to provide the best learning for their students. Salute to all teachers.

I have several in-laws who are educators and I'm sure the information you provided here will be of great value to them. Thank you.

Useful information which is really ideal for both student sand teachers.

I read through this post hoping that you knew a way to share individual columns or rows. Unfortunately Google Docs does not allow it.

I would imagine that the best way to get around this is to give every student a random 3 or 4 digit number. You can make public a copy of this spread sheet with the student's numbers opposed to the names, with the order rearranged. That way they all have 24 hour access to their progress as well as an anonymous comparison to other students in the course. You could also allow the students to create their own user names opposed to numbers. Many of my college grades were posted on my professors's doors in a similar manner.

Thank you so much for this wonderful resource.

Very cool. We also use Goog docs to communicate dept to dept and from middle school to high school. Gotta love the goog. You know what they say. There is God and there is Goog. Some say that anyway.

This is great and appreciate your putting it up. Here's a question:

Given the collaborative nature of Google Docs, have you found a way to securely share each student's grade with them -- and only them. For example, can you share Row 2 with Student 2, Row 3 with Student 3, etc?

Thanks for the clear instructions...this is exactly what I needed!

Nice hub!

Excellent hub,Chuck.

I have linked this article on my Great Expectations Blog.

This is a great hub, and very useful for teachers! It always helps to find new ways to use technology, and the fact that Google offers them for free makes them that much more useful for teachers!

That's a great idea you have given and explained so well.

This is very useful for teachers to be sure. I never would never had thought of using google for this.

Like you, I've used spreadsheets - Lotus 123, QuattroPro and Excel - for my gradebooks. The instructions you give for the Google Docs spreadsheet are very close, if not exactly, to Excel so it's no problem applying them to the Google Doc. As you point out, the advantage is that they're available anywhere. I keep my gradebook on a thumb drive which requires finding a USB port on a computer I'm working on (often they're in the back of the computer - very inconvenient) and I also backupit to my hard drive on my office desktop computer. With Google Docs I won't have to worry about losing the thumb drive and backing up.

Thanks for some good ideas.

Nice

Spreadsheets are great. Too bad teacheer education does not include teaching of computers and technolgy. I have developed an attendance tracking for a teacher who needed one that would track attendance, parent participation, and the days they took snacks and or milk. I was able to combine several different paper data sjeets into one spreadsheet and gave them monthly, semester and years totals for their reports. I will have to check out Google Doc. Thanks for the great Hub page and information. Your directions should be easy to follow for any one. I hope teachers see this and try it.

21