How to Make a GPA Calculator in Excel

A GPA calculator in Excel needs three things: a list of courses with credits and letter grades, a grade scale, and one formula that weights each grade by its credits. GPA (grade point average) is a weighted average, so a 4-credit course counts more than a 2-credit one. This guide builds the calculator with XLOOKUP for the grade points and SUMPRODUCT for the GPA, then shows how to combine terms into a cumulative GPA. It works for students, parents and school staff.

How GPA is calculated#

Each letter grade is worth a number of grade points, and each course is worth a number of credits. Multiply them to get quality points for each course, add up the quality points, and divide by the total credits:

GPA = (credits × grade points, added up) / total credits

Many schools use a 4.0 scale. The scale below is an example; the points for plus and minus grades vary, so always use the one your school publishes.

Some schools also report a weighted GPA, which adds extra points for harder courses, such as honours classes. The rules differ from school to school. If yours uses one, add a column for the bonus points and include it in the points before you multiply by credits; the rest of the calculator stays the same. A GPA without bonus points is called unweighted, and it is what this guide calculates.

Step 1: Set up the course list and grade scale#

Put one course per row, and keep the grade scale in its own two columns to the side. That way, if the scale changes, you edit it once.

Type the scale into G1:H11: the heading Grade in G1 and Points in H1, then the ten grades in G2:G11 and their points in H2:H11.

Step 2: Look up grade points with XLOOKUP#

XLOOKUP finds a value in one column and returns the matching value from another. In D2:

=XLOOKUP(C2,$G$2:$G$11,$H$2:$H$11)

It finds B+ in the grade list and returns 3.3. Fill it down to D6 (Windows: Ctrl+D; Mac: ⌘D). The $ signs keep the scale fixed while C2 changes to C3, C4 and so on. XLOOKUP looks for an exact match by default, so "A-" won't be mistaken for "A".

XLOOKUP needs Excel 2021 or Microsoft 365. In older versions, use:

=VLOOKUP(C2,$G$2:$H$11,2,FALSE)

The XLOOKUP function guide covers the optional arguments, and the article on how to use XLOOKUP has more worked examples. If you start from percentage marks instead of letters, the guide to the Excel grade formula turns marks into letters first.

Step 3: Calculate the GPA with SUMPRODUCT#

SUMPRODUCT multiplies two ranges row by row and adds the results. That is exactly the "credits × points, added up" part of the GPA. In F2:

=SUMPRODUCT(B2:B6,D2:D6)/SUM(B2:B6)

The GPA is 56.3 ÷ 16 = 3.51875. Most schools report two decimals, so wrap it in ROUND:

=ROUND(SUMPRODUCT(B2:B6,D2:D6)/SUM(B2:B6),2)

The result is 3.52. The SUMPRODUCT function guide explains the syntax, and the SUMPRODUCT article shows other uses, such as conditional totals.

Why a simple AVERAGE gives the wrong GPA#

=AVERAGE(D2:D6) returns 3.6, not 3.52. AVERAGE gives every course the same weight, so the 2-credit Art class counts as much as the 4-credit Calculus class. Because Art was an A and Calculus a B+, the plain average comes out too high.

This is the same idea as any weighted average: a grade, price or score that covers more units should count more. The guide to weighted averages in Excel explains it with other examples.

If you'd rather not use SUMPRODUCT, add a helper column E with =B2*D2, fill it down, and divide: =SUM(E2:E6)/SUM(B2:B6). It gives the same 3.51875, and the SUM lesson covers adding up a range.

How to calculate a cumulative GPA#

A cumulative GPA covers every term so far. Add the quality points from all terms and divide by all the credits. Don't average the term GPAs, unless every term had exactly the same credits.

With credits in B2:B3 and quality points in C2:C3:

=ROUND(SUM(C2:C3)/SUM(B2:B3),2)

The cumulative GPA is 92.3 ÷ 28 = 3.30. Averaging the two term GPAs would give 3.26, which is wrong because Spring had more credits.

Make the GPA calculator easier to use#

A few small touches make the sheet reliable for a whole year:

• Drop-down grades. Use Data > Data Validation > List with the scale range as the source, so nobody types "B +" with a space. A typo makes XLOOKUP return #N/A.
• Room to grow. Point the formulas at more rows than you need, such as B2:B20. Empty rows add nothing to SUMPRODUCT or SUM. If you fill the points column into empty rows too, use =IF(C2="","",XLOOKUP(C2,$G$2:$G$11,$H$2:$H$11)) so blank grades don't return #N/A.
• Pass/fail courses. These usually don't count towards GPA. Leave them off the list, or exclude their credits with SUMIF.
• Show the inputs. Keep the scale visible so anyone checking the GPA can see the points used.

Teachers who track many students can build on the same ideas in a gradebook in Excel.

Practise it#

A GPA calculator uses two everyday skills: looking up a value and adding up a range. Practise XLOOKUP in a live spreadsheet to turn grades into points, then practise the SUM function to total credits and quality points. Each lesson checks your formula instantly, and the exercise on this page calculates a real GPA.