How to Calculate GPA in Excel (with Easy Steps)

Method 1 – Creating Reference Table to Calculate GPA

Create a table with 3 columns and 6 rows.
Name the columns as Percentage, Grade, and Grade Point respectively.

Enter the Percentage ranges, Grades, and Grade Points as shown in the following picture.

Method 2 – Using VLOOKUP Function to Calculate GPA in Excel

Enter the following formula in cell D5.

=VLOOKUP(C5,$B$14:$D$18,2)

Cell C5 refers to the obtained Percentage of History exam, the range $B$14:$D$18 represents the Reference Table that we are using to calculate GPA.

Formula Breakdown

=VLOOKUP(C5,$B$14:$D$18,2) → It looks for a given value in the leftmost column of a given table and then returns a value in the same row from a specified column.
- C5 → lookup_value argument.
- $B$14:$D$18 → table_array argument
- 2 → col_index_num argument
  - Output → B

Press ENTER.

You will see the Grade of History as marked in the image given below.

Use the AutoFill feature of Excel to get the rest of the Grades for other Subjects as shown in the following picture.

Enter the formula given below in cell E5.

=VLOOKUP(C5,$B$14:$D$18,3)

Formula Breakdown

=VLOOKUP(C5,$B$14:$D$18,2) → It looks for a given value in the leftmost column of a given table and then returns a value in the same row from a specified column.
- C5 → lookup_value argument.
- $B$14:$D$18 → table_array argument
- 3 → col_index_num argument
  - Output → 3