Microsoft Excel – Maximizing PivotTables

One Day • Instructor-Led

This course provides students with the knowledge and skills to examine and manipulate raw data through the use of Microsoft Office Excel PivotTables and PivotCharts.


This course is aimed at information workers who need to learn more in-depth techniques of using PivotTables.


Before attending this course, students must have:

  • Taken the Windows XP Level 1 course or have the basic skills and knowledge of Windows applications
  • Be an intermediate user of Microsoft Excel


  • Understand the benefits of PivotTable Reports
  • Convert raw data in an Excel worksheet into meaningful PivotTables and PivotCharts
  • Manipulate the PivotTable layout
  • Use Grouping
  • Create Custom Calculations
  • Use Formulas and create formulas outside of a report
  • Create a PivotChart and publish it to a Web site

Course Outline:

Lesson 1: Make Your Data Work for You

  • Maximize the Power of PivotTable
  • Ask the Questions
  • Get the Answers

Lesson 2: A Quick Tour

  • Determine What You Need to Know
  • The Source is Important
  • Ready, Set, Go
  • The PivotTable Layout
  • What Goes Where
  • Result of our PivotTable
  • Viewing Our Data Differently

Lesson 3: Manipulating the PivotTable

  • Rearrange the Layout
  • What We Get
  • Add a New Field
  • Remove a Field
  • Rename a Field
  • Format Numbers
  • Change the Sort Order and Top 10 AutoShow
  • Refresh Data

Lesson 4: Digging Deeper

  • Create a Quarterly Group
  • Group Data Manually
  • Display Subtotals
  • Hide/Display Grand total
  • Format a PivotTable Report
  • Use a Slicer
  • Format a Slicer

Lesson 5: Beyond the Basics

  • Creating a Second Data Field
  • Use Count to Summarize Data
  • Display Numerical Data as a Percentage of the Total
  • Create a Custom Calculation
  • More Functions in Custom Calculation

Lesson 6: Move PivotTable Calculations

  • Using Formulas
  • Create a Calculated Field
  • Create a Calculated Item
  • Use PivotTable Data Outside the Report
  • Use GETPIIVOTDATA Function
  • Turn off GetPivotData Function

Lesson 7: Optional Topics

  • Create a PivotChart Report
  • Publish PivotTable Reports to the Web
  • Use Multiple Ranges as Source Data

View all upcoming classes