Cellwise

Excel guide · 3 min read

Excel formula for letter grades

For a standard 90/80/70/60 scale use =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F") in Excel 2019 or later. For easier changes, put the cut-offs in a table sorted lowest to highest and use =VLOOKUP(B2,$D$2:$E$6,2,TRUE), which finds the highest cut-off at or below the score.

Sample data

Every formula on this page was calculated live against this sheet.

ABCDE
1StudentScoreMinGrade
2Alicia930F
3Ben7860D
4Chloe86.570C
5Dmitri5980B
6Esme7090A
7Farid64

Method 1: nested IF (works in every version)

Check the highest grade first and work down. Each IF handles one grade and passes everything else to the next one. The last value, "F", is what is left.

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

Result: A

Alicia scored 93, which passes the first test, so she gets an A.

Method 2: IFS (Excel 2019, 2021 and 365)

IFS lists each condition and grade in pairs with no nesting. Keep them in descending order, and end with TRUE,"F" so scores below 60 still get a grade instead of #N/A.

fx=IFS(B3>=90,"A",B3>=80,"B",B3>=70,"C",B3>=60,"D",TRUE,"F")

Result: C

Ben scored 78. He fails the 90 and 80 tests and passes 70, so IFS returns C.

Method 3: VLOOKUP with a grade table

Put the minimum score for each grade in D2:D6, sorted smallest to largest, with the letters beside them. VLOOKUP with TRUE (approximate match) finds the largest minimum that is not above the score.

Lock the table with $ so it stays put when you copy the formula down. To change the scale, edit the table instead of every formula.

fx=VLOOKUP(B4,$D$2:$E$6,2,TRUE)

Result: B

Chloe's 86.5 is above 80 but below 90, so VLOOKUP lands on the 80 row and returns B.

Method 4: XLOOKUP with a grade table (Excel 365 and 2021)

XLOOKUP with match mode -1 means "exact match, or the next smaller item", which is exactly how grade bands work. It does not need a column number and reads the result column directly.

fx=XLOOKUP(B5,$D$2:$D$6,$E$2:$E$6,,-1)

Result: F

Dmitri scored 59. The next smaller cut-off is 0, so the grade is F.

Plus and minus grades

With 12 grades, a formula becomes unreadable. Extend the table instead: rows for 0 F, 60 D-, 63 D, 67 D+, 70 C-, 73 C, 77 C+, and so on up to 93 A. The VLOOKUP or XLOOKUP formula does not change at all.

Then count how many students got each grade with COUNTIF on the grade column, or count by score directly.

fx=COUNTIF(B2:B7,">=90")

Result: 1

Only Alicia scored 90 or more, so one student earned an A.

Which method to pick

Nested IF works everywhere but is hard to edit. IFS is clearer for a fixed five-grade scale in Excel 2019 or later. A lookup table with VLOOKUP or XLOOKUP is best when the scale might change, when you have plus and minus grades, or when several classes share the same scale, because you edit the cut-offs in one place.

Common mistakes

Using > instead of >=

B2>90 gives a score of exactly 90 a B. Use >= for "at least" cut-offs.

Unsorted grade table

VLOOKUP with TRUE needs the minimums sorted smallest to largest, or it returns wrong grades. XLOOKUP with -1 does not need sorting.

Decimals just below a cut-off

A shown 90 might really be 89.6 and get a B. Decide your rounding rule and use ROUND(B2,0) inside the formula if 89.5 should count as 90.

FAQ

What is the easiest letter grade formula in Excel?

IFS if you have Excel 2019 or later, because it reads like the grading policy. For a scale that changes often, a lookup table with VLOOKUP or XLOOKUP is easier to maintain.

Why does my grade formula return #N/A?

IFS returns #N/A when no condition is true, for example a score below your lowest cut-off. Add TRUE,"F" at the end. For VLOOKUP, make sure the table starts at 0.

How do I calculate the average grade first?

Average the scores, not the letters: =AVERAGE(B2:B7), then feed that result into your grade formula.

Functions used

Keep learning

Updated 2026-10-31