The most direct Excel formula for a letter grade is a nested IF statement, for example =IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C",IF(A1>=60,"D","F")))) when the numeric score is in cell A1. For a cleaner and more maintainable approach, use the IFS function (Excel 2019 and later) or a VLOOKUP against a grade table. This article explains each method, how to choose between them, and how to handle common grading scenarios like plus/minus scales, pass/fail, and missing scores.
How the Nested IF Formula Works
The classic nested IF checks conditions from highest to lowest. Excel evaluates the first condition; if true, it returns that grade and stops; if false, it moves to the next IF. The order matters—starting with the highest threshold ensures that a score of 95 hits the first condition and never falls through to a lower grade. With the score in A1, the formula is:
=IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C",IF(A1>=60,"D","F"))))
This returns “A” for 90–100, “B” for 80–89, “C” for 70–79, “D” for 60–69, and “F” below 60. The same pattern extends to any number of grades—just add another IF for each threshold. For example, a 10-level scale (A, A-, B+, B, etc.) would require nine nested IFs. That quickly becomes hard to read and edit, which is why the IFS function is often better.
Using IFS for Simpler Logic
The IFS function (available in Excel 2019, Excel 2021, and Microsoft 365) lets you list conditions and results in pairs without nesting. For the same 5-grade scale, with the score in A1:
=IFS(A1>=90,"A",A1>=80,"B",A1>=70,"C",A1>=60,"D",A1<60,"F")
Note that IFS requires a final catch-all condition (like A1<60) to handle scores below 60; otherwise it returns #N/A if no condition is true. The order still matters—Excel checks each condition in the order you list them. IFS is easier to read than a long nested IF, but it still hardcodes the thresholds inside the formula. If your grading scale changes, you must edit the formula itself.
VLOOKUP with a Grade Table (Best for Maintainability)
The most professional approach is to store the grading scale in a table and use VLOOKUP with an approximate match. This separates data from logic, so you can change the scale without touching the formula. Assume the score is in B2, and you have a table in D2:E6 with the lowest score for each grade in column D and the letter in column E:
D2: 0 E2: F
D3: 60 E3: D
D4: 70 E4: C
D5: 80 E5: B
D6: 90 E6: A
Then the formula in C2 is:
=VLOOKUP(B2,$D$2:$E$6,2,TRUE)
The TRUE argument (or omitting it) tells Excel to find the largest value in column D that is less than or equal to the lookup value. So a score of 85 matches the row with 80 and returns “B”. This method is easy to update: change the table, and all grades recalculate. You can also use XLOOKUP (Excel 2021/Microsoft 365) with the same table: =XLOOKUP(B2,$D$2:$D$6,$E$2:$E$6,,-1) where the -1 means “exact match or next smaller”.
How to Choose the Right Method
Your choice depends on how often the scale changes and how many grade levels you need. Consider these factors:
- Simple, one-time scale: A nested
IFis fine and requires no extra table. - Readable formula:
IFSis cleaner than nestedIFs and works in most modern Excel versions. - Frequently adjusted scale: Use
VLOOKUPorXLOOKUPwith a table so you only edit the table, not the formula. - Many grade levels (e.g., plus/minus): A table is far easier to maintain than a long formula.
- Need to show the scale to others: A visible table is self-documenting.
For a one-off calculation, any method works. For a spreadsheet you will reuse, the table approach is usually worth the extra setup.
Handling Plus/Minus Grades and Pass/Fail
For plus/minus scales, the same VLOOKUP table method works—just list every threshold. For example, an A+ might be 97+, A is 93–96, A- is 90–92, and so on. The table would have rows for 0, 60, 63, 67, 70, 73, 77, 80, 83, 87, 90, 93, and 97. The formula stays identical. For pass/fail, a single IF suffices: =IF(A1>=70,"Pass","Fail") or use IFS for more categories. If you need to handle missing scores (blank cells), wrap your formula in IF to return a custom message: =IF(A1="","No score",IF(A1>=90,"A",...)). This prevents blank cells from being treated as 0 and returning an “F”.
Common Problems and How to Fix Them
Several issues trip up users when grading with formulas:
- #N/A from VLOOKUP: This happens when the lookup value is below the smallest threshold in the table. Solution: ensure your table starts at 0 (or a negative number) for the lowest grade.
- #VALUE! error: Usually means the score cell contains text or is blank. Use
IFto check for blanks, and ensure scores are numeric. - Wrong grade for boundary values: If you use
VLOOKUPwithTRUE, the table must be sorted ascending by the lookup column. If not, results are unpredictable. - IFS returns #N/A: You forgot a final catch-all condition. Always include a last condition that covers all remaining cases.
- Nested IF too long: Excel limits nested
IFs to 64 levels in modern versions, but readability suffers long before that. Switch toIFSor a table.
If you get a result that seems wrong, double-check the order of conditions and the table’s sorted order.
Comparing IFS, Nested IF, and VLOOKUP
Here is a quick comparison to help you decide:
- Nested IF: Works in every Excel version, but becomes unwieldy beyond 3–4 grades.
- IFS: Cleaner syntax, but requires Excel 2019 or later. Still hardcodes thresholds.
- VLOOKUP/XLOOKUP: Best for complex scales and frequent changes, but requires a separate table and understanding of approximate match.
All three produce identical results when set up correctly. The choice is about maintainability and your comfort level.
Frequently Asked Questions
How do I add a plus/minus grade scale in Excel?
Create a table with every threshold (e.g., 97 for A+, 93 for A, 90 for A-, etc.) and use VLOOKUP with TRUE or XLOOKUP with match mode -1. The formula stays the same; you just add more rows to the table.
Can I use a formula that doesn’t require a separate table?
Yes, use IFS or nested IF. For a 5-grade scale, IFS is concise. For more than 5–6 grades, a table is much easier to manage.
What if my scores are in percentages with decimals, like 89.5?
The formulas work fine with decimals. IF and IFS compare the exact value, and VLOOKUP with TRUE finds the correct threshold. Just ensure your thresholds match your intended rounding (e.g., 89.5 might round to 90 or stay a B, depending on your scale).
How do I handle blank cells or text in the score column?
Wrap your formula in an IF that checks for blank: =IF(A1="","No grade",your_formula). For text, you can use ISNUMBER to return an error message: =IF(ISNUMBER(A1),your_formula,"Invalid").
Does the order of conditions in IFS matter?
Yes. Excel evaluates conditions left to right and returns the result for the first true condition. Always list thresholds from highest to lowest, or use a catch-all at the end.
Can I use conditional formatting to color-code grades instead of a formula?
Yes, you can use Home > Conditional Formatting > Highlight Cells Rules or a formula rule to color cells based on the numeric score. But that only changes the appearance; you still need a formula to display the letter grade in a cell.
Related
- Excel Conditional Formatting Color Scales Explained
- Excel Choose Date from Calendar Cell: How to Add a Date Picker
- Excel Center Louisville KY: What It Is & How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- PL/SQL String Functions: Syntax, Examples, and UsesPL/SQL string functions let you measure, search, extract, change, and format character data; commonly used examples include LENGTH,…
- Arduino Pass String to Function: Examples, Types, and SafetyYes. In Arduino C++, pass a String to a function by putting its type in the parameter list,…
- Remove Spaces in One Excel Cell: Formula and StepsTo remove every ordinary space from one Excel cell, enter =SUBSTITUTE(A1," ","") in a nearby blank cell, replacing…
- Excel Time Card Formula: Track Hours and OvertimeFor a basic Excel time card, enter a start time in B2, an end time in C2, and…
- Excel: Sum VLOOKUP Values Across Multiple Matching RowsTo total every row that matches a lookup value, use SUMIF, not a regular VLOOKUP: with the matching…
- Can You Filter Out Duplicates in Excel? Methods That WorkYes. In Excel for Microsoft 365 on Windows, you can identify duplicates with conditional formatting, filter them with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.