In Microsoft Excel, calculate a percentage grade by dividing points earned by points possible: with points earned in B2 and points possible in C2, enter =B2/C2 in D2 and format D2 as a percentage. To convert that result to a letter grade, use nested IF statements with the grading cutoffs your course requires.
What an Excel grade formula calculates
A grade formula can mean a few different things: a percentage from points, a letter based on that percentage, or a final mark assembled from weighted assignments. Excel does not impose a universal grading scale. The right formula depends on how the instructor or school defines points, category weights, cutoffs, missing work, and rounding.
The basic points formula divides the score earned by the maximum score. For example, 42 points out of 50 is 42 divided by 50, or 0.84. When the cell is displayed as a percentage, Excel shows 84%. The underlying value remains 0.84, which matters when another formula compares it with a cutoff such as 90%.
Before building a spreadsheet, decide whether you want a raw percentage, a letter grade, or both. Keeping the percentage and letter in separate columns makes the result easier to check and lets you change the letter-grade scale without changing the original score.
Calculate a percentage from points
Put each student’s points earned in column B and the points possible for that assignment in column C, starting on row 2. With the earned score in B2 and possible score in C2, enter this in D2:
=B2/C2
For an example, if B2 contains 42 and C2 contains 50, the formula returns 0.84, which displays as 84% after percentage formatting. Select the result cells and choose Home > Number > Percent Style. You can adjust the number of displayed decimal places with the increase or decrease decimal buttons in the Number group. Formatting changes how the value appears, not the underlying calculation.
Copy the formula to other rows by dragging the fill handle at the lower-right corner of D2, or copy and paste it down the column. Excel adjusts the relative references: the next row uses B3 divided by C3. Check that each row’s possible-points value is correct, especially if different assignments have different maximum scores.
Avoid multiplying by 100 if you plan to format the result as a percentage. Doing both would make 42 out of 50 display as 8,400%. If you want the result as the number 84 rather than a percentage-formatted 84%, multiply by 100 and leave the cell in a regular number format; for most gradebooks, percentage formatting is clearer.
Convert a percentage into a letter grade
Suppose the percentage is in D2 and the grading scale is A at 90% or higher, B at 80% or higher, C at 70% or higher, D at 60% or higher, and F below 60%. With the percentage in D2, enter the following in E2:
=IF(D2>=90%,"A",IF(D2>=80%,"B",IF(D2>=70%,"C",IF(D2>=60%,"D","F"))))
If D2 is 84%, this formula returns B. The order matters: Excel checks each condition from left to right and returns the result for the first condition that is true. Testing the highest threshold first ensures that a 95% is labeled A rather than being caught by a lower cutoff.
This is only a sample scale. Some classes use plus and minus grades, different boundaries, or no D grade. Change the thresholds and returned labels to match the published policy. For instance, a scale with A starting at 93% must use 93% as the first threshold; do not assume that a familiar scale applies to every course.
Build a weighted final grade
When assignments contribute different shares of the final mark, a simple average of the category percentages may be wrong. For example, homework might count for 30%, quizzes for 30%, and exams for 40%. Store those category grades in B2:B4 as percentage values and the corresponding weights in C2:C4 as 30%, 30%, and 40%. With grades in B2:B4 and weights in C2:C4, enter:
=SUMPRODUCT(B2:B4,C2:C4)
If the category grades are 90%, 80%, and 95%, the result is 89%: 90% × 30% plus 80% × 30% plus 95% × 40%. SUMPRODUCT multiplies matching cells and adds the products. The weights should add to 100% for a conventional weighted average; if they do not, the result may not represent the intended final percentage. You can check the weight total in a separate cell.
This calculation assumes each category grade already represents that category correctly. If a category grade is itself based on points, decide whether assignments within it are equally weighted or weighted by possible points. A raw average of assignment percentages gives a 5-point quiz the same influence as a 100-point exam; dividing total earned points by total possible points gives more influence to the larger assignment. Follow the grading policy rather than choosing whichever method gives a preferred result.
Handle blank cells and invalid scores
A blank score should not automatically become a zero unless the course policy says missing work is zero. In the basic formula, a blank points-possible cell can cause a division error, and a zero maximum points value also makes division invalid. It is often better to flag incomplete rows or leave their grade blank until the inputs are ready.
For a row with earned points in B2 and possible points in C2, this version returns a blank if either input is blank or if the calculation produces an error:
=IF(OR(B2="",C2=""),"",IFERROR(B2/C2,""))
For example, if B2 is 42 and C2 is 50, it returns 0.84; if either cell is empty, it returns an empty string, which appears blank. IFERROR also suppresses errors such as division by zero. That can make a sheet cleaner, but it can hide bad input, so consider checking for invalid values separately instead of treating every error as harmless.
Decide how to handle extra credit and scores above the stated maximum. The basic division formula can return more than 100% if earned points exceed possible points, which may be intentional. If the rules cap grades at 100%, apply that rule explicitly and document it; do not silently cap scores if the grading policy allows extra credit.
Choose a grading scale and rounding rule
Use the exact cutoffs and rounding method supplied by the teacher, department, or institution. A displayed value of 89.5% might look like 90% when rounded to a whole number, but that does not necessarily mean it qualifies for an A. Excel can display fewer decimals without changing the stored value, so a letter formula can still compare the full precision behind the display.
There are two distinct choices: rounding the percentage for display and rounding the value used to assign a grade. A policy may round to the nearest whole percentage point before assigning letters, or it may compare the unrounded value with its cutoffs. Those rules can produce different results near a boundary. If a policy specifies rounding, apply that exact rule consistently; otherwise, avoid assuming that changing the displayed decimal places changes the grade calculation.
It is also useful to keep the grading scale visible in the workbook, with a note stating where it came from. This makes the spreadsheet easier to audit and update if the course uses different thresholds in another term.
Common spreadsheet mistakes
- Using the wrong denominator: Divide by the points possible for that score, not by a fixed total that applies only to another assignment.
- Entering 84 instead of 84%: In a percentage-based letter formula, 84% is stored as 0.84. A cell containing the number 84 is not the same value and will exceed every threshold written as a percentage.
- Using equal averages for unequal weights: If categories have different contributions, multiply each grade by its stated weight rather than averaging the category percentages.
- Misreading blanks: A blank can mean not yet graded, exempt, or missing. Those meanings should not be combined without checking the grading policy.
- Relying on appearance: Percentage formatting and displayed decimal places do not by themselves change the value used in calculations.
Test formulas with a few known cases before filling a large gradebook: a score at a cutoff, one just below it, a full score, and a blank row. That quick check helps catch reversed comparisons, incorrect cell references, and scale assumptions.
Use the formula in a practical gradebook
A straightforward worksheet can use one row per student and separate columns for the student’s name, points earned, points possible, percentage, and letter. For a class with multiple assignments, give each assignment its own earned-points and possible-points columns, or calculate category grades in a separate section before calculating the weighted final grade. Clear headings and consistent units reduce the chance of comparing points with percentages by mistake.
On Excel for Microsoft 365 for Windows, formulas are entered in the formula bar or directly into a selected cell. The Home tab’s Number group contains percentage formatting. Excel for Mac has the same core formulas and a similar Home tab formatting control, though ribbon placement and appearance can vary with the app version. Excel for the web also supports these standard formulas and percentage formatting, though its ribbon layout may differ slightly.
Protect student information appropriately if the workbook contains identifiable grades. Limit access to people who need it, and follow the school’s data-handling rules. A formula helps calculate marks; it does not verify that the source scores, weights, or grading policy are correct.
Frequently Asked Questions
What is the basic formula for grades in Excel?
Divide points earned by points possible. If the earned score is in B2 and the possible score is in C2, enter =B2/C2 and format the result as a percentage. For example, 42 out of 50 displays as 84%.
How do I calculate a letter grade in Excel?
First calculate the percentage, then compare it with the course’s cutoffs using nested IF statements. The sample formula above assigns A at 90%, B at 80%, C at 70%, D at 60%, and F below 60%; replace those boundaries if your grading scale differs.
How do I calculate a weighted grade?
Multiply each category grade by its matching weight and add the results. If category grades are in B2:B4 and weights are in C2:C4, =SUMPRODUCT(B2:B4,C2:C4) calculates the weighted result, provided the weights are entered as percentages or equivalent decimal values and reflect the grading policy.
Should I multiply the grade formula by 100?
Usually not. The division result is a decimal that Excel can display as a percentage, so 0.84 appears as 84%. Multiplying by 100 and then applying percentage formatting would display 8,400%.
Why does my grade show as a decimal?
The result is stored as a decimal, but the cell may be using General or Number formatting. Select the result and choose Home > Number > Percent Style to display 0.84 as 84%.
Related
- Master Excel Custom Number Formats: A Practical Guide
- Master List Excel Template: Create, Organize, and Use It
- Master Contact List Excel Template: Build and Manage It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Extracting Text from a CellTo extract a set number of characters in Excel, use =LEFT(A2,3) when the text is in A2 and…
- Excel Yes or No Formula: How to Return Either AnswerYes. To show “Yes” when a test is true and “No” when it is false, put the value…
- Excel Formula for Adding Hours on a Timesheet CorrectlyTo add timesheet hours in Excel, use =SUM(B2:B8) when the daily durations are in cells B2 through B8.…
- Excel Formula to Reference a Sheet Name from a CellTo reference a worksheet named in a cell, use Excel’s INDIRECT function. If A1 contains the sheet name…
- Excel MAX Function: Find the Highest Value in a RangeTo find the largest number in Excel, enter a formula such as =MAX(B2:B10) in an empty cell, replacing…
- Excel Date Subtraction: Formulas, Days, and WorkdaysTo subtract one date from another in Excel, subtract the earlier date cell from the later one: if…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.