|
Links for K-12 Teachers | Assessment Assistance | On-Line Practice Modules | Daily Dose of the Web
Using Excel as a Gradebook
|
Grade book basics
 |
This is not shown so that you can copy my information into a
spreadsheet. It is Only shown as an example.
As you experiment with using an Excel workbook as a Grade book, you
should keep it simple.
Start with 4-5 names and at most two types of grades.
Legend
 |
Legend - List each item for which you gave a grade. Be specific, you have plenty of room and later you may wish that you had recorded more information about what assignment the grade was given for. |
Grade Policy
 |
Grade Policy - You should clearly spell out how you will use grades to determine a student's final grade. An Excel worksheet provides enough room that this information could be included on each Grade book page. |
Function
 |
|
Equation for determining grades (Function) - The equation above applies my stated grade policy. If you can state your grade policy as an equation, you can write an Excel function to do the calculation. The function above does the following
|
|
Filling the function into other cells - In the sample worksheet above the function has been entered into cell M7. Click on the bottom right corner of the cell and drag down to the last cell where the function is needed. In the example above that would be cell M17. |
Averages can be displayed to whatever precision you wish to use. I used one decimal place, although you may wish to use zero decimal places. Zero decimal places would keep the grades in a format like they are reported to students. An advantage of using zero decimal places would be to avoid confusion regarding rounding grades. To illustrate this consider the following grade:
- A grade of 75.49 would round to 75 with zero decimal places. However, at one decimal place that grade rounds to 75.5 and students would have the expectation that the grade would round to 76. Using zero decimal places will allow Excel to round without confusion to some students.
How to format
1. Highlight the column to be formatted by clicking on the letter at the top of the column.2. From the Format menu choose Cells
3. From the Format Cells window choose Number and then select the number of decimal places you want to use.
Using a Lookup table
Now we will ask Excel to look at the numerical average in column M and compare it to a list which defines the grading scale, for the purpose of assigning a letter grade to the average. Room was left at the top of the Grade book for this purpose.
 |
The information to the left, defining the grade scale must be entered in ascending order from top to bottom. The number entered to the left of a letter must be the lowest number grade that would equal that letter grade.
|
Next we will write an equation which will look at a student's numerical average, look at a list of grades, and assign a letter grade to the student. This is done with a functioned named VLOOKUP. The equation must specify three elements:
- The location of the numerical grade to be compared (M7 in the example)
- The name of the lookup table (grades)
- The location of the letter grade in the lookup table (2) [because the letter grade is in column 2 in the lookup table]
After the equation is entered in N7, click and drag to fill the equation down into the remainder of the Grade book
A List of Online Resources for Excel Grade books
Visitors since November 2000 |
 Memphis, TN |
