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.

Audience:

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

Prerequisites:

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

Objectives:

  • 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