The course is presented in four parts:

First, the course illustrates where pivot tables stand in Excel Techniques, and covers the topic of when and when NOT to use a pivot table.

Second, the course illustrates how to build a self-service dashboard using:

  • pivot tables
  • pivot charts
  • slicers
  • timeline

Third, the course provides 5 extreme hacks for pivot tables including:

  • How to collapse 12 months data into 1 field?
  • How to break free of Pivot Tables?
  • How to use Power Pivot without opening Power Pivot?
  • How to connect tables without using VLOOKUP()?

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

  • Do follow data table naming convention
  • Do fix changing column width
  • Do keep column/bar charts sorted
  • Do add a heat map for pivot tables

Course Key Concepts: Artificial Intelligence, Pivot chart, Slicer, Timeline, Power Pivot, Power Query, Data model, Outline form, Calculated fields, Calculated item, Auto Refresh, VBA, Macro.

Learning Objectives
  • Explore and understand crucial elements and skills in building a dashboard.
  • Recognize ways of building inter-connected data models for pivot tables and Power Pivot.
  • Discover 5 extreme Pivot Table hacks.
  • Recognize differences between GETPIVOTDATA() and CUBEVALUE().
  • Explore and understand 7 best practices of building pivot tables in a dashboard.
Last updated/reviewed: March 11, 2024
7 Reviews (32 ratings)

Reviews

5
Member's Profile
Awesome course! I learned so much about PivotTables, Power Query, Relationships, etc. Very informative - I am looking forward to doing more courses by Mr. Wu.
5
Member's Profile
I really like Lenny's courses. This is another great course that shows the power of pivot tables and how you can be free from mundane reports.
5
Member's Profile
Again another great course by Lenny Wu. He explains what to do and shows how to clearly. The hacks and best practices are great!
5
Anonymous Author
Love the tips for autorefresh and dashboard DOs and DON'Ts. Look forward to other courses w/ Lenny.
5
Anonymous Author
Thank you, Lenny for another excellent course! Much helpful and useful information.
5
Member's Profile
Really great content and awesome workbooks to practice along with
5
Member's Profile
PERFECT pace and excellent hacks to use immediately!
Prerequisites
Course Complexity: Advanced

Basic Excel knowledge
Basic Pivot Table knowledge
Example: be able to build a simple Pivot Table from scratch in seconds, 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 Advanced Pivot Table: Ticket to Self-service Dashboards0:48
  Hierarchy of Excel Techniques1:16
  Instructor Introduction 1:10
  Comparison with Other Similar Courses 1:22
  What You will Get from this Course? 0:37
Pivot Table and Self-service Dashboard
  Dashboard 1 - Artificial Intelligence function3:54
  Dashboard 2 - Pivot chart2:00
  Dashboard 3 - Top 10 List1:11
  Dashboard 4 - Value ranges3:28
  Dashboard 5 - Slicer1:33
  Dashboard 6 - Timeline1:18
  Dashboard 7 - How to control multiple pivot tables?2:28
Extreme Pivot Table Hacks
  How to collapse 12 months data into 1 field?5:35
  How to use Power Pivot without opening Power Pivot?2:32
  How to connect tables without using VLOOKUP()?4:43
  How to build a dynamic financial report on a pivot table?4:33
  How to break free of Pivot Tables?4:45
Best Practices - Pivot Tables in a Dashboard
  Best Practice No. 11:47
  Best Practice No. 21:39
  Best Practice No. 31:14
  Best Practice No. 41:08
  Best Practice No. 51:33
  Best Practice No. 61:40
  Best Practice No. 73:24
CONCLUSION
  Takeaways 1:22
  Next Course and Q&A 0:30
CONTINUOUS PLAY
  Advanced Pivot Table: Ticket to Self-service Dashboards57:26
SUPPORTING MATERIAL
  Slides: Advanced Pivot Table: Ticket to Self-service DashboardsPDF
  Advanced Pivot Table: Ticket to Self-service Dashboards Glossary/ IndexPDF
  Pivot Tables Advanced (Practice)XLSX
  Pivot Tables Advanced (Answer)XLSM
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam