Use Excel PivotTables to quickly analyze grades

Let’s say you have an Excel workbook that contains student grades for multiple sections of a class that you are coordinating. You would like to be able to summarize the grade data by different criteria. For example, you would like to see exam averages by section and by instructor. You could sort by section and then use AutoSum to calculate the average for each section, and then sort by instructor and do the same. Creating a PivotTable, though, would give you an easier and faster way to view this data.

Here’s how to create one. For example, say you have a workbook that contains information about 286 students and looks like this (partial listing):

There are three main steps to follow:

Step 1: Insert the PivotTable. Click on cell A1. In the Insert Ribbon in Excel, click on PivotTable, and then click OK (accept the defaults). In the PivotTable Fields panel in the new worksheet, drag Instructor down to the Rows box and then drag Section # down to right below Instructor in the Rows box. Drag Exam 1 down to the Values box. (See this short tutorial video for an overview of how to insert a pivot table into a workbook.)

Step 2: Change the summary calculations to averages. In the PivotTable worksheet, click the dropdown arrow next to “Sum of Exam 1” in the Values box and select Value Field Settings. Select Average and click OK. (See this short tutorial video for Windows users or this tutorial video for Mac users for an overview of how to change the summary calculation in a pivot table to an average.)

Step 3: Format the PivotTable numbers. Select the data under the heading Average of Exam 1 in the PivotTable, right-click, and select Value Field Settings. Then click on Number Format (it will be at the lower left side of the window that opens up) and select Number, change the number of decimal places to one, click OK, and click OK again. You will see a PivotTable like the one below. It will tell you the average grades on Exam 1 for each instructor and each section. (See this video for Windows users or this tutorial video for Mac users for an overview of how to format numbers in a PivotTable).

PivotTables offer an endless number of possibilities. They allow you to analyze data faster than you could if you were to sort it and insert formulas. Once you create one or two pivot tables and get the hang of it, they become a real time saver.

Wendy Tietz, CPA, CGMA, Ph.D., is a professor of accounting at Kent State University in Kent. Ohio; Jennifer Cainas, CPA, DBA, is an instructor of accountancy at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. See their site AccountingIsAnalytics.com for resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact senior editor Courtney Vien at Courtney.Vien@aicpa-cima.com.