To assign letter grades in Excel, put each numeric score in column B and enter a nested IF formula in C2: =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F")))). It returns A for 90 or higher, B for 80–89.99, C for 70–79.99, D for 60–69.99, and F for anything below 60; adjust the cutoffs to match your grading policy.
How the IF grading formula works
Excel’s IF function tests a condition and returns one value when the condition is true and another when it is false. In the example, B2>=90 is the first test. If it is true, Excel returns A and stops. If it is false, Excel checks the next IF, which asks whether the score is at least 80. This continues until a condition matches; if none does, the final value, F, is returned.
The order of the tests matters. The formula checks the highest cutoff first, then moves downward. A score of 92 meets the first condition and receives A. A score of 84 fails the 90 test but meets the 80 test, so it receives B. A score of 59 fails every threshold and reaches the final F. If you test for a lower cutoff first, such as 60, then a score of 95 also meets that condition and could be assigned D before Excel ever checks the A threshold.
The example assumes a 0–100 scale and a specific grading policy. Excel does not decide what counts as an A; the numbers in the formula encode your chosen rules. Some courses use plus and minus grades, curved grades, or different passing thresholds, so confirm the scale before filling the formula down a class list.
Set up score and grade columns
A simple worksheet makes the formula easier to enter and audit. Use a header row, one student per row, and separate columns for raw scores and calculated grades. For example:
- Column A: student name or ID.
- Column B: numeric score out of 100.
- Column C: letter grade.
With headers in row 1, put the first score in B2 and the formula in C2. Keep the score as a number rather than typing a combined entry such as “84 points” into the cell; text makes calculations and validation less dependable. You can format the header row in bold and keep the grade column separate so it is clear which values were entered and which were calculated.
For the basic formula, the assumed layout is student names in A2:A20, scores from 0 to 100 in B2:B20, and grades beginning in C2. Enter this in C2:
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))
For a score of 84 in B2, the result is B. For a score of exactly 90, the result is A, because the comparison uses “greater than or equal to.” That detail defines the boundary: 80 receives B, 70 receives C, and 60 receives D.
Fill the formula down the class list
Once the first result is correct, copy the formula to the other students’ rows. Select C2, point to the small square at the lower-right corner of the cell (the fill handle), and drag it down to the last student. Excel changes the relative reference from B2 to B3, B4, and so on. You can also copy C2, select the destination cells, and paste.
On Windows, Ctrl+D fills the selected range downward when the top cell contains the formula to copy. On Mac, the equivalent is Command+D. Another option is to select the range including the formula cell and press Ctrl+Enter on Windows or Control+Return on Mac to enter the formula into all selected cells; take care that the reference is appropriate for every row. After filling, spot-check a few rows against their scores, especially values exactly on a cutoff.
Handle blanks, invalid entries, and boundaries
The basic formula returns F for a blank score because a blank can be treated like zero in this comparison. It also returns F for a score above 100, since that score meets the 90 condition. Those outputs may conceal missing or mistyped data. A more guarded formula can leave an empty score blank, flag text or out-of-range values, and grade only numeric scores from 0 through 100.
Assume names are in A2:A20 and scores are in B2:B20 on a 0–100 scale. Enter this in C2 and fill it down:
=IF(B2="","",IF(NOT(ISNUMBER(B2)),"Check score",IF(OR(B2<0,B2>100),"Check score",IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F")))))))
If B2 is empty, the result is blank. If B2 contains text or a number outside 0–100, the result is Check score. If B2 contains 74, the result is C. The checks appear before the grade tests so invalid entries do not quietly become a letter grade. The ISNUMBER check is useful when a score might have been pasted as text, although a value such as “84” stored as text still needs to be corrected to a number.
Another safeguard is data validation. In Excel for Microsoft 365 on Windows, select the score cells and choose Data > Data Validation. Set Allow to Whole number or Decimal, then specify a minimum of 0 and maximum of 100. On Mac, use Data > Data Validation as well; labels and dialog details can vary slightly by release. Validation discourages out-of-range entries, but it does not replace checking imported or pasted data.
Use IFS or a grade lookup table
Nested IF is widely compatible and makes the decision sequence explicit, but a long chain can be difficult to read or edit. Excel also provides IFS, which checks conditions in order and returns the result associated with the first true condition. It is available in Microsoft 365 and newer Excel releases, including Excel 2019. As with nested IF, put the highest cutoff first.
Assume B2 contains a numeric score from 0 to 100. This IFS formula assigns the same grades, including F for valid scores below 60:
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2>=0,"F")
A score of 67 returns D, while 45 returns F. This version does not handle blank, text, or negative scores as clearly as the guarded nested formula: a negative value meets none of the tests and returns an error. Use it when the input is already controlled, or add validation and separate error handling.
For a grading policy that changes over time, a threshold table can be easier to maintain than editing a formula in every row. Put the minimum score cutoffs in E2:E6 in ascending order—0, 60, 70, 80, 90—and the corresponding grades in F2:F6—F, D, C, B, A. The values in the cutoff column must be sorted from smallest to largest for this approximate lookup.
Assuming B2 contains the score and the table occupies E2:F6, use this formula in C2. XLOOKUP is available in Microsoft 365 and Excel 2021 or later:
=XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,"Check score",-1)
The -1 match mode asks for an exact match or the next smaller cutoff. With a score of 84, Excel finds 80 and returns B; with 59, it finds 0 and returns F. A score below 0 returns Check score. Because a score above 100 would still match the 90 cutoff and return A, use validation or add an upper-bound check if scores must not exceed 100. The dollar signs keep the lookup table fixed when the formula is filled down.
Choose the right scale for percentage scores
One common source of surprising results is confusing a score out of 100 with a percentage stored as a decimal. If a cell displays 92% in Excel, its underlying value is usually 0.92, not 92. Comparing that value with 90 would never award an A. Either store percentage points as numbers such as 92, or use decimal cutoffs that match percentage-formatted values.
Assume B2 contains a genuine percentage stored from 0 to 1—for example, 92% is stored as 0.92—and the grade cutoffs are 90%, 80%, 70%, and 60%. Enter this in C2:
=IF(B2>=0.9,"A",IF(B2>=0.8,"B",IF(B2>=0.7,"C",IF(B2>=0.6,"D","F"))))
A value of 0.92 returns A, and 0.84 returns B. Do not apply this decimal-scale formula to scores entered as 92, because 92 is greater than 0.9 and would receive A regardless of the intended grade. Confirm how the score column is stored before choosing thresholds.
Common formula problems and troubleshooting
If grades seem shifted by one category, check both the order of the tests and the cutoff definitions. A formula using >90 awards A only above 90, while >=90 includes exactly 90. Decide which rule applies and use the same boundary convention at each threshold. Test representative scores such as 59, 60, 69, 70, 89, and 90.
If Excel displays the formula as text instead of calculating it, check whether the cell is formatted as Text or the formula begins with an apostrophe. Change the format to General, then re-enter the formula. If Excel shows a formula error after you type it, check that parentheses are balanced and that quotation marks are straight double quotes. The formulas here use commas between arguments; some regional settings require semicolons instead, depending on Excel’s list separator configuration.
If copied formulas all refer to the same score cell, inspect the references. B2 should be relative so it changes to B3 and B4 as you fill downward. By contrast, lookup-table references such as $E$2:$E$6 should be absolute so they do not move. Also check that there are no hidden spaces or inconsistent text entries in the score column. Sorting or filtering does not change the grading logic, but ensure that names, scores, and grades remain on matching rows.
Frequently Asked Questions
Can I use IF to assign plus and minus grades?
Yes. Add more thresholds and return labels such as A-, B+, or C-. Put each cutoff in descending order and define every boundary explicitly. For example, decide whether 90 starts A- or A, then test scores at and around that cutoff to confirm the intended result.
Why does a blank score show F?
The basic formula’s final result is F when none of its score thresholds is met, and a blank may be evaluated like zero in this comparison. Use a blank check such as the one in the guarded formula to return an empty string for an empty score cell.
Can Excel calculate a grade from weighted assignments?
Yes, but calculate the weighted numeric score first, then apply the letter-grade formula to that result. Make sure the weights and assignment scores use consistent scales and that the weights total the intended amount. The letter-grade formula only categorizes the number it receives; it does not determine whether the weighting policy is correct.
Should I use nested IF, IFS, or XLOOKUP?
Use nested IF for broad compatibility and straightforward rules. IFS can make a moderate number of conditions easier to read. A lookup table with XLOOKUP is often easier to update when cutoffs are maintained as data, but it requires a compatible Excel version and a correctly sorted threshold table.
Does this formula work if scores are out of 10?
Not with 60, 70, 80, and 90 cutoffs. Convert the thresholds to the same scale—for example, 6, 7, 8, and 9 for a comparable 0–10 policy—or convert the scores to percentages first. The score and thresholds must use the same units.
Related
- Excel Text Filters: How to Find Matching Rows Easily
- Excel Text Box Wrap Text: How to Enable It
- Excel Template for a Social Media Calendar
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Copy a Formula Down All Rows in ExcelTo copy a formula down a column in Excel, enter it in the first cell, select the cells…
- Conditional Formatting Formula If Statement in ExcelIn Excel, a conditional-formatting formula must evaluate to TRUE or FALSE for the cell or row you want…
- Excel SUM Function: Does It Count Filtered Values?Yes. In Excel for Microsoft 365, SUM adds every numeric value in its referenced range, including values in…
- Excel Dynamic Array Filter Multiple Criteria?Use Excel’s FILTER function and combine criteria with multiplication for AND or addition for OR. With order data…
- DAX Function in Excel: What It Does and How to Use ItDAX in Excel is a formula language for calculations in the workbook Data Model, used through Power Pivot…
- Compare Two Sheets in Excel: Methods, Formulas, and TipsTo compare two Excel sheets, check corresponding cells with a formula such as =IF(January!A2=February!A2,"Match","Different"), then fill it across…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.