The fastest way to assign a letter grade to a numeric score in Excel is a nested IF formula, for example =IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F")))). For Excel 2019 and later, the IFS function is shorter and easier to read: =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F").
Why Use a Formula Instead of Typing Grades?
Manually typing grades is slow and error-prone, especially with hundreds of students or test scores. A formula recalculates automatically if a score changes, and it enforces a consistent grading scale. You can also copy the formula down a column in seconds. This is the standard approach for gradebooks, quiz scores, and any numeric score that needs a categorical label.
How the Nested IF Formula Works
The nested IF checks conditions in order. Excel evaluates the first condition; if it is TRUE, it returns the corresponding value and stops. If FALSE, it moves to the next IF. This is why the order matters: you must list thresholds from high to low. With scores in A2, the formula =IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F")))) works as follows:
- If A2 is 90 or above, returns “A”.
- If not, but A2 is 80–89, returns “B”.
- If not, but A2 is 70–79, returns “C”.
- If not, but A2 is 60–69, returns “D”.
- If none of the above (below 60), returns “F”.
For a score of 85, the formula returns “B”. For 59, it returns “F”.
Using IFS for a Cleaner Formula
The IFS function (available in Excel 2019, Excel 2021, and Microsoft 365) eliminates the repeated IF and closing parentheses. The syntax is =IFS(condition1, value1, condition2, value2, ...). The same grading scale becomes =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F"). The final TRUE acts as a catch-all for scores below 60. If you omit a catch-all and no condition matches, the formula returns #N/A. For example, if you only had conditions for A through D, a score of 50 would give #N/A.
How to Choose Between IF, IFS, and VLOOKUP
If you have a simple scale (like the 90/80/70/60 example), IFS is the clearest. For more complex rules (like grade boundaries that change or extra conditions), a lookup table with VLOOKUP or XLOOKUP is more flexible and easier to maintain. Here’s how to set up a lookup table:
- In cells D2:E5, enter the lower bound and grade: 90/A, 80/B, 70/C, 60/D (leave the lowest row for F, e.g., 0/F).
- Use the formula
=VLOOKUP(A2,$D$2:$E$6,2,TRUE)(assuming D2:E6 contains the table). TheTRUEargument finds the largest value less than or equal to the score. - For Excel 2021 or 365, you can use
=XLOOKUP(A2,$D$2:$D$6,$E$2:$E$6,,-1)where -1 means “next smaller item”.
This method is easier to update: change the boundary in the table, and all grades update. It also avoids long nested formulas if you have many grade bands.
Common Problems and How to Fix Them
Several issues often trip up users:
- #NAME? error: Usually means you typed the function name wrong (e.g.,
IFSin Excel 2016). Use nested IF if your version doesn’t support IFS. - #VALUE! error: The cell contains text or a blank. Ensure the score is numeric. You can use
=IF(ISNUMBER(A2), formula, "")to handle blanks. - Wrong grades due to order: If you write
=IF(A2>=60,"D",IF(A2>=70,"C",...)), every score above 60 gets a D. Always list thresholds from highest to lowest. - Boundary confusion: Decide whether 90 is an A or a B. The formula above uses >=90 for A, so 90 is an A. If you want 90 to be a B, change to >90.
- Copying formulas with relative references: If you use
A2and drag down, it becomes A3, A4, etc. That’s correct. But if you reference a lookup table, use absolute references like$D$2:$E$6.
Using Conditional Formatting to Color-Code Grades
Once you have the letter grades, you can highlight them visually. Select the grade cells, go to Home > Conditional Formatting > Highlight Cells Rules > Equal To, and enter “A” to set a fill color. Repeat for B, C, D, and F. Or use New Rule > Use a formula with a formula like =$B2="A" to format entire rows. This makes it easy to spot top performers or failing students at a glance.
Handling Plus/Minus Grades and Pass/Fail
For plus/minus (A+, A, A-, B+, etc.), you need more thresholds. A nested IF can handle it, but it gets long. A lookup table is better. For example, set boundaries: 97/A+, 93/A, 90/A-, 87/B+, etc. Use VLOOKUP with the table. For pass/fail, simply use =IF(A2>=60,"Pass","Fail"). You can also combine with other criteria, like =IF(AND(A2>=90,B2="Yes"),"A",...) for extra conditions.
Frequently Asked Questions
How do I assign a grade range in Excel without typing each IF?
Use a lookup table with VLOOKUP or XLOOKUP as described above. It’s the cleanest method for many grade bands. For a simple 5-letter scale, IFS is fine.
Can I use a formula to count how many students got each grade?
Yes. Use COUNTIF on the grade column. For example, =COUNTIF(B2:B100,"A") counts A’s. Or use COUNTIFS for multiple criteria.
What if my scores are in percentages but the formula gives #N/A?
If you used IFS without a final TRUE condition, any score that doesn’t match a condition returns #N/A. Add TRUE,"F" at the end. If you used VLOOKUP, ensure the first column of the table has the lowest possible score (like 0) as the smallest value.
How do I make the grade formula ignore blank cells?
Wrap the formula in an IF that checks if the cell is blank: =IF(A2="","",IF(A2>=90,"A",...)). This leaves the grade cell empty until you enter a score.
Is there a way to round scores before grading?
Yes, use ROUND inside the formula. For example, =IFS(ROUND(A2,0)>=90,"A",...) rounds to the nearest integer. Or wrap the entire grade formula: =IF(ROUND(A2,0)>=90,...).
Can I use a grade range formula in Google Sheets?
Yes, the same IF, IFS, and VLOOKUP functions work in Google Sheets. The IFS function is available. The only difference is the menu paths for conditional formatting (Format > Conditional formatting).
Related
- Excel Conditional Formatting Duplicate Rows: Full Guide
- Excel Conditional Formatting Date Occurring: How To
- Excel Conditional Formatting Color Scales Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUM Formula That Changes With FiltersUse =SUBTOTAL(109,B2:B100) to sum only the currently visible values in B2:B100. When you filter the rows, Excel recalculates…
- How to Get Out of Filter Mode in Excel and Show All RowsTo get out of filter mode in Excel and show every row, select a cell in the filtered…
- Dependent Dropdown in Excel Using INDIRECT: Setup GuideYes. Create a named range for each parent choice, then use INDIRECT in the child dropdown’s Data Validation…
- Excel PMT Function for Mortgage PaymentsUse Excel’s PMT function to estimate a fixed, regular mortgage payment for principal and interest. For a $300,000…
- Excel XLOOKUP Practice Examples: Formulas and TipsExcel XLOOKUP practice examples are easiest to learn by matching a lookup value to a column and returning…
- COUNTIF Excel Formula for Dates: Examples and FixesTo count cells that contain a particular date, use Excel’s COUNTIF function with a real date value as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.