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

More by this Author


Comments 21 comments

Demetrio Garcia Jr. 5 years ago

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


htodd profile image

htodd 5 years ago from United States

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


JelaV profile image

JelaV 5 years ago from Philippines

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.


glorgeousmom profile image

glorgeousmom 5 years ago from Philippines

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.


crystolite profile image

crystolite 5 years ago from Houston TX

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


Chuck profile image

Chuck 6 years ago from Tucson, Arizona Author

Random Reader - you are correct in that Google does not, as far as I know, allow you to share individual columns or rows.

As to your suggestion for getting around this to make grades available to individual students, I recommend that you consult your institution's policies concerning FERPA (Federal Educational Rights and Privacy Act) compliance. The school I work for, and others I know of, consider what you are proposing to be a violation of FERPA which could expose the institution to a lawsuit over violation of a student's privacy rights.

Like you, my teachers in college posted grades using only Social Security numbers (another violation these days) or by assigned numbers to identify students. None of us had any problem with either of these but times have changed and laws have become more complex.

Thanks for your comment.


Random Reader 6 years ago

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.


Sue Goldsack 6 years ago

Thank you so much for this wonderful resource.


Ryan Clinton profile image

Ryan Clinton 6 years ago from room17art@yahoo.com

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.


Chuck profile image

Chuck 6 years ago from Tucson, Arizona Author

Susan Gautsch - You can share the full spreadsheet or the entire contents of the folder that it is in but I don't know of any way to share a single row or column.

Unless someone else has a practical way to accomplish with the Google Spreadsheets what you are trying to do (and if anyone does, please share it here) I can only offer two suggestions for alternatives.

1 If you have access to a Learning Management System (LMS) or Course Management System (CMS) such as Blackboard, WebCT, Moodle or similar product these generally have a grade book feature (at least Blackboard & WebCT, both of which I have used, have) in which you can record and view everyone's grades in one place but students can only see their own grades. I have heard of Moodle, which is open source software that appears to be available to educators as a free download (here is a link for more information http://moodle.org/ I can't tell you any more about Moodle as I have no experience with it and have only seen references to it in online teaching articles dealing with K-12 online education.

2 A second option would be to create a spreadsheet on which each student can record their grades as assignments and tests are returned and then EMAIL (DON'T SHARE) this document to each of your students with instructions on how to use it. I say "Don't Share" it because there will always be at least one who will begin using the shared copy rather than making a copy for their own use and, as soon as that student starts posting their grades they will be visible to all. If you are teaching in the United States, this exposing of a student's grades to other students will put you in violation of the FERPA Law (Federal Educational Rights an Privacy Act).

If you create and share a separate spreadsheet for each student you will stay within the law but will end up with twenty to thirty or more grade books depending upon how many students you have in your class which would be management nightmare and huge consumer of your time.

Good Luck


Susan Gautsch 6 years ago

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?


Charlie 7 years ago

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


Dang1 7 years ago

Nice hub!


Chuck profile image

Chuck 7 years ago from Tucson, Arizona Author

Purple Perl - Thanks for your comment and for the link.

Chuck


Purple Perl profile image

Purple Perl 7 years ago from Bangalore,India

Excellent hub,Chuck.

I have linked this article on my Great Expectations Blog.


jdnyc profile image

jdnyc 7 years ago from California

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!


deccasandy profile image

deccasandy 7 years ago from Belmont

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


SweetiePie profile image

SweetiePie 7 years ago from Southern California, USA

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


jkfrancis 7 years ago

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.


ilovehubbing profile image

ilovehubbing 7 years ago

Nice


bobmnu 7 years ago

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.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working