Excel Pivot Table Workshop – Advanced Level

Students will learn how to use Pivot Tables and the available tools to assist with summarizing and analyzing large amounts of data.

Training Goal

Upon successful completion of this course, students will be able to:

• Build a PivotTable
• Analyze data using PivotTables
• Present PivotTable data visually

PreRequisites

This course is meant for people with a sound working knowledge of Microsoft Excel and general computer proficiency.

Topics Covered

Getting Started with PivotTables

• What is a PivotTable?
• Creating a PivotTable
• Using the PivotTable Tools Tabs
• Adding and Removing Data with the Field List
• Changing the Field List Layout
• Pivoting Data

Working with PivotTable Data

• Expanding and Collapsing Data
• Filtering Data
• Sorting Data
• Grouping Data
• Refreshing Data
• Editing the Data Source

Formatting a PivotTable

• Modifying Fields and Labels
• Modifying Values
• Using the Layout Group on the Design Tab
• Applying a Style to a PivotTable
• Changing PivotTable Style Options
• Manually Formatting a PivotTable
• Using the PivotTable Options Dialog

Using the Classic PivotTable Layout

• Creating an Empty (Classic) PivotTable Frame
• Switching an Existing PivotTable to a Classic Layout
• Adding Data
• Pivoting Data

Advanced PivotTable Tasks

• Creating a PivotTable Based on External Data
• Refreshing External Data
• Creating a Slicer
• Using the Slicer Tools Tab

Using Power Pivot

• System Requirements
• Downloading and Installing
• Importing Access Data
• Importing Excel Data
• Integrating Data with Relationships
• Creating a PivotTable with PowerPivot Data

Charting Pivoted Data

• Getting Started with PivotCharts
• Creating a PivotChart from Scratch
• Creating a PivotChart from Existing Data
• Adding Data to your Chart
• Pivoting Data

Formatting a PivotChart

• Renaming Fields
• Changing the Chart Type
• Applying a Chart Style
• Manually Formatting Chart Elements
• Changing the Layout of Chart Elements

Advanced PivotChart Tasks

• Creating a PivotChart Based on External Data
• Creating a Slicer
• Creating a PivotTable and PivotChart from a Scenario
• Creating PivotCharts with PowerPivot Data

Additional Tools for Managing Large Amounts of Data

• Outlining and Grouping Data
• Using Automatic Outlining
• Displaying and Collapsing Levels
• Grouping Data Manually
• Creating Subtotals