mentor training
Excel Pivot Tables

This class is designed for the experienced Excel user who wants to go beyond the basics with a special focus on analyzing large tables of data or databases using Excel's PivotTables. Participants will learn to create, format and refresh PivotTables and Charts. The class covers Grouping data, showing and hiding detail, using calculated items and fields, and connecting to external data sources to create PivotTables. Participants should have experience with the Office 2007 user interface. 6 hours.

Course Prerequisites: Microsoft Excel Level Two or equivalent experience.

Pivot Table Fundamentals
What is a Pivot Table?
When and Why to use a Pivot Table
Pivot Table anatomy
Row area
Data area
Column area
Page area
Pivot Table cache
The PivotTable Toolbar
Pivot Table limitations

Creating a Pivot Table
Preparing Data
Guidelines for Good Data Source Design
Tabular layout
Practices to avoid
Formatting fields
Cleaning up messy source data
Introduction to the Pivot Table Wizard
Pivot Table location considerations
New worksheet
Existing worksheet
Pivot Table Layout Dialog Box
Adding Fields to the Pivot Table
Rearranging the Pivot Table
Managing Changes and Additions to Source Data
Changes to Cells only
New Rows or Columns
Copying and Deleting Pivot Tables

Customizing Fields in a Pivot Table
Using the Pivot Table Field Dialog Box
For Row and Column Fields
For Page Fields
For Data Fields
Customizing Field Names
Formatting Data Fields
Count, Sum and Other Summary Calculations
Adding and Removing Subtotals
Using Running Totals

Formatting a Pivot Table
Using AutoFormat
Custom Formatting
Setting Table Options
Grand Totals
Merged Labels
Preserve Formatting
Repeat Item Labels

Controlling What's Viewed in a Pivot Table
Using the Show and Hide Options
Row Fields
Column Fields
Items without Data
Page Fields
Data Fields
Sorting in a Pivot Table
AutoSort and AutoShow
Manual Sort
Standard Toolbar Sort
Grouping Data
Grouping Date Fields
Grouping Numeric Fields
Grouping Text Fields
Using Drill-down to View Detail Data
Using Pages to View Data Subsets

Using Calculations in a Pivot Table
Distinguish Calculation Fields and Calculation Items
Using Multiple Methods to Create Calculation Fields and Items
Rules for Pivot Table Calculations
Managing Pivot Table Calculations

Using Disparate Data Sources in a Pivot Table
Working with Multiple Ranges of Data
Anatomy of a Multiple Consolidation Range Pivot Table
Creating a Pivot Table from another Pivot Table
Working with External Data Sources
Using the Pivot Table Wizard to get External Data
Getting External Data without the Pivot Table Wizard
Creating Dynamic Pivot Tables

Creating and Using Pivot Charts
What is a Pivot Chart?
Creating a Pivot Chart
Rules and Limitations of Pivot Charts
Alternatives to Pivot Charts

Microsoft Excel Level Two or equivalent experience.