How do I calculate a departmental GPA?

Calculating the departmental or major GPA can be broken down into three overall steps.

• Step 1: Download student grade data into a spreadsheet.
• Step 2: Summarize the data and calculate the GPA using a pivot table.
• Step 3: Limit the GPA calculation to the subject area or classes you want.

Step 1: Download student grade data into a spreadsheet.

Learn more: How do I export student grade data in a spreadsheet?

Step 2: Summarize the data and calculate the GPA using a pivot table in MS-Excel

1. Open the grade data you've just downloaded using Microsoft Excel.
2. Click the Insert menu, choose PivotTable. A pivot table wizard window appears, and Excel selects a portion of the datasheet for pivot table analysis. Select the radio button option that indicates where you'd like your pivot table to be placed (such as in a new worksheet) and click the OK button.
3. Drag and drop the little boxes on the PivotTable Field List border in the upper right into the filter areas on the lower right so that the border looks like the picture below, then click OK and click Finish.   
4. Next, you'll insert the calculated field that will divide Grd Points by Attempted for Grade Credit and yield the GPA.
   1. Click inside cell C4 to put the focus there.
   2. From the main menu, choose Analyze > Fields, Items, & Sets. Click Insert Calculated Field.
   3. Delete the contents of the Formula box, and leave your cursor in that box.
   4. From the Fields list, click to highlight Grd Points, then click Insert Field.
   5. Type a slash (/) after Grd Points in the "Formula" box.
   6. From the Fields list, click to highlight Attempted for Grade Credit, then click Insert Field.
   7. Click the Add button at the top and then click the OK button at the bottom.
5. Next, click inside the first cell that contains "Sum of Field 1" and type "GPA." Press the tab key to move out of the field and "GPA" replace all the "Sum of Field 1."

Step 3: Limit the GPA calculation to the subject area or classes you want.

Here's the fun part - use the pivot table's features to limit which classes are used to calculate the GPA. Here are some sample scenarios that illustrate how you could use this spreadsheet.

Scenario 1: If all the classes in only one subject area count, click the subject drop-down list in cell A1 at the top of the spreadsheet and choose the desired subject area code ("SP"). The pivot table filters out all other subject areas and thus only calculates the GPA for classes in Spanish.

Scenario 2: Only certain classes count and the subject areas vary: click the drop-down list for Row Labels and look at the the Subject Area & Catalog Number column items. Uncheck the boxes next to the classes that don't count toward the special GPA. Then click OK. For example, if SP 102 and 103 did not count towards this special GPA, you can only check the boxes next to the classes that do count thereby calculating a GPA based on a set of classes that you determine.