Learn to to restructure and summarize your data using Excel's PivotTable tool!

Pivot Tables

Designing a pivot table by hand is one thing. Calculating the values is a task for Excel! Excel's `PivotTable` tool takes our pivot table design and dynamically creates the corresponding pivot table within a spreadsheet.

We've provided a slideshow in the Learning Environment that illustrates the process of creating a pivot table in Excel. 

A quick note:  you might notice that when Excel automatically references a range in the PivotTable dialogues, it will put the sheet name followed by `!` before the range. This sheet name syntax is for referencing ranges that may be on a different sheet than the original table. If you are typing in a range on the sheet you have currently selected, you can type in ranges as usual without the sheet name.

When you're ready to try creating pivot tables yourself, move on to the next set of instructions!

Pivot Tables in Excel

Unlike formulas, pivot tables in Excel do not update automatically if the original table changes. There are two methods for updating a pivot table manually, depending on how the original table has changed:

1. **Refresh**: if the size and location of the source dataset have not changed, select  `Refresh` from the `PivotTable Analyze` tab.
2. **Change data source**: if the size and/or location of the source dataset have changed, select `Change Data Source` from the `PivotTable Analyze` tab and select the new source range.

Note that the `PivotTable Analyze` tab will only appear if you have selected a cell within the pivot table.

We've placed a slideshow illustrating both cases in the Learning Environment. When you're ready to try this yourself, get started on the next set of instructions!


Updating Pivot Tables

Congratulations on finishing this lesson! You've taken your Excel skills to the next level with the PivotTable tool, and now have everything you need to start exploring datasets and answering data questions. You've learned how to

- Translate a data question into pivot table settings
- Implement those settings in Excel's PivotTable tool
- Update pivot tables with new and corrected data

An Excel interface surrounded by illustrative tables of data, charts, and formulas.

Let's Review

Two tables. The first table has 5 columns. 
Column 1 is blank.
Column 2 has the header "Month" and is highlighted with the label "Rows"
Column three has the header "Losses" and is highlighted with the label "Values"
Column 4 is blank
Column 5 has the header "State" and is highlighted with the label "Columns".
The table has two rows with data. Both rows have month "February" and state "FL". One row has the losses of 10,000 and the other has losses of 25,000.

The second table has rows labeled with months January through April and columns labeled with states AZ, FL, and TX. The cell in the row February and column FL has the sum of the two losses from the first table 10,000+25,000.

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.

|   | Year | Month    | Losses ($) | Length (mi) | State |
|---|------|----------|--------|--------|-------|
| 1 | 2020 | March    | 5,000  | .4     | TX    |
| 2 | 2020 | February | 10,000 | 1.5    | FL    |
| 3 | 2020 | January      | 50,000 | .8     | AZ    |
| 4 | 2020 | February | 25,000 | .9     | FL    |
| 5 | 2020 | January      | 30,000 | 1      | AZ    |
| 6 | 2020 | January      | 1,000 | 1.2      | TX    |

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.

|          | AZ | FL | TX |
|----------|----|----|----|
| |    |    |    |
|    |    |    |    |
|     |    |    |    |

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.

|          | AZ | FL | TX |
|----------|----|----|----|
| January |    |    |    |
| February    |    |    |    |
| March      |    |    |    |


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

|          | AZ | FL | TX |
|----------|----|----|----|
| January | 80,000   |    |    |
| February    |    |    |    |
| March      |    |    |    |

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 &mdash; do you agree with the rest of our calculations?

|          | AZ | FL | TX |
|----------|----|----|----|
| January | 80,000   |    | 1,000   |
| February    |    |  35,000  |    |
| March      |    |   |  5,000  |

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)


What is a Pivot Table

Formulas, filtering, and sorting are powerful tools! But combining them to answer complex data questions can be tricky. Excel solves this problem for many common data questions through the `PivotTable` tool.

In this lesson you'll learn how to take a question about data and

- Determine if a pivot table is appropriate
- Translate the question into pivot table settings
- Create the pivot table in Excel
- Update the pivot table with new data


