Class Outline

Excel PivotTables is a one-day instructor-led course that will teach you how to use pivot tables to easily manipulate large data sets with drags and drops, and present that data superbly in charts with little effort. PivotTables rightly has changed the way organizations use Excel, and this class will take your data game to another level helping you scrub, summarize, sort, filter group, and format your data like a pro.

The training covers these topics:

Creating PivotTables

  • What is a PivotTable?
  • Scrubbing the Data
  • Creating the PivotTable on a Range of Cells
  • Creating a PivotTable Based on an Excel Table
  • Creating a PivotTable Based on a Microsoft Access Table
  • Changing Data and the Pivot Cache

Drilling Down

  • Drilling Down on a Value
  • Drilling Down on a Row or Column Label

Sorting in PivotTables

  • How PivotTable Sorting Works
  • Sorting a PivotTable with Row Labels
  • Sorting Values in a PivotTable
  • Manually Override a Sort Order

Filtering in PivotTables

  • Filtering a PivotTable by Row
  • Filtering a PivotTable by Column
  • Filtering a PivotTable by Filter Section in the Field List
  • Using Report Filter Pages
  • Using Slicers to Filter Data
  • Setting up a Timeline

Grouping in PivotTables

  • What is Grouping in a PivotTable?
  • Grouping Based on Dates
  • Grouping Based on Labels
  • Un-Grouping PivotTable Data
  • Issues with Groupings

Setting PivotTable Field and Global Options

  • Displaying Items with No Data
  • Displaying Blank Cells with Other Text
  • Setting and Modifying Subtotals
  • Setting Custom Subtotal Options
  • The Global Options Feature

Report Layout Features

  • Adding Blank Rows within your PivotTable
  • Exploring the Three Layout Options
  • Setting a Default Layout and Repeating Items
  • The Grand Totals Feature
  • Using Styles in a PivotTable

Working with Calculations in a PivotTable

  • Field Calculations in PivotTables
  • Modifying Field Calculations
  • List the PivotTable Calculations
  • Creating a Function Outside of a PivotTable

Pivot Charts

  • What is a Pivot Chart?
  • Creating a Pivot Chart
  • Filtering and Grouping in Pivot Charts
  • Enhancing Pivot Charts

Working with the Show Commands

  • Showing and Hiding the Field List
  • Showing and Hiding Expand/Collapse Buttons and Headers