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 😉