Build a Gradebook in Excel
By ExcelReady TeamUpdated 7 min read
The short answer
To build a gradebook in Excel, list students in column A and one assignment per column. AVERAGE gives each student's mean score, or SUMPRODUCT with a row of weights gives a weighted mark. IFS turns the mark into a letter grade, for example 90 or more is an A. AVERAGE, MIN and MAX under each column summarise the class.
Key takeaways
- One row per student and one column per assignment keeps every formula a simple fill-down.
- AVERAGE ignores blank cells but counts zeros, so decide how a missed assignment is entered.
- =IFS(F2>=90,"A",F2>=80,"B",F2>=70,"C",F2>=60,"D",TRUE,"F") checks the highest band first.
- Weighted marks use SUMPRODUCT with a locked row of weights that adds up to 100%.
- AVERAGE, MIN and MAX under each column show how the class did on each assignment.
On this page+
A gradebook in Excel needs four formulas: AVERAGE for each student's mark, SUMPRODUCT if assignments carry different weights, IFS to turn the mark into a letter grade, and AVERAGE, MIN and MAX to summarise the class. This guide builds a gradebook for five students and four assessments. It suits teachers, tutors and trainers, and it is good practice for the IF and AVERAGE questions in Excel tests.
How to lay out the gradebook#
Put one student per row and one assignment per column. That shape means every formula is written once and filled down or across.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Student | Quiz 1 | Quiz 2 | Midterm | Final | Average | Weighted | Grade |
| 2 | Ana | 92 | 88 | 95 | 90 | 91.25 | 91.50 | A |
| 3 | Ben | 78 | 85 | 72 | 80 | 78.75 | 78.05 | C |
| 4 | Chloe | 65 | 70 | 58 | 62 | 63.75 | 62.45 | D |
| 5 | Dev | 88 | 91 | 84 | 86 | 87.25 | 86.45 | B |
| 6 | Ema | 55 | 60 | 48 | 52 | 53.75 | 52.45 | F |
All scores are out of 100. If an assignment is marked out of something else, such as 20, convert it first: =B2/20*100.
Step 1: Average each student's scores#
AVERAGE adds the numbers in a range and divides by how many there are. In F2:
=AVERAGE(B2:E2)Fill it down to F6 with Ctrl+D (Mac: ⌘D). Ana's average is (92 + 88 + 95 + 90) ÷ 4 = 91.25.
Watch how missing work is entered. AVERAGE skips blank cells, so a student who missed a quiz and has a blank cell is averaged over three scores. If a missed assignment should count as zero, type 0. Pick one rule and tell your students. The AVERAGE function guide has more examples.
Step 2: Weight the assessments#
When a final exam counts for more than a quiz, use a weighted mark. Put the weights in row 8, under the assignments they belong to:
| B | C | D | E | |
|---|---|---|---|---|
| 8 | 15% | 15% | 30% | 40% |
The weights must add up to 100%. In G2:
=SUMPRODUCT($B$8:$E$8,B2:E2)SUMPRODUCT multiplies each score by its weight and adds the results: 92×15% + 88×15% + 95×30% + 90×40% = 91.50. The weights are locked with $ so they stay on row 8 when you fill down; the scores move with each row. For more on this, see how to calculate a weighted average in Excel.
Step 3: Turn marks into letter grades with IFS#
IFS checks a list of tests in order and returns the value for the first one that is TRUE. In H2, grade the weighted mark:
=IFS(G2>=90,"A",G2>=80,"B",G2>=70,"C",G2>=60,"D",TRUE,"F")| Weighted mark | Test that passes first | Grade |
|---|---|---|
| 91.50 | G2>=90 | A |
| 86.45 | G2>=80 | B |
| 78.05 | G2>=70 | C |
| 62.45 | G2>=60 | D |
| 52.45 | TRUE | F |
Order matters. Excel stops at the first TRUE test, so the highest band must come first. If G2>=60 came first, every passing student would get a D. IFS has no built-in "otherwise", so the final TRUE,"F" catches every mark below 60.
IFS needs Excel 2019 or later. In older versions, nest IF functions instead:
=IF(G2>=90,"A",IF(G2>=80,"B",IF(G2>=70,"C",IF(G2>=60,"D","F"))))The guides to nested IF and the IFS function compare the two. The IFS function guide has the full syntax.
Use a grade table when the scale changes#
With many bands, or a scale that changes each term, keep the scale in a small table instead of inside the formula. List each band's lowest mark, smallest first, in M2:N6: 0 F, 60 D, 70 C, 80 B, 90 A. Then:
=VLOOKUP(G2,$M$2:$N$6,2,TRUE)TRUE asks for an approximate match: VLOOKUP finds the largest lowest-mark that is not above the student's mark, so 78.05 lands on the 70 row and returns C. Changing a boundary now means editing one cell. See VLOOKUP approximate match for how the sorting rule works.
Step 4: Summarise the class with AVERAGE, MIN and MAX#
Under each assignment, add three rows that show how the class did. In B10, B11 and B12:
=AVERAGE(B2:B6)
=MIN(B2:B6)
=MAX(B2:B6)Select B10:B12 and fill right to E12 with Ctrl+R (Mac: ⌘R).
| Quiz 1 | Quiz 2 | Midterm | Final | |
|---|---|---|---|---|
| Class average | 75.6 | 78.8 | 71.4 | 74.0 |
| Lowest | 55 | 60 | 48 | 52 |
| Highest | 92 | 91 | 95 | 90 |
The midterm has the lowest average and the widest gap (95 − 48 = 47 points), which may point to a topic worth reviewing. AVERAGE, MIN and MAX in Excel covers these functions in more depth.
Step 5: Count the grades#
COUNTIF counts how many students got each grade. List A, B, C, D and F in J2:J6, then in K2:
=COUNTIF($H$2:$H$6,J2)Fill down. The counts should add up to the number of students; if not, a mark is missing or a formula has an error.
Step 6: Highlight students who need support#
Conditional formatting colors marks below a pass line automatically.
- Select the scores, B2:E6.
- Choose Home > Conditional Formatting > Highlight Cells Rules > Less Than.
- Type
60and choose Light Red Fill with Dark Red Text.
Any score under 60 turns red, and the color updates when a mark changes.
Common gradebook mistakes#
- Grading the wrong column. Make sure the grade formula reads the weighted mark if you use weights, not the simple average.
- Bands in the wrong order, so high scores fall into a low band. Test the boundaries: does exactly 90 give an A, and 89.99 a B?
- Weights that don't add up to 100%. Add
=SUM(B8:E8)next to the weights as a check. - Rounding only with formatting. A mark that shows 90.0 may really be 89.96 and get a B. If the policy is to round, use
ROUNDinside the grade formula.
Practise it#
Do the exercise on this page to grade a student with IFS. Then practise nested IF and IFS with grades and commission rates, and practise AVERAGE, MIN and MAX in a live spreadsheet. When you're ready for another project, try a project timeline or an expense tracker.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Column E already holds each student's average. In F3, use IFS to give Ben a letter grade: A for 90 or more, B for 80 or more, C for 70 or more, D for 60 or more, and F for anything lower (use TRUE for the last test).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Grade | ||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 | |||||||
| 9 |
Tip: Write the tests from the highest band down: E3>=90,"A", then E3>=80,"B", and so on. End with TRUE,"F" to catch everything else.
Practise this in a lesson
Frequently asked questions
How do I calculate a letter grade in Excel?
Use IFS with the bands from highest to lowest: =IFS(F2>=90,"A",F2>=80,"B",F2>=70,"C",F2>=60,"D",TRUE,"F"). In Excel 2016 or older, which have no IFS, use nested IF or VLOOKUP with approximate match on a grade table.
How do I calculate a weighted grade in Excel?
Put the weights in a row under the assignments, such as 15%, 15%, 30% and 40%, so they add up to 100%. Then use =SUMPRODUCT($B$8:$E$8,B2:E2), which multiplies each score by its weight and adds the results.
Does AVERAGE count blank cells as zero?
No. AVERAGE skips empty cells and text, so a blank score doesn't lower the average. Type 0 if a missed assignment should count as zero.
Should I round the average before giving a grade?
Decide on a rule and apply it to everyone. If 89.95 should count as 90, grade on =ROUND(AVERAGE(B2:E2),0) instead of the raw average; otherwise grade on the unrounded value and only format it to show fewer decimals.