Google Sheets Gradebook Formulas

Letter grades, attendance rows, and rubric progress for gradebooks.

Letter grade from percent

Put score percent 0-100 in A1. Converts to letter grade A through F.

=IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C",IF(A1>=60,"D","F"))))

Rubric stars out of five

Put rubric score 0-5 in A1. Stars rate assignment quality on your rubric.

=REPT("⭐",MIN(A1,5))&REPT("☆",MAX(0,5-A1))&" rubric"

⭐⭐⭐⭐☆ rubric

REPT MIN MAX

Late work warning status

Put days late in A1. Green, yellow, or red late work alert.

=IF(A1=0,"🟢 On time",IF(A1<=3,"🟡 Late","🔴 Very late"))

🟡 Late

Attendance present absent tally

Put present days in A1 and absent days in B1. Checks and X marks tally attendance.

=REPT("✅",MIN(A1,5))&REPT("❌",MIN(B1,5))&" "&A1&"P-"&B1&"A"

✅✅✅✅❌ 4P-1A

REPT MIN

Homework turn-in progress

Put assignments turned in in A1 and assigned in B1. Pencil blocks track homework completion.

=REPT("📝",MIN(A1,B1))&REPT("○",MAX(0,B1-A1))&" "&A1&" turned in"

📝📝📝📝📝📝📝○○○ 7 turned in

REPT MIN MAX

Parent contact flag

Put 1 if parent contacted or 0 if not. Quick parent outreach status.

=IF(A1=1,"📞 Parent contacted","📋 Contact needed")

📋 Contact needed

Parent note sent status

Put 1 if note sent home or 0 if pending. Tracks parent communication.

=IF(A1=1,"📝 Note sent to parent","📋 No note yet")

📝 Note sent to parent

Seating chart picker

Put row 1-5 in A1. Picks a desk row for seating chart shuffle.

=CHOOSE(A1,"🪑 Row 1","🪑 Row 2","🪑 Row 3","🪑 Row 4","🪑 Row 5")

🪑 Row 3

CHOOSE

Participation points bar

Put participation points 0-5 in A1. Raised hands track class engagement.

=REPT("🙋",MIN(A1,5))&REPT("○",MAX(0,5-A1))&" pts"

🙋🙋🙋○○ pts

REPT MIN MAX

Semester grade trajectory

Put trend 1-3 in A1. Rising, steady, or falling grade arrow.

=CHOOSE(A1,"📈 Rising","➡️ Steady","📉 Falling")

📈 Rising

CHOOSE

Weighted average grade display

Put test score in A1, quiz in B1, and homework in C1. Shows weighted class average.

=ROUND(A1*0.4+B1*0.3+C1*0.3,0)&"% weighted avg"

82% weighted avg

ROUND

Missing work counter

Put missing assignments in A1. Warning icons stack for each gap in the gradebook.

=REPT("⚠️",MIN(A1,5))&" "&A1&" missing"

⚠️⚠️⚠️ 3 missing

REPT MIN

Exam countdown days

Put days until exam in A1. Calendar icons count down to test day.

=REPT("📅",MIN(A1,5))&" "&A1&" days left"

📅📅📅📅 4 days left

REPT MIN

Student mood check-in

Put mood 1-5 in A1. Happy, good, OK, struggling, or tired face.

=CHOOSE(A1,"😊 Great","🙂 Good","😐 OK","😟 Struggling","😴 Tired")

🙂 Good

CHOOSE

Class period timer

Put period 1-6 in A1. Bell icons mark which class block is active.

=CHOOSE(A1,"🔔 Period 1","🔔 Period 2","🔔 Period 3","🔔 Period 4","🔔 Period 5","🔔 Period 6")

🔔 Period 3

CHOOSE

Reading level progress

Put books read in A1 and level goal in B1. Book icons track reading growth.

=REPT("📖",MIN(A1,B1))&REPT("○",MAX(0,B1-A1))&" level "&A1

📖📖📖📖📖○○○○○ level 5

REPT MIN MAX

Behavior chart traffic light

Put behavior level 1-3 in A1. Green, yellow, or red classroom behavior flag.

=CHOOSE(A1,"🟢 Great day","🟡 Warning","🔴 Referral")

🟡 Warning

CHOOSE

Quiz score percent bar

Put quiz percent in A1. Ten blocks scale to quiz score.

=REPT("█",MIN(10,MAX(0,ROUND(A1/10,0))))&REPT("░",MAX(0,10-MIN(10,MAX(0,ROUND(A1/10,0)))))&" "&A1&"%"

███████░░░ 72%

REPT ROUND MIN MAX

GPA milestone bar

Put GPA 0-4 in A1. Graduation caps fill as GPA climbs.

=REPT("🎓",MIN(ROUND(A1*2.5,0),10))&REPT("○",MAX(0,10-MIN(ROUND(A1*2.5,0),10)))&" GPA "&TEXT(A1,"0.0")

🎓🎓🎓🎓🎓🎓🎓○○○ GPA 3.2

REPT ROUND MIN MAX TEXT

Extra credit stars

Put extra credit points 0-5 in A1. Sparkle stars reward bonus work.

=REPT("✨",MIN(A1,5))&" extra credit"

✨✨✨ extra credit

REPT MIN

Field trip permission status

Put 1 if slip signed or 0 if missing. Tracks field trip permission forms.

=IF(A1=1,"✅ Permission slip signed","📄 Slip missing")

📄 Slip missing

Report card celebration

Put final percent in A1. Honor roll burst or grade display.

=IF(A1>=90,"🎉 Honor roll!","📊 Grade: "&A1&"%")

🎉 Honor roll!

Detention warning status

Put infraction count in A1. Clean record, warning, or detention flag.

=IF(A1=0,"✅ No issues",IF(A1=1,"⚠️ Warning","🔴 Detention"))

⚠️ Warning

Study group picker

Put group 1-5 in A1. Randomly assigns a study group label.

=CHOOSE(A1,"📚 Group A","📚 Group B","📚 Group C","📚 Group D","📚 Group E")

📚 Group B

CHOOSE

Classroom participation streak

Put participation days in A1 and streak goal in B1. Hand raises track engagement streak.

=REPT("🙋",MIN(A1,B1))&REPT("○",MAX(0,B1-A1))&" "&A1&" days"

🙋🙋🙋🙋🙋○○ 5 days

REPT MIN MAX

Related formula guides

Go deeper with Better Sheets

Members get full courses on CHAR, REPT, and dashboard design, plus hundreds of Google Sheets tutorials. Copy-paste formulas here stay free.

See membership options