If your class uses weighted categories, such as homework 15% and tests 25%, a spreadsheet is a handy way to track your grade all term. Excel can do the whole calculation with one formula, and it can also tell you what you need on the final. This guide builds a small grade sheet step by step, explains each formula, and covers the mistakes that most often give a wrong answer.
If you only need a quick answer, the free grade calculator does the same math in your browser. The spreadsheet approach is useful when you want to keep a running record or track several classes.
The weighted grade formula in plain words
A weighted grade multiplies each category average by its weight, adds the results, and divides by the total weight:
- Weighted grade = (average₁ × weight₁ + average₂ × weight₂ + …) ÷ (sum of the weights)
When every category is graded and the weights add up to 100%, dividing by the total weight changes nothing. It matters when some categories are still empty, which is most of the term. Our weighted grades guide explains the idea without a spreadsheet.
Step 1: Set up the sheet
Put one category per row. Column A holds the category name, column B your average in that category, and column C the weight from your syllabus. Here is the example we will use, with the final exam not taken yet:
| Row | A: Category | B: Average | C: Weight |
|---|---|---|---|
| 2 | Homework | 94% | 15% |
| 3 | Quizzes | 82% | 25% |
| 4 | Labs | 88% | 20% |
| 5 | Midterm | 76% | 15% |
| 6 | Final exam | (empty) | 25% |
Type the averages and weights with the % sign, such as 94%, and Excel stores them as decimals (0.94) and displays them as percentages. Plain numbers like 94 and 15 also work for the weighted average, but the final exam formula in Step 6 needs weights stored as percentages or decimals, so the % style is the safer habit.
Add a check cell under the weights: =SUM(C2:C6). It should show 100%. If it does not, recheck the syllabus before trusting any result.
Check your weighted grade or what you need on the final — free, in your browser.
Open the free grade calculator →Step 2: Calculate each category average
If your teacher gives you category averages, type them in. If you only have individual scores, calculate each category on its own sheet or block of cells.
For a points-based category, divide total points earned by total points possible. With earned points in D2:D9 and possible points in E2:E9:
=SUM(D2:D9)/SUM(E2:E9)
For example, 47 points earned out of 50 possible gives 47 ÷ 50 = 94%, the homework average in our table. Do not use =AVERAGE() on a list of percentages when the assignments are worth different numbers of points; that treats a 10-point quiz like a 100-point test. Our post on how to calculate grade percentage shows why with a worked example.
Step 3: The weighted grade formula
When every category has a score, use SUMPRODUCT. It multiplies each average by its weight and adds them all up in one step:
=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)
Step 4: Handle categories that are not graded yet
In our example the final exam is still empty. SUMPRODUCT treats an empty cell as zero, so the formula above would divide by the full 100% and effectively count the final as a zero. To divide only by the weights of categories that have a score, use SUMIF:
=SUMPRODUCT(B2:B6,C2:C6)/SUMIF(B2:B6,"<>",C2:C6)
The "<>" criterion means "not empty", so only rows with an average contribute their weight. Here is the arithmetic Excel does:
| Category | Average × weight |
|---|---|
| Homework | 94 × 0.15 = 14.1 |
| Quizzes | 82 × 0.25 = 20.5 |
| Labs | 88 × 0.20 = 17.6 |
| Midterm | 76 × 0.15 = 11.4 |
| Total | 63.6 |
The graded weights add up to 15% + 25% + 20% + 15% = 75%, so the current grade is 63.6 ÷ 0.75 = 84.8%. With the first formula you would see 63.6% instead, which looks like a failing grade but is only because the final has not happened yet.
Leave a category cell empty until it has a real score. Typing 0 tells Excel you scored zero.