Overview
-
Provides comprehensive, hands-on Excel tutorials on PivotTables, and more advanced techniques such as using macros and the new PowerPivot add-in.
Syllabus
-
Introduction
- Welcome
- Using the exercise files
- 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
- 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
- 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
- Applying a PivotTable style
- Creating a PivotTable style
- Changing the PivotTable layout
- Changing the data field number format
- 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
- 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
- Printing a PivotTable
- Printing each item on its own page
- Printing a PivotChart
- Recording and reviewing a macro
- Running a macro
- Creating a simple PivotTable presentation kit
- Introducing PowerPivot
- Downloading and installing PowerPivot
- Importing PowerPivot data
- Managing table columns
- Adding tables to a PowerPivot model
- Creating relationships between tables
- Introducing the DAX language
- Using DAX operators
- Surveying DAX functions
- Adding calculated columns and measures
- Using aggregate functions
- Using filters in aggregate functions
- Additional resources
