Excel Formula for Grade Scale: A Complete Guide

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for a grade scale uses IFS to map a numeric score to a letter grade. For example, with a score in cell A2, =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F") returns a letter grade based on the standard 90-80-70-60 scale. Below, we explain this formula in detail, show alternatives like VLOOKUP and Nested IF, and cover how to customize the scale for your own grading system.

Understanding the IFS Function

The IFS function (available in Excel 2019 and later, including Microsoft 365) evaluates conditions in order and returns the value for the first condition that is TRUE. It is the cleanest way to assign letter grades because you list each cutoff once.

For the standard scale, with a numeric score in A2, the formula is:

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

Here is how it works:

  • If A2 is 90 or above, it returns “A”.
  • If not, it checks if A2 is 80 or above (but less than 90) and returns “B”.
  • It continues down the list.
  • The final TRUE acts as a catch-all for any score below 60, returning “F”.

For example, if A2 contains 85, the formula returns “B”. If A2 contains 59, it returns “F”.

Using VLOOKUP with a Grade Table

Another common method is to create a separate lookup table that defines the grade boundaries. This is easier to modify later because you only edit the table, not the formula.

Assume you have a table in cells D1:E5 like this:

  • D1: 0, E1: “F”
  • D2: 60, E2: “D”
  • D3: 70, E3: “C”
  • D4: 80, E4: “B”
  • D5: 90, E5: “A”

Then, with a score in A2, use:

=VLOOKUP(A2,$D$1:$E$5,2,TRUE)

The TRUE argument means approximate match: VLOOKUP finds the largest value in column D that is less than or equal to the score, and returns the corresponding grade from column E. So a score of 85 matches the row with 80 and returns “B”. This method is robust and easy to update.

Nested IF as an Alternative

Before IFS was introduced, nested IF functions were the standard. They still work in all Excel versions. The formula for the same scale is:

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

This is harder to read and maintain, especially if you have many grade levels. However, it is the only option in Excel 2016 and earlier. If you are using an older version, use this nested approach.

Customizing the Grade Scale

Not every school uses the 90-80-70-60 scale. You might have a different set of cutoffs, such as 93-85-77-70, or you might use plus/minus grades. To customize, simply adjust the thresholds in the IFS formula or in the lookup table.

For a plus/minus scale, you need more conditions. For example, with a score in A2, you could use:

=IFS(A2>=97,"A+",A2>=93,"A",A2>=90,"A-",A2>=87,"B+",A2>=83,"B",A2>=80,"B-",A2>=77,"C+",A2>=73,"C",A2>=70,"C-",A2>=67,"D+",A2>=63,"D",A2>=60,"D-",TRUE,"F")

This gives a 13-point scale. You can add or remove conditions as needed.

Common Errors and Troubleshooting

When using these formulas, watch out for these common issues:

  • Text scores: If your score is stored as text (e.g., “85”), the formula may not work. Convert to numbers by selecting the cells and using the error indicator, or multiply by 1: =A2*1.
  • Out-of-range scores: If a score is above 100 or below 0, the formula still assigns a grade. You can add a check: =IF(OR(A2>100,A2<0),"Invalid",IFS(...)).
  • VLOOKUP table order: The first column of the lookup table must be sorted ascending for approximate match to work correctly.
  • Absolute references: When copying the VLOOKUP formula down, use $D$1:$E$5 to keep the table range fixed.

Comparing Methods: IFS vs VLOOKUP vs Nested IF

Each method has its strengths:

  • IFS: Best for a small, fixed set of grades that you rarely change. It is easy to read and write.
  • VLOOKUP: Best when you want to maintain the scale separately, perhaps on another sheet, or when you have many grades. It is also easier to update without editing formulas.
  • Nested IF: Only necessary for older Excel versions. It is more error-prone and harder to debug.

For most modern users, IFS is the recommended choice. If you need to share the workbook with someone using Excel 2016 or earlier, use nested IF or VLOOKUP.

Frequently Asked Questions

Can I use the IFS formula in Excel for Mac or Excel for the web?

Yes, the IFS function is available in Excel for Microsoft 365 on Mac, Windows, and Excel for the web. The formula syntax is identical. In older versions of Excel for Mac (before 2019), you would need to use nested IF.

How do I assign letter grades to a column of scores?

Enter the formula in the first cell next to the first score (e.g., B2), then double-click the fill handle (the small square at the bottom-right of the cell) to copy it down the column. Excel automatically adjusts the row references for each row.

What if my grades are based on percentages but I have a total score?

First convert the raw score to a percentage by dividing by the total points. For example, if the score is in A2 and the total is 50, use =A2/50 in a helper column, then apply the grade formula to that helper cell. Alternatively, incorporate the division directly: =IFS(A2/50>=0.9,"A",...).

Can I use conditional formatting to color-code grades?

Yes. Select the cells with the letter grades, go to Home > Conditional Formatting > Highlight Cells Rules, and choose options like “Equal To” to set colors for each grade. For example, set “A” to green, “D” to yellow, and “F” to red. You can also use a formula-based rule for more control.

Is there a way to calculate GPA from letter grades?

You can use a lookup table to convert letter grades to grade points. For instance, create a table with letters in column A and points in column B (A=4, B=3, etc.), then use =VLOOKUP(B2,table,2,FALSE) to get points for each grade. Then average the points with =AVERAGE(range).

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.