Free Excel template

Free Excel grade book template

For teachers and tutors who weight assignments differently, like quizzes at 10% and the final at 35%. Enter scores out of 100 and the weights; the template calculates each student's weighted percentage, assigns a letter grade from a cutoff table you can edit, and summarizes the class.

cellwise-grade-book-template.xlsxExcel workbook · 4.5 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup

Download free template

Preview and try it

This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.

	A	B	C	D	E	F	G	H
1	Student	Quiz 1	Quiz 2	Midterm	Project	Final exam	Weighted %	Letter
2	Weight	0.1	0.1	0.25	0.2	0.35	1
3	Ava Thompson	92	88	85	94	90	89.6	B
4	Ben Carter	78	82	74	80	71	75.4	C
5	Chloe Nguyen	95	97	91	98	93	94.1	A
6	Daniel Ortiz	65	70	58	72	61	63.8	D
7	Emma Wilson	88	85	90	86	84	86.4	B
8	Farah Ali	72	68	77	85	79	77.9	C
9	Gabe Miller	55	62	49	60	52	54.2	F
10	Hana Sato	84	90	88	79	89	86.4	B
11	Isaac Brown	90	76	82	88	80	82.7	B
12	Julia Rossi	81	79	69	91	74	77.4	C
13	Class average	80	79.7	76.3	83.3	77.3	78.8
14	Highest	95	97	91	98	93	94.1
15	Lowest	55	62	49	60	52	54.2
16
17	Min %	Letter	Students
18	0	F	1
19	60	D	1
20	70	C	3

How the formulas work

Weighted percentage with SUMPRODUCT

G3=ROUND(SUMPRODUCT(B3:F3,$B$2:$F$2)/$G$2,1)

SUMPRODUCT multiplies each score by its weight and adds the results. Dividing by the total of the weights (G2) means the grade still comes out right if your weights do not add up to exactly 100%. The weights row is locked with dollar signs so the formula copies down for every student.

Letter grade from an editable cutoff table

H3=VLOOKUP(G3,$A$18:$B$22,2,TRUE)

With TRUE as the last argument, VLOOKUP finds the largest cutoff that is not above the score: 84.4 lands on 80 and returns B. The cutoffs must be sorted smallest to largest. Change 90 to 93 in the table and every letter updates.

Class average per assignment

B13=ROUND(AVERAGE(B3:B12),1)

AVERAGE skips empty cells, so a missing score does not drag the class average down. Highest and Lowest use MAX and MIN on the same range. A low class average on one assignment can point to a question that needs rewording.

Count students per letter grade

C18=COUNTIF($H$3:$H$12,B18)

COUNTIF counts how many letters in column H match the letter in B18. Copied down, it gives a full grade distribution for the class.

Make it yours

A blank score counts as 0 in SUMPRODUCT, which lowers the weighted grade. Enter 0 for missing work on purpose, and for an excused assignment move its weight to the other columns for that student.
Add an assignment by inserting a column before the Weighted % column, adding its weight in row 2, and widening the SUMPRODUCT ranges to include it.
For plus and minus grades, add rows to the cutoff table (for example 87 for B+ and 90 for A-), keep it sorted, and extend the VLOOKUP range.
Highlight failing grades: select G3:G12, then Home > Conditional Formatting > Highlight Cells Rules > Less Than, and type 60.

FAQ

How do I calculate a weighted grade in Excel?

Multiply each score by its weight and add them up, which is what =SUMPRODUCT(scores,weights) does in one step. Divide by SUM(weights) if the weights do not add up to 100%.

How do I convert a percentage to a letter grade in Excel?

Use a small cutoff table sorted from lowest to highest and VLOOKUP with TRUE (approximate match), or nested IF / IFS formulas. The table is easier to change later.

Should scores be out of 100?

This template expects percentages out of 100. If a quiz is out of 20, enter the score as a percentage (16 out of 20 = 80) or add a column that divides the raw score by 20 and multiplies by 100.

Functions used

Related guides

More free templates

Updated November 2, 2026