Excel Grade Formula: Find Letter Grades From Scores

Formulas & functions · Updated November 20, 2026

To turn a score into a letter grade in Excel, create a table of grade cutoffs and use a lookup formula. With a numeric score in B2 and the cutoff-and-grade table in E2:F6, enter =VLOOKUP(B2,$E$2:$F$6,2,TRUE) in C2. For example, a score of 84 returns B when the cutoffs are 0, 60, 70, 80, and 90. Set up the table first, and check that its cutoffs match your grading policy.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows. The same formulas work in current Excel for Mac and Excel for the web, although ribbon placement can vary slightly.
  • A column of numeric scores, such as 0–100 points, and an empty column for the letter grades.
  • Your actual grading scale. The A–F cutoffs below are an example, not a universal grading standard.

Step-by-Step

  1. Put the scores in a column. Enter a heading such as Score in A1 and the first student’s score in A2. Continue down column A, one score per row. This example uses whole-number scores from 0 through 100; a score of 84 means 84 points, not the decimal value 0.84.
  2. Build a cutoff table. In E1, type Minimum score; in F1, type Grade. Then enter the following values beneath the headings. Each number in the first column is the lowest score that earns the grade beside it.
Minimum score Grade
0 F
60 D
70 C
80 B
90 A

That example assigns 60–69 to D, 70–79 to C, 80–89 to B, and 90–100 to A. If your school uses plus/minus grades, different boundaries, or a different lowest passing score, use those rules in the table instead. The first cutoff should cover the lowest possible score you expect to grade.

  1. Sort the cutoffs from low to high. Keep the first-column values in ascending order, with no duplicate cutoff values. Approximate-match lookup depends on this ordering: Excel finds the largest cutoff that is less than or equal to the score. If the cutoffs are out of order, the result can be wrong even though the formula looks correct.
  2. Enter the lookup formula beside the first score. With the score in A2 and the cutoff-and-grade table in E2:F6, enter this in B2: =VLOOKUP(A2,$E$2:$F$6,2,TRUE). The 2 tells Excel to return the second column of the table, which contains the grade. TRUE requests an approximate match, which is the right behavior for minimum-score thresholds.
  3. Check the first result against the scale. With the example table, a score of 84 returns B because 80 is the highest cutoff that does not exceed 84. A score of 90 returns A; a score of 59 returns F. Check a score exactly on each boundary as well, such as 60, 70, and 80, to confirm that the grades match your policy.
  4. Copy the formula down. Select B2, point to the small square at the lower-right corner of the selection, and drag it down alongside the remaining scores. You can also select the formula cell and the cells below it, then press Ctrl+D on Windows. In Excel for Mac, use Command+D. The dollar signs in $E$2:$F$6 keep the cutoff table fixed while the score reference changes from A2 to A3, A4, and so on.
  5. Give the result column a clear heading and review it. Label B1 Grade, then scan the results and compare several scores with your cutoff table. Save the workbook after confirming that the formula has filled through every row containing a score.

If you do not want a separate lookup table, a nested IF formula can return a grade directly. With a numeric score from 0 to 100 in A2, enter =IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F")))) in B2. For 84, the result is B. This method is suitable for a short, fixed scale, but you must edit the formula to change a cutoff or add another grade. The table method is usually easier to maintain and review.

Common Mistakes

  • Using TRUE without sorting the cutoffs. Approximate match is not an exact search. Sort the first table column from smallest to largest; otherwise, Excel may return an unexpected grade.
  • Leaving out the lowest cutoff. If the table starts at 60, a score below 60 has no valid lower boundary and can produce #N/A. Include a starting value that covers the lowest score you plan to grade, such as 0 for a 0–100 scale.
  • Choosing the wrong match type. In the example, TRUE is intentional because a score usually falls between grade boundaries. FALSE requires an exact match to a cutoff, so a score such as 84 would not match the table and would typically return #N/A.
  • Using percent-formatted values as if they were points. A cell displaying 84% stores 0.84. That will not match a table of cutoffs such as 60, 70, and 80. Decide whether your scores are points or decimal percentages, then make the score cells and cutoffs use the same scale.
  • Allowing text to stand in for a number. A score pasted from another system may be stored as text. Numbers aligned to the left, a green error indicator, or an unexpected result can be clues. Convert the data to numbers before relying on the grade formula.
  • Forgetting that the example scale is only an example. A course may use 93 as the A cutoff, include plus/minus grades, or treat a borderline score differently. Enter the official boundaries before grading a real class.

