Introduction to Pivot Tables

If you’re looking for some of the flexible query power of a database but don’t want to mess with relational database design, pivot tables may be just the technology you need. Chances are you’re already using one of the popular spreadsheet packages that include this technology, such as Microsoft Excel or the free Google Spreadsheets package.

What is a Pivot Table?

Pivot tables allow you to drag-and-drop columns and rows to arrange data into groups and subgroups with the click of a mouse. They also provide easy access to aggregate functions that allow you to summarize data using totals, averages, minimums, maximums and other functions.

Pivot tables have the power to transform a traditional flat spreadsheet file into a fairly powerful database without requiring database software or expertise. In fact, you can easily get up and running with pivot tables in just a few minutes with the spreadsheet software that’s probably already present on your computer.

Common Uses of Pivot Tables

One of the most common uses of pivot tables is to quickly summarize large data files. You can use them to answer questions like “How many students were enrolled in each course that we offer over the past five years?” and “What product inventory levels have dropped below their historic averages?”

Pivot tables are commonly used in the analysis of moderate-to-large size datasets. If you have more than a few dozen rows in a spreadsheet and find yourself trying to answer questions about the data using complicated COUNT() functions, you might be better off investing a few minutes in building a pivot table.

For example, the pivot table shown in the figure above summarizes a 100-row spreadsheet of student enrollment information. The original data source contains one row for each student enrollment in a course. It includes the semester and year of enrollment, the name of the course and the name of the instructor. The particular pivot table that I built shows enrollment trends during the fall and spring semesters of various years with separate columns for each course.

Granted, I’m experienced with pivot tables, but this took me only about five minutes to build. I could just as easily completely rearrange the data summary to show all sorts of different information. By dragging and dropping data to different columns and rows, I could modify the pivot table to answer any of the following questions:

• How many students did each instructor teach each year?
• How many different instructors taught each course?
• What is the average class size each semester?

In addition, pivot tables can be filtered so that certain records do not appear. For example, if I were only interested in the courses taken by students in their senior year, I could add a filter to the entire table that excludes students whose class year is not “senior”.

Pivot Tables in Microsoft Excel

Depending upon the software you use to generate your pivot tables, you’ll have a variety of functions at your disposal.

The most powerful and flexible pivot table functionality appears in Microsoft Excel. There’s good reason that it’s the most popular spreadsheet package in use today. The pivot table functionality in Excel is both intuitive and powerful, offering access to a number of aggregate functions and an especially powerful filtering mechanism.

Pivot Tables in Google Spreadsheets

If you’re looking for a free alternative, you can also use pivot tables in Google Spreadsheets. In fact, this technology has matured so much since its initial release in early 2011 that it rivals that in Microsoft Excel. With a few exceptions (such as the ability to include calculated columns), Google Spreadsheets can do most anything that Excel can do. My only hesitation is the slow response that comes from an online application. That said, unless you’re doing some powerful number crunching, you may not need the full Microsoft Excel experience. For more, read Creating Pivot Tables in Google Spreadsheets.