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: March 16, 2024
5 Reviews (27 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
5
Member's Profile
I really like the short videos as they will allow me to return and quickly find the details at a later date should I forget. Great Class!
5
Member's Profile
Very productive. Actual examples are great and very useful in actual work.
5
Anonymous Author
Great course Informative and helpful Good explanations and examples
4
Anonymous Author
Excellent presentation with all topics supported by Excel examples.
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 .
Instructor for this course
  Course Overview CPE
Course Syllabus
Introduction and overview
  Introduction to Excel Pivot Table Intermediate: Mastering Pivot Tables like a PRO0:58
  Recap0:46
  Instructor Introduction 1:12
  Comparison with Other Similar Courses 1:26
  What You Will Get from This Course? 0:38
Intermediate Taming Maneuvers – Calculations
  How to solve a formula error in a pivot table3:20
  How to show a running total2:37
  How to show averages? What average?4:03
  How to show a list of formulas used2:00
  How to count a unique times3:55
Intermediate Taming Maneuvers – Display and Layout
  How to customize pivot table fields window1:36
  How to add or remove fields quickly1:26
  How to turn grand total off1:33
  How to repeat all row labels1:07
  How to quickly open value field settings1:00
  How to keep formatting upon update0:46
  How to set up a schedule to refresh pivot tables2:26
Advanced Taming Maneuvers
  How to remove old data from the filter2:46
  How to automatically update manual filtered list3:40
  How to set up a customized sorting order4:50
  How to cut down big file size2:59
  How to get original Field name for a Renamed one1:14
  How to generate pivot reports for all entities1:49
10 Keyboard Shortcuts
  To convert data list to data table1:18
  To create a pivot table0:38
  To open Calculated Field Window0:33
  To insert a pivot chart0:30
  To refresh 1 pivot table0:35
  To refresh all pivot tables0:52
  To group fields0:34
  To ungroup fields0:24
  To filter out an item0:38
  To open the old PivotTable Wizard window2:10
CONCLUSION
  Takeaways 1:56
  Next Course & Q&A0:26
CONTINUOUS PLAY
  Pivot Table Intermediate: Mastering Pivot Tables like a PRO58:36
SUPPORTING MATERIAL
  Slides: Pivot Table Intermediate: Mastering Pivot Tables like a PROPDF
  Pivot Table Intermediate: Mastering Pivot Tables like a PRO Glossary/ IndexPDF
  Workbook: Pivot Table Intermediate: Mastering Pivot Tables like a PROXLXS
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam