Instructor for this course
more

The course is presented in four parts.

First, the course reviews 5 basic skills in building a pivot table learned in “Excel Pivot Table: How to Tame the Excel Beast!”

Second, we demonstrate 12 intermediate maneuvers to tame the Pivot Table beast. They include:

  • How to solve a formula error in a pivot table
  • How to show a Running total
  • How to show averages. What average?
  • How to count unique items

Third, we demonstrate 6 advanced maneuvers to tame the Pivot Table beast. They include:

  • How to update manual filtered list
  • How to set up a customized sorting order
  • How to cut down big file size
  • How to generate pivot reports for all entities

Last, the course shows 10 keyboard shortcuts used in building a pivot table. They include:

  • Ctrl + T to convert data list to data table
  • Alt + N + V to create a pivot table
  • Alt + F1 to insert a pivot chart

 

Course Key Concepts: Trap, Distinct count, Pivot table, Calculated fields, Calculated item.

Learning Objectives

  • Identify and understand when to suppress and when not to suppress a formula error.
  • Explore 5 intermediate skills of taming a pivot table’s calculations and formulas.
  • Discover 7 intermediate skills of taming a pivot table’s display and layout.
  • Identify and understand 6 advanced skills of taming a pivot table.
  • Recognize 10 keyboard shortcuts in working with a pivot table.
Last updated/reviewed: January 18, 2021

1 Review (6 ratings)Reviews

5
Anonymous Author
well presented and practical. I gain the understanding after walking through with the excel file following all the steps. To enjoy the session need a practice of the pivot

Prerequisites

Course Complexity: Advanced

Basic Excel knowledge
Excel Pivot Table: basics
Example: be able to open one Excel file and build a simple pivot table, etc.

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
Introduction and overview
  0:58Introduction to Excel Pivot Table Intermediate: Mastering Pivot Tables like a PRO
  0:46Recap
  1:12Instructor Introduction
  1:26Comparison with Other Similar Courses
  0:38What You Will Get from This Course?
Intermediate Taming Maneuvers – Calculations
  3:20How to solve a formula error in a pivot table
  2:37How to show a running total
  4:03How to show averages? What average?
  2:00How to show a list of formulas used
  3:55How to count a unique times
Intermediate Taming Maneuvers – Display and Layout
  1:36How to customize pivot table fields window
  1:26How to add or remove fields quickly
  1:33How to turn grand total off
  1:07How to repeat all row labels
  1:00How to quickly open value field settings
  0:46How to keep formatting upon update
  2:26How to set up a schedule to refresh pivot tables
Advanced Taming Maneuvers
  2:46How to remove old data from the filter
  3:40How to automatically update manual filtered list
  4:50How to set up a customized sorting order
  2:59How to cut down big file size
  1:14How to get original Field name for a Renamed one
  1:49How to generate pivot reports for all entities
10 Keyboard Shortcuts
  1:18To convert data list to data table
  0:38To create a pivot table
  0:33To open Calculated Field Window
  0:30To insert a pivot chart
  0:35To refresh 1 pivot table
  0:52To refresh all pivot tables
  0:34To group fields
  0:24To ungroup fields
  0:38To filter out an item
  2:10To open the old PivotTable Wizard window
CONCLUSION
  1:56Takeaways
  0:26Next Course & Q&A
CONTINUOUS PLAY
  58:36Pivot Table Intermediate: Mastering Pivot Tables like a PRO
SUPPORTING MATERIAL
  PDFSlides: Pivot Table Intermediate: Mastering Pivot Tables like a PRO
  PDFPivot Table Intermediate: Mastering Pivot Tables like a PRO Glossary/ Index
  XLXSWorkbook: Pivot Table Intermediate: Mastering Pivot Tables like a PRO
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM