How to Calculate Letter Grades in Excel (IF and VLOOKUP)

To calculate letter grades in Excel, use a nested IF formula for a small, fixed grading scale or VLOOKUP with a table of grade cutoffs. A visible lookup table is easier to change when your grading policy changes. Both methods below leave blank score cells blank.

Download the example gradebook to compare all three methods on the Scores sheet: nested IF in column B, a table lookup in C, and a named-array lookup in D.

Quick Answer: Convert a Score to a Letter Grade

With a numeric score from 0 to 100 in A2, enter this formula in B2:

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

For the example scale, 89.5 returns B, 90 returns A, and a blank input produces a blank result. These formulas assume valid numeric scores; the blank check doesn't validate text or out-of-range entries.

Set Your Grading Scale First

Check your syllabus before copying a formula. This tutorial uses an illustrative scale, not a rule every school follows:

The cutoffs include decimal scores. A score of 59.9 is F; 60 is D. A score of 89.5 is B because it hasn't reached 90. None of the three methods rounds a score before assigning its grade. Displaying fewer decimal places also doesn't change the stored score used by the formula.

Use numeric points on a 0–100 scale, with Number or General formatting. Excel stores 90% as 0.9, which these formulas treat as 0.9 points and grade F. If your source stores percentage fractions, convert those values to points by multiplying by 100 before using this setup. Changing the cell's display format alone doesn't perform that conversion.

Method 1: Use a Nested IF Formula

Enter 89.5 in A2, then put the quick-answer formula in B2. You should see B.

The first IF function checks A2="". When the input is blank, it returns "", leaving the result visually empty. Otherwise, the nested checks work from the highest cutoff down:

1. At least 90 returns A.
2. If that check fails, at least 80 returns B.
3. Otherwise, at least 70 returns C, then at least 60 returns D.
4. A valid score below 60 reaches the final F.

For 89.5, the 90 check fails and the 80 check succeeds. Checking the highest cutoff first matters: if you tested for 60 first, a score of 95 would already qualify for D.

Copy B2 down your score list. In the next row, A2 becomes A3, so each result uses the score beside it. Microsoft's IF documentation explains the true/false branches and why text results such as "A" need quotation marks.

This works well when your scale is short and stable. If you expect to change cutoffs, put them in worksheet cells instead.

Method 2: Use VLOOKUP with a Grading Table

Create this table in F1:G6. Row 1 contains the labels; the five lookup rows are F2:G6.

Keep the lower bounds in ascending order, with each letter alongside its cutoff. Enter this formula in C2:

=IF(A2="","",VLOOKUP(A2,$F$2:$G$6,2,TRUE))

For 89.5 in A2, the result is B. The VLOOKUP function uses the largest lower bound that does not exceed the score: 80 in this case. It then returns the letter from that row.

The 2 selects the second column of the lookup range, containing the letters. TRUE selects approximate match; Microsoft's VLOOKUP documentation requires a sorted first column for this mode. Here, approximate match selects a score band—it doesn't round to the nearest cutoff.

Copy C2 down. The dollar signs keep $F$2:$G$6 fixed while the score reference changes to A3, A4, and so on. You can change a cutoff in the table without editing every formula. If you add more grade bands, expand the lookup range too. Our VLOOKUP arguments walkthrough explains the match setting in more detail.

Method 3: Use VLOOKUP with a Named Array Constant

You can store the same five pairs in a named array constant rather than worksheet cells. This keeps the formula short, but the scale is less visible to someone reviewing the gradebook.

In desktop Excel, open Formulas → Define Name. Microsoft's named constant instructions show the Windows dialog; its Excel for Mac naming instructions also use Define Name on the Formulas tab.

Create the name GradeLookup with Workbook scope where the scope option is shown. In Refers to, replace the existing entry with:

={0,"F";60,"D";70,"C";80,"B";90,"A"}

Keep the leading equals sign and type the braces yourself. Save the name, then enter this formula in D2 and press Enter:

=IF(A2="","",VLOOKUP(A2,GradeLookup,2,TRUE))

You should again see B for 89.5. Copy D2 down to apply it to the other scores.

In the US-English array syntax shown here, commas separate columns and semicolons separate rows. Each row therefore pairs one cutoff with one letter. Formula argument separators and array row/column separators can differ by regional settings. If Excel rejects the pasted syntax, use the visible-table method or adapt the separators to your local Excel settings. Microsoft's array-constant guide explains the structure.

The named constant and the visible table are separate copies of the scale. Editing F2:G6 won't change GradeLookup or the cutoffs inside the nested IF formula. The workbook shows all three for comparison; choose one method for your working gradebook so you have one grading scale to maintain.

Restrict Score Entry to Numbers from 0 to 100

Before entering scores, add a validation rule to the input cells. For example, to cover the first 99 score rows:

1. Select A2:A100 and choose Data → Data Validation.
2. Under Allow, select Decimal. Choose between, with a minimum of 0 and a maximum of 100.
3. Leave Ignore blank selected so you can leave ungraded rows empty.
4. Enable the Error Alert, choose the Stop style, and enter a message such as “Enter numeric points from 0 to 100, or leave the cell blank.”
5. Save the rule and try a valid score such as 89.5, then an invalid entry such as 100.1.

Decimal validation allows both whole-number and fractional scores. Microsoft's data validation instructions describe these restrictions and the Stop alert.

Check existing and imported values too. Validation doesn't clean up old entries, and copying or pasting data can bypass its entry checks or overwrite the rule. Text such as absent, negative scores, and scores above 100 aren't valid inputs for this example, even if a formula displays a letter. The formulas check for blanks; they don't independently enforce the numeric range.

A percentage fraction such as 0.9 is within 0–100, so validation accepts it. You still need to check whether your source means 0.9 points or 90%.

Check the Results Before Using the Gradebook

Use these scores to confirm that your formulas match the example policy:

If a result differs, check these details:

• Wrong grade from VLOOKUP: keep the numeric lower bounds sorted smallest to largest and use TRUE for score bands. Sorting only the cutoffs without their letters breaks the scale.
• 90% returns F: check the stored value. The formulas expect 90 points, not the fraction 0.9.
• An empty row returns F: use the complete formula, including the outer blank check. A cell containing spaces isn't blank.
• A score returns an error: check for text, invalid values, a missing cutoff, or a misspelled GradeLookup name before changing the formula.
• The three methods disagree after a policy change: update each separate scale if you keep all three for comparison. In an everyday gradebook, maintaining the visible table alone is usually easier.

Before assigning real grades, confirm the cutoffs and any rounding policy against your syllabus. The formula can apply those rules consistently only when the inputs and scale match them.