Tips

  • Keep the lookup table visible and labeled. Clear headings make it easier for another person to verify what each threshold means. You can place the table on a separate worksheet, but update the formula’s range to point to that sheet if you do.
  • Use absolute references for the table. The dollar signs in $E$2:$F$6 stop that range moving when you copy the formula. If you omit them, the table reference may shift down each row and produce errors or incorrect grades.
  • Decide how to handle blank scores. If blank cells should stay blank rather than receive a grade, use a blank-check formula. With scores in A2 and the cutoff table in E2:F6, enter =IF(A2="","",VLOOKUP(A2,$E$2:$F$6,2,TRUE)) in B2. The result remains empty when A2 is empty; a score of 84 returns B. Copy this version down instead of the simpler lookup formula.
  • Agree on rounding before assigning grades. If a policy says to grade the recorded score without rounding, do not round just to make a score cross a boundary. If the policy does call for rounding, apply that rule consistently and keep the displayed score and grade aligned.
  • Prevent out-of-range entries where practical. On Windows, select the score cells and choose Data > Data Validation. Set Allow to Whole number, set the minimum and maximum to match your scale, and confirm the rule. On Mac or the web, the Data Validation command may be arranged differently, but the same idea applies. Validation helps prevent accidental entries such as 840 on a 0–100 scale.

Troubleshooting

The formula returns #N/A. Check that the score is a number and that it is at least as large as the first cutoff. Confirm that the lookup range includes both columns and that the first column contains sorted, numeric cutoffs. If using the example scale, a score below 0 is outside the table; decide how your policy should handle it rather than assigning a grade by accident.

The grade is one level too high or too low. Check the minimum-score values and the exact-boundary policy. A cutoff of 80 means that 80 and higher can receive B, unless a higher cutoff also applies. If your institution defines the intervals differently, change the table to match its written rules. Also verify that the formula returns column 2 and that the grade labels are in the second table column.

The formula appears as text instead of calculating. Select the formula cell and check that the cell is not formatted as Text. Change the format to General, then re-enter the formula by pressing F2 and Enter. Make sure it begins with an equals sign and has no apostrophe before it.

Excel shows the formula in the cell or does not update it. Check whether Show Formulas is enabled; press Ctrl+` on Windows to toggle it. On Mac, use Control+` where that shortcut is available, or use the Formulas tab’s Show Formulas control. Also check Formulas > Calculation Options and choose Automatic if the workbook is set to manual calculation.

The formula returns the wrong grade only after copying. Inspect the formula in a lower row. Its score reference should change to that row, while the table should still say $E$2:$F$6. If the table coordinates changed, restore the dollar signs and fill the formula down again.

Frequently Asked Questions

Can Excel calculate a letter grade without a lookup table?
Yes. A nested IF formula can test each cutoff in descending order and return the matching letter. It is convenient for a short scale that rarely changes. For a longer scale or one that administrators may update, a separate table is easier to audit.

How do I calculate an average grade as well as a letter grade?
Use AVERAGE for the numeric result, then convert that result using the same cutoff table. With five scores in A2:A6, enter =AVERAGE(A2:A6) in A7. For the illustrative scores 92, 84, 76, 88, and 90, the average is 86. With that average in A7 and the cutoff table in E2:F6, the lookup formula in B7 is =VLOOKUP(A7,$E$2:$F$6,2,TRUE), which returns B on the example scale.

What if my grades include A-, B+, or other plus/minus marks?
Add a separate row for each grade boundary, using the exact minimum score for that grade and its label. Sort all the minimum scores from low to high. For example, the labels and thresholds should follow your school’s scale rather than a generic plus/minus convention.

Can I use the formula if scores are percentages?
Yes, provided the scores and thresholds use the same representation. If 84% is stored as 0.84, use decimal cutoffs such as 0, 0.6, 0.7, 0.8, and 0.9 in the lookup table. Do not mix decimal percentages with whole-number point cutoffs.

Can I use XLOOKUP instead?
Yes. XLOOKUP can perform an approximate match against sorted thresholds, but it is not necessary for this task. The VLOOKUP formula above works well for a two-column cutoff table and is compatible with older Excel versions as well as Microsoft 365.

Will the formula update if I change a cutoff?
Yes. When you edit a value in the referenced cutoff table, Excel recalculates grades based on the new boundary. Review the table for ascending order after editing, and check a few scores around the changed cutoff before using the revised results.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.