To calculate GPA in Excel or Google Sheets, list each class’s letter grade and credits, convert the letters to grade points with a lookup table, then divide the credit-weighted total by the total credits: =SUMPRODUCT(C2:C7, D2:D7)/SUM(C2:C7). The same formulas work in both programs, with one exception covered below.
Everything here uses the standard plus/minus 4.0 scale (A = 4.0, A- = 3.7, B+ = 3.3 and so on). If your school uses a different one, change the lookup table and every formula that points to it updates.
Step 1: Build a “Scale” sheet
Add a tab named Scale with three small lookup tables. Columns C and F stay empty.
Columns A–B, letter to points:
| Row | A | B |
|---|---|---|
| 1 | Letter | Points |
| 2 | A+ | 4.0 |
| 3 | A | 4.0 |
| 4 | A- | 3.7 |
| 5 | B+ | 3.3 |
| 6 | B | 3.0 |
| 7 | B- | 2.7 |
| 8 | C+ | 2.3 |
| 9 | C | 2.0 |
| 10 | C- | 1.7 |
| 11 | D+ | 1.3 |
| 12 | D | 1.0 |
| 13 | F | 0.0 |
Columns D–E, level bonus: D1 “Level”, E1 “Bonus”, then Regular / 0 in row 2, Honors / 0.5 in row 3 and AP / 1.0 in row 4.
Columns G–H, percent to letter, sorted from lowest to highest (this matters, see Step 7):
| Row | G (minimum %) | H (letter) |
|---|---|---|
| 2 | 0 | F |
| 3 | 65 | D |
| 4 | 67 | D+ |
| 5 | 70 | C- |
| 6 | 73 | C |
| 7 | 77 | C+ |
| 8 | 80 | B- |
| 9 | 83 | B |
| 10 | 87 | B+ |
| 11 | 90 | A- |
| 12 | 93 | A |
| 13 | 97 | A+ |
Step 2: Lay out your classes
On the first tab (call it Classes), one row per class:
| Row | A: Class | B: Grade | C: Credits | D: Points | E: Level | F: Bonus | G: Weighted points |
|---|---|---|---|---|---|---|---|
| 1 | Class | Grade | Credits | Points | Level | Bonus | Weighted |
| 2 | English 11 Honors | A- | 1 | 3.7 | Honors | 0.5 | 4.2 |
| 3 | AP U.S. History | B+ | 1 | 3.3 | AP | 1.0 | 4.3 |
| 4 | Algebra II | B | 1 | 3.0 | Regular | 0 | 3.0 |
| 5 | Chemistry | A | 1 | 4.0 | Regular | 0 | 4.0 |
| 6 | Health | A | 0.5 | 4.0 | Regular | 0 | 4.0 |
| 7 | AP Biology | C+ | 1 | 2.3 | AP | 1.0 | 3.3 |
You type A, B, C and E. D, F and G are formulas.
Step 3: Convert letter grades to points
In D2, then fill down to D7. Pick whichever version your spreadsheet supports.
XLOOKUP (Microsoft 365, Excel 2021 and later, Google Sheets):
=XLOOKUP(B2, Scale!$A$2:$A$13, Scale!$B$2:$B$13)VLOOKUP (every version):
=VLOOKUP(B2, Scale!$A$2:$B$13, 2, FALSE)INDEX and MATCH (every version):
=INDEX(Scale!$B$2:$B$13, MATCH(B2, Scale!$A$2:$A$13, 0))IFS, with no lookup table at all (Excel 2019 and later, Google Sheets):
=IFS(B2="A+",4, B2="A",4, B2="A-",3.7, B2="B+",3.3, B2="B",3, B2="B-",2.7, B2="C+",2.3, B2="C",2, B2="C-",1.7, B2="D+",1.3, B2="D",1, B2="F",0)IFS works, but the scale is buried in every cell of column D, so changing one value means editing all of them.
What goes wrong: a typo like “B +” or a trailing space returns #N/A. That’s actually what you want, because an error is visible. Don’t hide it with XLOOKUP’s optional “not found” argument set to text: Microsoft’s SUMPRODUCT documentation says non-numeric entries are treated as zeros, so a typo would quietly count as an F.
The FALSE in VLOOKUP and the 0 in MATCH force an exact match. Leave them out and both functions switch to approximate matching, which gives wrong answers on an unsorted letter list.
Step 4: Calculate GPA with SUMPRODUCT
In any empty cell, for example B9:
=SUMPRODUCT(C2:C7, D2:D7)/SUM(C2:C7)SUMPRODUCT multiplies each class’s credits by its points and adds the results (Google’s page for Sheets, Microsoft’s linked above). For the sample sheet that’s 3.7 + 3.3 + 3.0 + 4.0 + 2.0 + 2.3 = 18.3 quality points, divided by 5.5 credits = 3.327, or 3.33 rounded.
Compare =AVERAGE(D2:D7), which returns 3.38. AVERAGE treats the half-credit Health class like a full-year class. It’s an easy mistake to miss, and with college credits (a 4-credit lab next to a 1-credit seminar) the gap gets bigger. If you want to see the method by hand first, how to calculate GPA walks through it.
To match how your school reports it, wrap the formula. ROUND(..., 2) rounds; TRUNC(..., 2) cuts off, which is what some registrars do:
=ROUND(SUMPRODUCT(C2:C7, D2:D7)/SUM(C2:C7), 2)Both ranges have to be the same size. Microsoft’s page says SUMPRODUCT returns #VALUE! if the arrays have different dimensions.
A sheet you can keep adding to. Point the ranges at row 30 and change D2 so an empty row gives 0 instead of #N/A:
=IF(B2="", 0, XLOOKUP(B2, Scale!$A$2:$A$13, Scale!$B$2:$B$13))Then count credits only where a grade exists, so a class you’ve entered but haven’t finished doesn’t drag the GPA down:
=SUMPRODUCT(C2:C30, D2:D30)/SUMIFS(C2:C30, B2:B30, "<>")Step 5: Weighted GPA with a level bonus
F2 looks up the bonus for the class level:
=XLOOKUP(E2, Scale!$D$2:$D$4, Scale!$E$2:$E$4)G2 adds it, except on an F, which usually earns no bonus:
=IF(D2=0, 0, D2+F2)Fill both down, then in B10:
=SUMPRODUCT(C2:C7, G2:G7)/SUM(C2:C7)The sample comes to 20.8 ÷ 5.5 = 3.78 weighted, against 3.33 unweighted. If you’d rather skip the helper columns, this single formula gives the same 3.78 (the (D2:D7>0) part is the no-bonus-on-F rule):
=SUMPRODUCT(C2:C7, D2:D7 + F2:F7*(D2:D7>0))/SUM(C2:C7)Districts set their own weights. If yours gives IB a bonus or uses different values, edit the Scale table and extend the range to $D$2:$D$5. The weighted GPA calculator uses the same +0.5 / +1.0 default, so it’s a quick way to check the sheet.
Step 6: Cumulative GPA across terms
On a third tab, Terms, one row per semester:
| Row | A: Term | B: Term GPA | C: GPA credits | D: Cumulative |
|---|---|---|---|---|
| 2 | Fall 2025 | 3.20 | 15 | 3.20 |
| 3 | Spring 2026 | 3.60 | 16 | 3.41 |
| 4 | Fall 2026 | 2.90 | 14 | 3.25 |
D2, filled down, shows the cumulative GPA up to each term:
=SUMPRODUCT($B$2:B2, $C$2:C2)/SUM($C$2:C2)Row 4 works out to (48.0 + 57.6 + 40.6) ÷ 45 = 146.2 ÷ 45 = 3.25. A plain =AVERAGE(B2:B4) says 3.23, because it ignores that the 2.90 term had fewer credits.
Want to know what you need next term? Put the target in G1 (say 3.40) and next term’s credits in G2 (15):
=(G1*(SUM(C2:C4)+G2) - SUMPRODUCT(B2:B4, C2:C4))/G2That returns 3.85. Anything above 4.0 means the target can’t be reached in one term.
What goes wrong: transcripts round term GPAs, so the result can be a hundredth off; if yours lists quality points, divide their sum by total credits instead. And use GPA credits, not earned credits, since pass/fail classes add earned credits but no grade. The cumulative GPA calculator does the same math if you want a second opinion.
Step 7: Weighted grades and percent to letter
The same SUMPRODUCT pattern gives a class grade from weighted categories. On a Grades tab:
| Row | A: Category | B: Weight | C: Score |
|---|---|---|---|
| 2 | Homework | 15% | 92 |
| 3 | Quizzes | 25% | 85 |
| 4 | Tests | 40% | 78 |
| 5 | Final exam | 20% |
The textbook formula is =SUMPRODUCT(B2:B5, C2:C5)/SUM(B2:B5). Here it returns 66.25, because the empty final counts as zero. To grade only what’s been graded, divide by the weights that have a score:
=SUMPRODUCT(B2:B5, C2:C5)/SUMIFS(B2:B5, C2:C5, "<>")That’s 66.25 ÷ 0.80 = 82.8%. The grade calculator handles empty categories the same way, averaging only the ones that have a score.
To turn that percent into a letter, use an approximate match against the sorted table in Scale!G:H. With the category formula in B6, put this in B7:
=VLOOKUP(ROUND(B6, 0), Scale!$G$2:$H$13, 2, TRUE)Or with XLOOKUP, where match mode -1 means “exact match, or the next smaller value”:
=XLOOKUP(ROUND(B6, 0), Scale!$G$2:$G$13, Scale!$H$2:$H$13, "", -1)Both find the largest cutoff at or below the score. With ROUND, 82.8 becomes 83 and returns B. Without it, 82.8 falls under the 80 cutoff and returns B-. Which one is right depends on whether your teacher rounds, so match the formula to the syllabus.
Approximate VLOOKUP needs the first column sorted ascending. Google’s VLOOKUP page warns that an unsorted range can return a wrong value, and Microsoft’s VLOOKUP page says the same about the first column. XLOOKUP’s -1 mode checks the whole list, so order only matters if you also switch it to a binary search mode.
Excel vs. Google Sheets: what’s different
Every formula above runs unchanged in both. The differences worth knowing:
- XLOOKUP isn’t in older Excel. Microsoft’s XLOOKUP page notes it isn’t available in Excel 2016 or Excel 2019. Use the VLOOKUP or INDEX/MATCH versions there. Google Sheets has XLOOKUP with the same argument order and match modes.
- VLOOKUP’s last argument defaults to approximate in both programs. Always type FALSE for the letter lookup.
- Separators follow your locale. If your spreadsheet uses a comma as the decimal mark, formulas use semicolons between arguments:
=SUMPRODUCT(C2:C7; D2:D7)/SUM(C2:C7).
Check the sheet against a calculator
A spreadsheet is only as good as its ranges: point one formula at D2:D6 instead of D2:D7 and a class silently drops out. Once your sheet is built, enter the same classes in the GPA calculator. If the numbers match, the sheet is right.