Keep track of grades in Microsoft Office Excel 2007

Use Microsoft Office Excel 2007 to keep track of grades and create grade reports for your class.

Keep track of grades in Microsoft Office Excel 2007

Office Excel 2007 sets you up an initial spreadsheet grading system, then save the Excel worksheet as a template and use Office Excel 2007 to calculate, providing the data views you need, and producing grades and reports for every class. Watch the video

Save time with an electronic grade book

To manage grades, Office Excel 2007 can help you:

  • Enter, view, and change data. The basic function of an Office Excel 2007 worksheet is to help you lay out your data so that it is easy to understand and work with.
  • Do the math. The worksheet can have columns or rows that contain formulas for efficiently adding or averaging lists of numbers and for displaying grades or percentages. You enter individual test and homework scores, and the final grades can be automatically calculated.
  • Manage the data. You can convert the data to an Office Excel 2007 table and then use the useful table tools to help you manage your class’ database. You can even go beyond simply tracking grades to create a master database of student information, including names, quiz scores, averages, phone numbers, seating charts, and more.
Keeping track of grades is easy with Microsoft Office Excel 2007.

You can create a grade book from scratch, download a grade book template from Microsoft Office Online, or create your own grade book that is based on a template that you download. To create a grade book similar to this sample, follow the steps below.

Enter, view, and change data

To create your grade book, think of the rows in the spreadsheet as student records, and think of the columns as fields for holding a grade or score, or a formula for calculating averages. For example, create a field for each graded assignment, test, quiz, and oral report. Also, create fields for any other item that can affect the grade, such as participation or cleaning up after lab. Then, add fields for totals and averages.

Before you add actual student names and grades, save the worksheet as a template. Then, you can use the template to create a new workbook for each of your classes.

  1. Enter placeholder student names in column A, starting in cell A3.
  2. In row 2, which is the header row that contains the names for each column or field, enter these names:
    1. In A2, enter Student Names, or since that is obvious, enter an identifying name for the data, such as the name of the class.
    2. In B2, enter Total Average. This column will contain formulas for calculating the overall grade percentage.
    3. In C2 through F2, enter group names for the types of graded work, such as Quiz/Lab, Tests, Homework, and Participation. Except for Participation, these columns will contain formulas for calculating grades of each type. You must enter Participation scores manually because no formula is necessary.
    4. Starting in G2, enter the names of each item that is graded, such as quizzes, tests, and homework assignments. Group the names, so that items of the same type are in adjacent cells.
  3. Use Fill Color formatting to color code the item groups. For example, color the homework cells red and quiz/lab cells green.
  4. To give the names a vertical orientation, select the column names. On the Home tab, in the Cells group, click Format and then click Format Cells. And then, on the Alignment tab, change Orientation to 90 degrees.
  5. Save the workbook as a template.

When you are ready to set up a grade book for a class, use this master template to start a new workbook, and then replace the name placeholders in column A with actual names.

Do the math

Add formulas for calculating averages in columns B through E. The formulas will divide the sum of a student's scores by the sum of total possible scores to arrive at a percentage score.

  1. Starting in G1, enter a zero over each graded item name. These cells will contain the total possible score for each item and will be used to calculate averages. In the first item in each group, enter a placeholder number above zero. Since we will be using these numbers for division, Office Excel 2007 will display an error if we try to divide by zero.
  2. Select cell C3 (the cell below Quiz/Lab).
  3. Type =SUM(
  4. Select the Quiz/Lab cells in row 3, and type )/SUM(.
  5. Select the Quiz/Lab cells in row 1.
  6. Click the formula and add a $ (dollar sign) in front of each letter and number in the range. The formula should look similar to this: =SUM(G3:L3)/SUM($G$1:$L$1)
  7. Press ENTER. The cell displays 0.
  8. Click the cell, and then, on the Home tab, in the Number group, change the number format to Percentage, and click the Decrease Decimals button twice. The cell displays 0%.
  9. Select the cells in the column below the name to the end of the student names, and then press CTRL+D. The formula is copied into all of the cells. When you look at the formulas in the cells, you will see that the row numbers in the first range reflect the row in which the formula is located. But because we used the dollar signs in the second range, the row numbers continue to point to the total possible numbers.
  10. Repeat steps 2 through 10 for the rest of the groups (Tests and Homework).
  11. In cell B3, type =AVERAGE(.
  12. Select the group average cells in row 3 (Quiz/Lab, Tests, Homework, and Participation).
  13. Type ), and press ENTER. The formula should look similar to this: =AVERAGE(C3:F3)
  14. Format the cell to display percentage with no decimals, and then copy the formula to the other cells in the column.
  15. Save the workbook as a template.

When you enter actual grades in the student cells, start by entering the total possible. For example, if the total possible for quiz #1 is 20, enter 20 in the Total possible cell above quiz #1, and then enter the actual student scores in the cells below the header. The formulas display the average score as a percentage.

Teacher Tips

   

  • Using "Track changes" in Microsoft Word can save a teacher many hours grading and reviewing students’ papers.

    Make a comment or suggestion on how to improve a passage, and return the document to the student. When the paper is revised, you can jump right to the place that needed improvement.

  • Microsoft Office Live Workspace is a free, secure place to store and share documents with your class.

    You can easily upload large files for everyone to access. It is simple and centralized, so you can avoid the complication and confusion of emailing to everyone individually.

Manage the data

The grade book is set up so that you can convert it easily to an Office Excel 2007 table. The total possible values are placed in the first row so that you can create a table without including these values.

  1. Click cell A2 (the first cell in the header row), and drag the mouse to select all of the cells that you want in the table. Include all student rows and header items.
  2. On the Home tab, click Format as Table, and select a table style.
  3. In the Format As Table dialog box, select the My table has headers check box and click OK.

With your grade book converted to a table, you can more easily work with the information as a database. Click the arrows in the header row to quickly sort and filter the table. For example, click the arrow in the student name column, and click Sort A to Z to sort the table alphabetically by student name.

For more ideas about how to use the grade book as a database, see View your grade book in a PivotTable with Microsoft Office Excel 2007.