For a percentage score in B2, use =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F")))) to return a letter grade; with 87 in B2, the result is B. This example uses a common 90/80/70/60 scale, which you can adjust to match your school or course policy.
Letter grades with nested IF formulas
The formula above checks each threshold from highest to lowest. Excel tests the first condition; if it is true, it returns that letter and stops. If it is false, Excel evaluates the next IF. The final “F” is the result when none of the earlier conditions is met. Because the thresholds are written into the formula, they are easy to see, but changing the grading scale means editing the formula.
To apply the same formula to other students, enter it in the grade cell beside the first score, then use the fill handle at the lower-right corner of that cell to copy it down. Excel adjusts B2 to B3, B4, and so on. Keep scores on a 0–100 scale for this version: a score entered as 87 means 87 points, not 87% stored as 0.87.
Letter-grade cutoffs are not universal. Some instructors use plus and minus grades, a different pass threshold, or a scale that gives an A at 93. Change the numbers to the official cutoffs before relying on the result. Also decide how to handle scores above 100, extra credit, and scores below zero; the nested IF returns A for any score of 90 or more, including a score over 100.
How Excel evaluates a grade formula
IF follows the pattern IF(test, result_if_true, result_if_false). In the example, B2>=90 is the first test. The greater-than-or-equal-to symbol matters: a score of exactly 90 receives an A, while a score of 89.9 proceeds to the next test. The order matters too. If you tested B2>=60 first, then every score of 60 or higher would receive the first matching result, making the higher-grade tests ineffective.
For a class with many students, one formula copied down is usually more reliable than typing each grade by hand. It also makes revisions straightforward: a changed score recalculates its grade, and a changed cutoff in the formula affects every copied formula after you update them. Use absolute references, such as $B$2, when a formula should keep pointing to one fixed cell as it is copied; ordinary relative references such as B2 are appropriate when each row has its own score.
Before filling a formula down a long roster, test boundary values. With the example scale, check 59, 60, 69, 70, 79, 80, 89, and 90. Those checks reveal off-by-one mistakes and confirm that the grade at a cutoff follows the intended policy. A quick test is especially helpful when the scale includes unusual intervals or plus/minus bands.
Calculate a weighted grade
A weighted grade gives assignments or categories different shares of the final result. For example, exams might count for 50% of the grade, homework for 30%, and a project for 20%. With scores in B2:B4 and matching weights in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). If the scores are 92, 84, and 78, and the weights are 20%, 30%, and 50%, the result is 82.6.
SUMPRODUCT multiplies each score by its corresponding weight and adds the products: 92 × 0.20 + 84 × 0.30 + 78 × 0.50 = 82.6. Dividing by the sum of the weights makes the formula work even if the entered weights total something other than exactly 100%. Still, check the weights: a typo such as 5 instead of 0.05 can radically change the result. Format the weight cells as percentages so the intended shares are easy to inspect.
Do not mix weight formats. If C2:C4 contains 20%, 30%, and 50%, Excel stores those as 0.2, 0.3, and 0.5, which is appropriate. If you enter 20, 30, and 50 as plain numbers, the formula still returns the weighted average because it divides by their total, but formatting them as percentages helps prevent confusion. If the scores themselves are stored as percentage fractions such as 0.92, keep the whole calculation consistent and format the output as a percentage.
Average scores and combine course categories
For a simple average, where every assignment counts equally, with scores in B2:B4 enter =AVERAGE(B2:B4). For scores of 88, 92, and 76, Excel returns 85.333333…; applying a one-decimal display format shows 85.3 without changing the underlying value. If you want the formula itself to return a rounded value, use =ROUND(AVERAGE(B2:B4),1) with the same scores and range; the result is 85.3.
Choosing between a simple average and a weighted average is a policy decision, not just a spreadsheet preference. A simple average gives every included score equal influence. A weighted average reflects specified shares, such as a final exam counting more than a short quiz. Confirm whether category weights apply to individual assignments or to category averages. Those methods can produce different results when categories contain different numbers of assignments.
Excel’s AVERAGE function ignores empty cells but includes cells containing zero. That is useful if an ungraded assignment should not yet count, but it can be misleading if a missing submission is supposed to count as zero. Agree on what blank means before using an average in a gradebook. In particular, do not replace missing work with zero automatically unless that matches the course rules.
Use a lookup table for reusable grade scales
A lookup table is often easier to maintain than a long nested formula, especially if the scale may change or includes more letter grades. Put the minimum score for each band in A2:A6 and its grade in B2:B6: 0/F, 60/D, 70/C, 80/B, and 90/A. Keep the minimum scores in ascending order. With the student’s score in D2, use =VLOOKUP(D2,$A$2:$B$6,2,TRUE). A score of 87 returns B.
The final argument TRUE asks VLOOKUP for an approximate match. It finds the greatest first-column value that is less than or equal to the score, which is why the cutoff column must be sorted in ascending order. The dollar signs lock the table range when you copy the formula to other rows. This method assumes scores are at least zero and that the table covers the score range you need; add or adjust bands if your grading rules require them.
A table makes the policy visible to a colleague reviewing the workbook and lets you update a cutoff in one place. It also reduces the chance of missing a nested IF when extending the scale. For a small, fixed scale, a nested IF is quick and self-contained. For a scale that needs maintenance, a lookup table is usually clearer. In Microsoft 365, XLOOKUP is another option, but VLOOKUP is widely understood and works in older Excel versions as well.
Pass/fail results and blank scores
For a pass/fail rule with a 60-point passing cutoff, with the score in B2 enter =IF(B2>=60,"Pass","Fail"). If B2 contains 72, the result is Pass; if it contains 58, the result is Fail. Replace 60 with the actual required threshold. This formula treats a blank B2 as zero and therefore returns Fail, which may not be right for work that has not been graded yet.
To leave the result blank until a score is entered, with the score in B2 use =IF(B2="","",IF(B2>=60,"Pass","Fail")). If B2 is empty, the result is empty; with 72, it is Pass. The outer IF checks for a blank before testing the score. Use the same pattern with a letter-grade formula when you do not want every empty row in a roster to display F.
Be careful with cells that contain a formula returning an empty string: they can look blank even though they contain a formula. The test B2=”” treats an empty cell and an empty-string result as blank. If your worksheet uses special text such as “N/A” to mark exempt work, decide how that status should affect the final grade rather than letting it be treated as a number by accident.
Formatting and common grade-formula problems
Keep raw scores, weights, and calculated grades in separate columns. Label each column and state whether scores are entered as points from 0 to 100 or as percentages from 0% to 100%. For a numeric score column, use a consistent number format and sensible decimal places. A grade can appear wrong simply because the cell is displaying a rounded value; formatting changes what you see, while ROUND changes the calculated result.
- Wrong scale: A formula with a cutoff of 90 expects 90, not 0.90. If your data uses percentage fractions, use thresholds such as 0.9 consistently or convert the data to a 0–100 scale.
- Weights do not match scores: SUMPRODUCT pairs items by position, so the first score must have its weight in the first corresponding cell.
- Unexpected blanks or zeros: AVERAGE ignores truly empty cells but counts zero. Check whether a missing grade is blank, zero, or text.
- Boundary errors: Use >= when the cutoff score itself should earn the higher grade; use > only if the policy says the score must exceed the cutoff.
- Copied formulas point to the wrong table: Lock fixed lookup ranges with dollar signs, as in $A$2:$B$6.
In Excel for Microsoft 365 on Windows, you can format cells from Home > Number, and use Home > Conditional Formatting to highlight scores or grades that need review. Conditional formatting changes appearance, not the grade calculation, so it should not replace a formula. Excel for Mac has the same core formulas; ribbon placement and keyboard shortcuts can differ by version and window layout. Excel for the web also supports these basic formulas, though some ribbon controls and editing details may look different.
Frequently Asked Questions
What is the Excel formula for a letter grade?
For a 90/80/70/60 scale and a score in B2, use the nested IF formula shown above. Change the cutoff values and returned letters to match the grading policy, and test scores at each boundary before copying it down.
How do I calculate a weighted grade in Excel?
Use SUMPRODUCT to multiply each score by its matching weight, then divide by the total weights. Keep the score and weight ranges the same size and in the same order. For weights of 20%, 30%, and 50%, the example formula returns 82.6 for scores of 92, 84, and 78.
How do I keep Excel from showing F for an empty score?
Test for a blank before evaluating the score, using an outer IF that returns an empty string when the score cell is empty. This is useful for unfinished gradebooks, but it does not decide how late or missing work should count.
Does Excel round a grade automatically?
Excel stores and calculates values at their underlying precision, even when a cell displays fewer decimal places. Change the number format to control display, or use ROUND in the formula when the grading rules require the calculated value itself to be rounded.
Should I use IF or a lookup table for grades?
Nested IF is convenient for a short scale that rarely changes. A sorted lookup table is easier to inspect and update when cutoffs may change or multiple people use the workbook. Either approach can return the same grades if its thresholds and boundary rules are correct.
Related
- Excel File Too Large? How to Make It Smaller in Excel
- Excel File Too Large to Email? Practical Fixes
- Excel File Size Too Large? How to Reduce It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter: How to Exclude Text From Your ResultsTo exclude rows containing particular text in Excel for Microsoft 365 on Windows, open the column’s filter arrow,…
- How to Filter Two Columns in ExcelTo filter two columns in Excel, turn on AutoFilter for your data, then choose a filter in the…
- LAMBDA in Excel: A Practical Guide to Custom FunctionsYes—Excel’s LAMBDA function lets you create reusable custom functions without writing VBA. For a quick test, with no…
- Excel Formula to Highlight Every Other Row AutomaticallyTo highlight every other row in Excel, select the range, open Home > Conditional Formatting > New Rule,…
- How to Delete a Whole Column in Excel the Right WayTo delete a whole column in Excel, select its lettered header, then choose Home > Delete > Delete…
- Excel LEFT of a Character: Formula and ExamplesTo return everything to the left of a character in Excel, combine LEFT with FIND: with the text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.