The course is presented in four parts.

First, the course illustrates where pivot tables stand in the world of Excel techniques, and brings up the topic of when to and when NOT to use a pivot table.

Second, we use a business case to compare ways of reporting on large data sets – Formulas vs. Pivot Tables, and point out the pros and cons of Pivot Tables:

  • No complex formulas needed
  • Easy to drag and drop
  • Drag away to remove
  • Double click to drill down to details

Third, the course illustrates 6 basic and 6 intermediate maneuvers to tame the Pivot Table beast!

Last, the course explores best practices in building a pivot table. They include:

  • Do set up Data List as Data Table
  • Do put multiple pivot tables in multiple tabs
  • Do protect sensitive info such as Salary details
  • Do set up “auto refresh” of Pivot Table

 

Course Key Concepts: Row labels, Column labels, Calculated fields, Calculated item, Data table, Salary details, Pivot Table style, Auto Refresh, VBA, Macro.

Learning Objectives
  • Recognize the differences between using formulas and using pivot tables in Excel reporting.
  • Identify and understand the pros and cons of pivot tables.
  • Discover 6 basic skills of taming a pivot table.
  • Explore 6 intermediate skills of taming a pivot table.
  • Identify and understand 6 best practices in building pivot tables.
Last updated/reviewed: August 6, 2023
17 Reviews (60 ratings)

Reviews

5
Anonymous Author
This is a great course to obtain more tips about pivot tables. Lenny does a great job explaining things. It is helpful to take various Excel courses on the same topic because each instructor often mentions different tips that help improve your Excel technique. I am looking forward to more of Lenny's courses.

5
Anonymous Author
Professor Wu - This is second course of yours that I am taking and I am amazed of the various tricks and tips you have illustrated on sumifs and pivot tables. And yet, I am considered to be a pivot table expert! Thank you, I will continue to practise and use them for better outcomes.

5
Member's Profile
An excellent dive beyond the basics of pivot tables. This course is beneficial to someone new to pivot tables or someone experienced with pivot tables to sharpen their skills and pick up tips and tricks. The instructor is rather enjoyable with his presentation method.

4
Anonymous Author
In total, it was very informative & I appreciated the detailed explanations. However I could have done without the instructors introduction of all of his background. Also the summary on how this course compares to similar courses was not needed.

3
Anonymous Author
Provided great information, would have preferred the reference material to be more detailed. It’s hard to remember all the clicks. The demonstration was very good. Did not like the verbiage used in test. Found confusing.

4
Anonymous Author
There is so much Excel can do, it's great to learn when and why to use different features and techniques. The teacher clearly knows the ins and outs of Excel. Thank you for the comprehensive training!

5
Anonymous Author
Very informative! I only knew the basics of pivot tables, I will definitely start using some of these extra features.

4
Anonymous Author
I thought some of the questions on the quiz that asked which things were conducive to...were a little confusing.

5
Anonymous Author
Very detailed course. I appreciated that the instructer personalized the course based on his own experience.

5
Anonymous Author
This is so awesome. There is so much I can do with pivot tables that I never knew. Thank you Illumio!

4
Anonymous Author
Great course Lenny - step by step simple instructions for tips and tricks regarding pivot tables.

5
Member's Profile
Great hacks and tips to building beginning pivot tables. Good clear instruction and materials!

5
Member's Profile
So much useful information, presented clearly so it can be immediately applicable.

5
Member's Profile
Great and easy steps and tricks to get better working with pivot tables

5
Anonymous Author
It helped me better understand the advantages of using a pivot table.

4
Anonymous Author
great class regarding the intricacies of pivot tables.

4
Member's Profile
I user pivot table everyday, so this is good lesson

Prerequisites
Course Complexity: Intermediate

Basic Excel knowledge.
Example: be able to open one Excel file and connect to external data files, 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 Syllabus
INTRODUCTION AND OVERVIEW
  Introduction to Excel Pivot Table: How to Tame the Excel Beast!1:01
  Recap0:32
  Hierarchy 1:27
  Instructor Introduction 1:08
  Other Similar Courses 1:25
  What You will Get from this Course?0:32
Pivot Tables (Gen 3) vs. Formulas (Gen 2)
  Excel Reporting Challenges8:40
  Pivot Table comes to the rescue5:57
Basic Taming Maneuvers
  How to remove “Row Labels” or “Column Labels”?2:48
  How to sort the Total Revenue columns: select “Row Labels”!1:03
  How to format numbers in 1 go?2:33
  How to show Subtotals at the top or bottom?1:00
  How to fix changing column width?1:12
  How to solve “PivotTable field name already exists”?1:21
Intermediate Taming Maneuvers
  How to show % of Grand Total?2:47
  How to insert a blank row?1:24
  How to add customized formulas?4:57
  How to group/ungroup rows or columns?1:26
  How to expand/collapse groups?1:44
  Why does it Count instead of Sum?2:20
Best Practices of Pivot Table
  Pivot Table Best Practice (No. 1)2:26
  Pivot Table Best Practice (No. 2)1:08
  Pivot Table Best Practice (No. 3)1:07
  Pivot Table Best Practice (No. 4)1:54
  Pivot Table Best Practice (No. 5)1:44
  Pivot Table Best Practice (No. 6)6:10
CONCLUSION
  Takeaways 1:27
  Next Course and Q&A 0:28
CONTINUOUS PLAY
  Excel Pivot Table: How to Tame the Excel Beast!1:01:40
SUPPORTING MATERIAL
  Slides: Excel Pivot Table: How to Tame the Excel Beast!PDF
  Excel Pivot Table: How to Tame the Excel Beast! Glossary/ IndexPDF
  Workbook: Excel Pivot Table: How to Tame the Excel Beast!XLSM
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam