Excel 2010: Pivot Tables

Provides comprehensive, hands-on Excel tutorials on PivotTables, and more advanced techniques such as using macros and the new PowerPivot add-in.

Introduction

• Welcome
• Using the exercise files

1. Creating and Pivoting PivotTables

• Introducing PivotTables
• Formatting data for use in a PivotTable
• Creating a PivotTable
• Pivoting a PivotTable
• Configuring a PivotTable
• Connecting to an external data source
• Connecting to an Access database
• Consolidating data from multiple sources
• Updating and refreshing PivotTable data sources
• Managing PivotTables

2. Summarizing PivotTable Data

• Adding, removing, and positioning subtotals and grand totals
• Changing the PivotTable data field summary operation
• Summarizing more than one data field
• Creating a calculated field
• Grouping PivotTable fields
• 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 a PivotTable 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 Formatting to PivotTables

• Highlighting cells by applying a rule
• Highlighting the top or bottom values in a PivotTable
• Formatting PivotTable cells using data bars
• Formatting PivotTable cells using color scales
• Formatting PivotTable cells using icon sets
• Editing conditional formatting rules
• 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 layout
• Changing a PivotChart chart type
• Adding a trendline to a PivotChart

7. Printing PivotTables and PivotCharts

• Printing a PivotTable
• Printing each item on its own page
• Printing a PivotChart

8. Manipulating PivotTables Using Macros

• Recording and reviewing a macro
• Running a macro
• Creating a simple PivotTable presentation kit

9. Getting Started with PowerPivot

• Introducing PowerPivot
• Downloading and installing PowerPivot
• Importing PowerPivot data
• Managing table columns
• Adding tables to a PowerPivot model
• Creating relationships between tables

10. Working with DAX Expressions

• Introducing the DAX language
• Using DAX operators
• Surveying DAX functions
• Adding calculated columns and measures
• Using aggregate functions
• Using filters in aggregate functions

Conclusion

• Additional resources