Excel for Education: Gradebooks, Attendance, and Student Tracking

Gradebook Structure

Essential gradebook columns:

• Student name (or ID for privacy)
• Assignment columns with point values in header
• Category columns (Homework, Tests, Projects, Participation)
• Running totals and percentages
• Letter grade (calculated)

Weighted Grade Calculations

With category weights (e.g., Tests 40%, Homework 30%, Projects 20%, Participation 10%):

=(TestAvg*0.4)+(HomeworkAvg*0.3)+(ProjectAvg*0.2)+(ParticipationAvg*0.1)

Or use SUMPRODUCT with a weights table for easier adjustments.

Letter Grade Conversion

Convert percentages to letter grades:

=IF(Score>=90,“A”,IF(Score>=80,“B”,IF(Score>=70,“C”,IF(Score>=60,“D”,“F”))))

Or use XLOOKUP with a grade scale table for cleaner formulas:

=XLOOKUP(Score, GradeThresholds, LetterGrades, , -1)

Attendance Tracking

Simple attendance grid:

• Students in rows, dates in columns
• Codes: P (Present), A (Absent), T (Tardy), E (Excused)
• Absent count: =COUNTIF(StudentRow, “A”)
• Attendance rate: =COUNTIF(StudentRow, “P”)/COUNTA(StudentRow)

Conditional formatting can highlight students with attendance concerns.

Missing Assignment Tracking

Identify students with missing work:

=COUNTBLANK(StudentAssignmentRange)

List missing assignments:

=TEXTJOIN(“, ”, TRUE, IF(ISBLANK(Assignments), AssignmentNames, “”))

(Entered as array formula with Ctrl+Shift+Enter in older Excel)

Class Statistics

Summary statistics for assignments:

• Class average: =AVERAGE(ScoreRange)
• Median: =MEDIAN(ScoreRange)
• High/Low: =MAX(ScoreRange), =MIN(ScoreRange)
• Standard deviation: =STDEV(ScoreRange)
• Pass rate: =COUNTIF(ScoreRange, “>=70”)/COUNT(ScoreRange)

Official Resources

• AVERAGEIF function — conditional averages
• COUNTBLANK function — find missing assignments