Skip to content
  • AICPA & CIMA:
  • Home
  • Engage 365 Communities
  • CPE & Learning
  • My Account
Journal of Accountancy
  • TECH & AI
    • All articles
    • Artificial Intelligence (AI)
    • Microsoft Excel
    • Information Security & Privacy

    Latest Stories

    • Tax practitioners need updated AI guidance, council tells IRS
    • What Binance’s $6.3B crypto hack means for CPAs
    • Why AI needs to show its work

  • TAX
    • All articles
    • Corporations
    • Employee benefits
    • Individuals
    • IRS procedure

    Latest Stories

    • Tax practitioners need updated AI guidance, council tells IRS
    • IRS finalizes retirement plan auto-enrollment rules
    • Inflation adjustments to retirement account limits issued for 2027
  • PFP
  • PRACTICE MANAGEMENT
    • All articles
    • Diversity, equity & inclusion
    • Human capital
    • Firm operations
    • Practice growth & client service

    Latest Stories

    • Tax practitioners need updated AI guidance, council tells IRS
    • FASAB seeks feedback on its work plan, disposal liabilities guidance
    • IRS finalizes retirement plan auto-enrollment rules
  • FINANCIAL REPORTING
    • All articles
    • FASB reporting
    • IFRS
    • Private company reporting
    • SEC compliance and reporting

    Latest Stories

    • SEC proposes overhaul of executive compensation disclosures
    • SEC proposal aims to clarify securities rules for crypto assets
    • SEC eyes e-delivery as the default over paper
  • AUDIT
    • All articles
    • Attestation
    • Audit
    • Compilation and review
    • Peer review
    • Quality Management

    Latest Stories

    • AICPA publishes proposed updates to SOC 2 description criteria
    • PCAOB seeks feedback on comfort-letter proposal, restructures enforcement
    • AI raises the stakes for auditor judgment
  • MANAGEMENT ACCOUNTING
    • All articles
    • Business planning
    • Human resources
    • Risk management
    • Strategy

    Latest Stories

    • 2027 CFO forecast: Mergers and acquisitions on the rise
    • U.S. employee engagement falls below 30% for first time in 15 years
    • Risk response often doesn’t match the threat or the opportunity
  • Home
  • News
  • Magazine
  • Podcast
  • Topics
Advertisement
  1. newsletter
  2. Extra Credit
Extra Credit Cover

Use Excel PivotTables to quickly analyze grades

By Wendy Tietz, CPA, Ph.D.; Jennifer Cainas, CPA, DBA; and Tracie Miller-Nobles, CPA
May 12, 2020

Please note: This item is from our archives and was published in 2020. It is provided for historical reference. The content may be out of date and links may no longer function.

Related

April 14, 2020

Use Excel’s AutoSum button to add up rows and columns with just one click

March 10, 2020

Sort by color to group highlighted cells in Excel

TOPICS

  • Accounting Education

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):

student-listings


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).

pivottable-grades


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.

Advertisement

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.

Advertisement

latest news

November 19, 2026

Tax practitioners need updated AI guidance, council tells IRS

November 18, 2026

FASAB seeks feedback on its work plan, disposal liabilities guidance

November 17, 2026

IRS finalizes retirement plan auto-enrollment rules

November 17, 2026

AICPA publishes proposed updates to SOC 2 description criteria

November 16, 2026

AICPA and CIMA to showcase Rise2040 at World Congress of Accountants

Advertisement

Most Read

IRS announces inflation adjustments for tax year 2027
Inflation adjustments to retirement account limits issued for 2027
How CPAs are putting AI to work in audits
AICPA partners on new offering that assures AI systems and agents
Scary stories of professional liability
Advertisement

Podcast

November 19, 2026

Global tax advocacy, local impact: What the EU omnibus means for U.S. CPAs

November 12, 2026

5 E’s for leading through change in the accounting profession

November 5, 2026

Building a career defined by impact, not accolades or titles

Features

AI raises the stakes for auditor judgment
AI raises the stakes for auditor judgment

AI raises the stakes for auditor judgment

How CPAs are putting AI to work in audits
How CPAs are putting AI to work in audits

How CPAs are putting AI to work in audits

How to craft the CPA career you want
How to craft the CPA career you want

How to craft the CPA career you want

Not-for-profit pay faces broader tax risk
Not-for-profit pay faces broader tax risk

Not-for-profit pay faces broader tax risk

Why AI needs to show its work
Accordance

Why AI needs to show its work

How firms make AI adoption work for them
How firms make AI adoption work for them

How firms make AI adoption work for them

Sales tax doesn’t have to be this hard
Sales tax doesn’t have to be this hard

Sales tax doesn’t have to be this hard

SPONSORED REPORT

Get your clients ready for tax season

Help clients avoid surprises with year-end planning strategies for managing taxable income, maximizing deductions, and adjusting to OBBBA changes affecting individuals and businesses.

From The Tax Adviser

October 31, 2026

AI risks to the Sec. 7525 practitioner privilege

September 30, 2026

Navigating the QSBS rules in pass-through structures

September 30, 2026

Sec. 338(h)(10) elections in business acquisitions

August 31, 2026

2026 tax software survey

MAGAZINE

November 2026

November 2026

November 2026
October 2026

October 2026

October 2026
September 2026

September 2026

September 2026
August 2026

August 2026

August 2026
July 2026

July 2026

July 2026
June 2026

June 2026

June 2026
May 2026

May 2026

May 2026
April 2026

April 2026

April 2026
March 2026

March 2026

March 2026
February 2026

February 2026

February 2026
January 2026

January 2026

January 2026
December 2025

December 2025

December 2025
view all

View All

http://JofA_Default_Mag_cover_small_official_blue

PUSH NOTIFICATIONS

Learn about important news

This quick guide walks you through the process of enabling and troubleshooting push notifications from the JofA on your computer or phone.

CPA LETTER DAILY EMAIL

CPA Letter Logo

Subscribe to the daily CPA Letter

Stay on top of the biggest news affecting the profession every business day. Follow this link to your marketing preferences on aicpa-cima.com to subscribe. If you don't already have an aicpa-cima.com account, create one for free and then navigate to your marketing preferences.

Connect

  • X Logo JofA on X
  • facebook JofA on Facebook

HOME

  • News
  • Monthly issues
  • Podcast
  • A&A Focus
  • PFP Digest
  • Academic Update
  • Topics
  • RSS feed rss feed
  • Site map

ABOUT

  • Contact us
  • Advertise
  • Submit an article
  • Editorial calendar
  • Privacy policy
  • Terms & conditions

SUBSCRIBE

  • Academic Update
  • CPE Express

AICPA & CIMA SITES

  • AICPA-CIMA.com
  • Global Engagement Center
  • Financial Management (FM)
  • The Tax Adviser
  • AICPA Insights
  • Global Career Hub
AICPA & CIMA

© 2026 Association of International Certified Professional Accountants. All rights reserved.

Reliable. Resourceful. Respected.