Spreadsheets make calculating students’ grades quick and easy. They’re efficient and can be easily updated if anything changes, saving you from redoing all the math. Plus, they help keep everything organized and accurate, reducing mistakes. With a digital backup, your data is safe and easy to share with the school office, making it simple to include grades on report cards. Overall, spreadsheets make grading a breeze!
If you’ve never done this before, here’s a step-by-step guide to creating your custom grading spreadsheet.
1. Set Up the Spreadsheet Header
Open Excel and in Row 1, create the following headers:
A1: Student Name
B1: Test 1
C1: Test 2
D1: Test 3
E1: Test 4
F1: Final Score
G1: Grade
You can adjust the number of assignments/tests to suit your needs.
2. Enter Students’ Scores
In rows 2 and onward, enter data for each student. Example:
A2: Aviva
B2: 85
C2: 90
D2: 88
E2: 92
Do this for each student in rows below.
Formulas to Enter
Average Score (In cell F2)
=AVERAGE(B2:E2)
Copy this formula down column F for all students.
Letter Grade (in cell G2)
=IF(F2>=90,”A”,IF(F2>=80,”B”,IF(F2>=70,”C”,IF(F2>=60,”D”,”F”))))
Copy this down column G for all students.
TIP: to quickly copy formulas down a column: click the cell, then drag the small square at the bottom-right corner downward.
Let’s take this a step further!
Do you want your final grade to reflect classwork, assignments, behavior, and more? For example, if you’ve told your students that assignments will make up 30% of their final grade and tests 70%, you can adjust the spreadsheet like this:
1. Set Up the Spreadsheet Header
In Row 1, create the following headers:
A1: Student Name
B1: Assignment 1
C1: Assignment 2
D1: Test 1
E1: Test 2
F1: Test 3
G1: Final Score
H1: Grade
Again, you can adjust the number of assignments/tests to suit your needs.
2. Enter Students’ Scores
Enter the data for each student. Example:
A2: Aviva
B2: 85
C2: 90
D2: 88
E2: 92
F2: 94
Do this for each student in rows below.
Formulas to Enter
Average Score (In cell F2)
=AVERAGE(B2:C2)*.3 + AVERAGE(D2:F2)*.7
Copy this formula down column F for all students.
Letter Grade (in cell G2)
=IF(F2>=90,”A”,IF(F2>=80,”B”,IF(F2>=70,”C”,IF(F2>=60,”D”,”F”))))
Copy this down column G for all students.