Pivot Tables

Key Terms:

• Value: Data that populates a cell once inside a pivot table

• Rows, columns, and filters remain the same

How to construct a pivot table

Pivot tables are operations that can perform some level of aggregation on an existing table of data. Here, it is particularly useful to illustrate the uses of a pivot table with an example, since they vary widely depending on your specific desired application. As a quick reminder, rows refer to horizontal groups of cells and columns refer to vertical groups of cells. Filters are a way to limit which data actually appears on the spreadsheet. As a final note before the example, it is also useful to know the GETPIVOTDATA function, which allows you to retrieve data from a pivot table based on specified labels.

To begin with an example, let’s take a look at this table, which has a couple columns: division, subdivision, product number, units sold, and the price per unit.

While this table is fairly informative on its own, we’d like to put together a report for each division and subdivision to more easily compare their number of units sold. We can accomplish this by using a pivot table. We enter into the Data menu and select Pivot Table while highlighting our current table. Let’s move this pivot table to a new sheet so as not to disturb the original table.

As you can see, the pivot table is totally blank. The software needs us to input what we would like to fill the three areas: rows, columns, and values. To start, let’s populate “rows” with divisions, since we’d like to easily compare the east and west divisions of the company.

Now let’s add another category for “rows” to break each division into its constituent subdivisions.

We’ve successfully grouped our data into divisions and subdivisions. Notice how this table is already easier to interpret and compare between east and west division. However, we still need to fill in what we want in the columns and in the cells themselves. Let’s add the product numbers as columns to prepare for filling in the values with the sales numbers.

Almost there! The last step is to fill in the values with the amount sold.

Done! The pivot table is now complete. We can easily compare the east division’s sales numbers to the west division, and see how each subdivision contributed to the division’s sales. We can even see that product 1 was more popular in both divisions than product 2! What’s more, the pivot table is nicely formatted and should be easy to understand for future users.

This is just a simple example of what you can do with pivot tables. A great deal of possibilities arise from simply reordering rows and columns and choosing different values! Additionally, it’s possible to use the Calculated Field feature under values to populate cells with the result of applying a custom formula. It’s up to you to experiment with various sets of data and various customizations that might be more relevant to your specific dataset.