# Speed Challenge Day 3

## PivotTables

### Description

PivotTables are a way to summarize data. Essentially, a PivotTable is a summary report.

### Holly's Immeasurable Efficiencies

PivotTables are an incredible tool. After learning about PivotTables, my student Holly said she uses them daily and "they have created efficiencies for us that I do not think we could measure." Learn more here.

### File

If you'd like to give it a try after watching the video, download the Excel file: ChallengePivotTables.xlsx.

### To Insert a PivotTable

• Windows: Insert > PivotTable
• Mac: Insert > PivotTable

### Try It

#### Exercise 1

• Head to the Data worksheet, and select any cell in the data Table.
• Insert > PivotTable.
• In the resulting dialog, confirm the Table/Range is Table1.
• In the 'Choose where you want the PivotTable to be placed' option, select Existing Worksheet.
• Then navigate to the PivotTable worksheet, and select a cell to represent the upper-left corner of the PivotTable. For example, select Cell B16.
• Click OK to close the dialog and insert the PivotTable.
• Inspect the new PivotTable Fields panel on the right side of the worksheet. Define the structure of your report by dragging fields into the layout areas.
• Drag the Acct field into the Rows area.
• Drag the Amount field into the Values area.
• Drag the Period field into the Columns area.
• Drag the Dept field into the Filters area.

#### Exercise 2

• To format the values, right-click any numeric cell in the PivotTable.
• Select Number Format.
• In the resulting dialog box, select Number, 0 Decimals, and use the 1000 Separator.
• Click OK to apply the formatting to the report.

#### Exercise 3

• Add a new transaction to the Data table.
• Navigate to the Data worksheet.
• Type a new transaction for Finance, Meals, Mar, 1000.
• Navigate back to the PivotTable.
• Note the PivotTable did not automatically refresh itself to include the new value.
• Right-click any cell in the PivotTable and click Refresh.
• Observe that the PivotTable has updated to include the new transaction.
• You can add additional transactions as well if desired. Feel free to add new periods, departments, or accounts.

### Conclusion

A PivotTable is a dynamic summary report. It is great for recurring-use workbooks because once you've defined the structure of the report, it is easy to update next period.

And ... I have a feeling that we'll be seeing PivotTables again during the challenge ðŸ˜‰