Excel: PivotTables in Depth

Introduction

• Apply the power of Excel PivotTables to your data
• What you should know before starting

1. Creating and Pivoting PivotTables

• Introduce 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
• 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 a PivotTable 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 a PivotChart’s layout
• Change a PivotChart’s chart type
• 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
• Define sets of values

Conclusion

• Further resources