Excel: PivotTables in Depth

Learn how to summarize and analyze data using the most powerful and most overlooked feature in Excel: PivotTables.

Many Excel users have a fear of PivotTables. This course provides a thorough introduction to this powerful and overlooked feature and, for more advanced students, shows how to use macros and the data model to extend PivotTables even further. Instructor Curt Frye demystifies PivotTables, using real-world examples that empower students to make the most of their time with the program and to analyze their data effectively. Learn how to evaluate and summarize data, sort and filter results, format your PivotTables with styles and conditional formatting, and visualize your analysis with PivotCharts.

Note: This course was recorded in Office 365. However, anyone using Excel 2019, Excel 2016, and even prior editions of Excel should be able to follow along with the course contents.

Introduction

• Work in depth with PivotTables
• What you should know

1. Creating and Pivoting PivotTables

• Introducing PivotTables
• Format data for use in a PivotTable
• Create a PivotTable
• Pivot a PivotTable
• Configure a PivotTable
• Connect to an external data source
• Consolidate data from multiple sources
• Manage PivotTables
• Define a default PivotTable layout

2. Summarizing PivotTable Data

• Manage subtotals and grand totals
• Change the data field summary operation
• Summarize more than one data field
• Manage dates in a PivotTable
• Create a calculated field
• Use PivotTable data in a formula
• Drill down to the underlying data

3. Sorting and Filtering PivotTable Data

• Sort PivotTable data
• Create a custom sort order
• Filter a PivotTable field by selection
• Filter a PivotTable by rule
• Filter a PivotTable using a search filter
• Filter a PivotTable using slicers
• Format slicers
• Filter with report filter fields
• Clear and reapply PivotTable filters
• Filter a PivotTable using a timeline

4. Formatting PivotTables

• Apply a PivotTable style
• Create a PivotTable style
• Change the PivotTable layout
• Change the data field number format

5. Applying Conditional Formats to PivotTables

• Highlight cells by applying a rule
• Highlight the top or bottom values in a PivotTable
• Format cells using data bars
• Format cells using color scales
• Format cells using icon sets
• Edit a conditional formatting rule
• Control how multiple rules are applied
• Delete a conditional formatting rule

6. Creating and Formatting PivotCharts

• Create a PivotChart
• Pivot a PivotChart
• Filter a PivotChart
• Format a PivotChart
• Change the layout of a PivotChart
• Change the chart type of a PivotChart
• Add a trendline to a PivotChart

7. Printing PivotTables

• Print a PivotTable
• Set PivotTable print options
• Print each item on its own page
• Print a PivotChart

8. Manipulating PivotTables

• Record and review an Excel macro
• Run an Excel macro
• Create a simple PivotTable presentation kit
• Change PivotTables using keyboard shortcuts

9. Enabling and Adding Tables to the Data Model

• Create relationships between tables
• Create a PivotTable using the data model
• Edit table relationships
• Deactivate and delete table relationships

Conclusion

• Further information