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 😉