Excel 2016: Pivot Tables in Depth

Learn how to use PivotTables to summarize, sort, count, and chart your data in Microsoft Excel 2016.

Introduction

• Welcome
• Using the exercise files

1. Creating and Pivoting PivotTables

• Introducing PivotTables
• Formatting data for use in a PivotTable
• Creating a PivotTable
• Creating a recommended PivotTable
• Pivoting a PivotTable
• Configuring a PivotTable
• Connecting to an external data source
• Consolidating data from multiple sources
• Managing PivotTables

2. Summarizing PivotTable Data

• Managing subtotals and grand totals
• Changing the data field summary operation
• Summarizing more than one data field
• Creating a calculated field
• Using PivotTable data in a formula
• Drilling down to the underlying data

3. Sorting and Filtering PivotTable Data

• Sorting PivotTable data
• Creating a custom sort order
• Filtering a PivotTable field by selection
• Filtering a PivotTable by rule
• Filtering a PivotTable using a search filter
• Filtering a PivotTable using slicers
• Formatting slicers
• Filtering with report filter fields
• Clearing and reapplying PivotTable filters

4. Formatting PivotTables

• Applying a PivotTable style
• Creating a PivotTable style
• Changing the PivotTable layout
• Changing the data field number format

5. Applying Conditional Formats to PivotTables

• Highlighting cells by applying a rule
• Highlighting the top or bottom values in a PivotTable
• Formatting cells using data bars
• Formatting cells using color scales
• Formatting cells using icon sets
• Editing a conditional formatting rule
• Controlling how multiple rules are applied
• Deleting a conditional formatting rule

6. Creating and Manipulating PivotCharts

• Creating a PivotChart
• Pivoting a PivotChart
• Filtering a PivotChart
• Formatting a PivotChart
• Changing a PivotChart's layout
• Changing a PivotChart's chart type
• Adding a trendline to a PivotChart

7. Printing PivotTables

• Printing a PivotTable
• Setting PivotTable print options
• Printing each item on its own page
• Printing a PivotChart

8. Manipulating PivotTables

• Recording and reviewing an Excel macro
• Running an Excel macro
• Creating a simple PivotTable presentation kit
• Working with PivotTables using keyboard shortcuts

9. Creating PivotTables Using the Data Model

• Creating relationships between tables
• Creating a PivotTable using the data model
• Editing table relationships

Conclusion

• Next steps