Pivot tables won’t help you get your furniture through a door (unfortunately). They will help you group and summarize your data. Let’s look at an example of how powerful this can be.

Suppose we’re trying to calculate the total dollars lost to tornados in each state by month. Let’s use the small example table below.

We could answer this question by hand in three steps:

1. Group together all rows with the same state and month.
2. Collect the Losses values for all the rows in each group.
3. Sum the Losses for each group to report the total losses by group.

A pivot table is a separate table collecting the results of this process. That might sound a bit abstract, so let’s go through this step-by-step.

We start by creating a table of all the possible Month and State groups.

First, we use the values of the State column as column headers for the pivot table. Note that we don’t repeat values. Even though AZ appears twice in the state column, it only appears once in the headers of our new table.

Second, we use the values of the Month column to label the rows of the new table. Once again, each month will only appear once.

The cells of this table correspond to the groups we need to form to answer the original question. Now, we need to gather and summarize the values in each group to fill in the corresponding cell.

For example, the upper-left cell of the new table is in the row January and the column AZ. When we look back at the original table, there are two rows in this group. To report the total losses, we collect the Losses values for those rows and add them up: 50,000+30,000 = 80,000.

If we move one cell down, we’ll be in the row February and column AZ. There are no rows of the original table corresponding to this group. We leave this cell blank, to tell someone reading the table that there were no rows of data in this group.

Here’s the final pivot table — do you agree with the rest of our calculations?

This process is the same for any pivot table. Starting with a table of data, you pick

• column labels: a column or columns of the original table to serve as column labels (or headers) for the pivot table
• row labels: a column or columns of the original table to serve as row labels for the pivot table
• cell values: a column or columns of the original table to provide the values for the cells of the pivot table
• summary method: a method for taking multiple values corresponding to a single group and producing one value for the pivot table

In our initial example, we picked

• column labels: State
• row labels: Month
• cell values: Losses
• summary method: sum (or total)