Instructor for this course
more

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: November 08, 2020

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 .
Course Syllabus
INTRODUCTION AND OVERVIEW
  0:48Introduction to Advanced Pivot Table: Ticket to Self-service Dashboards
  1:16Hierarchy of Excel Techniques
  1:10Instructor Introduction
  1:22Comparison with Other Similar Courses
  0:37What You will Get from this Course?
Pivot Table and Self-service Dashboard
  3:54Dashboard 1 - Artificial Intelligence function
  2:00Dashboard 2 - Pivot chart
  1:11Dashboard 3 - Top 10 List
  3:28Dashboard 4 - Value ranges
  1:33Dashboard 5 - Slicer
  1:18Dashboard 6 - Timeline
  2:28Dashboard 7 - How to control multiple pivot tables?
Extreme Pivot Table Hacks
  5:35How to collapse 12 months data into 1 field?
  2:32How to use Power Pivot without opening Power Pivot?
  4:43How to connect tables without using VLOOKUP()?
  4:33How to build a dynamic financial report on a pivot table?
  4:45How to break free of Pivot Tables?
Best Practices - Pivot Tables in a Dashboard
  1:47Best Practice No. 1
  1:39Best Practice No. 2
  1:14Best Practice No. 3
  1:08Best Practice No. 4
  1:33Best Practice No. 5
  1:40Best Practice No. 6
  3:24Best Practice No. 7
CONCLUSION
  1:22Takeaways
  0:30Next Course and Q&A
CONTINUOUS PLAY
  57:26Advanced Pivot Table: Ticket to Self-service Dashboards
SUPPORTING MATERIAL
  PDFSlides: Advanced Pivot Table: Ticket to Self-service Dashboards
  PDFAdvanced Pivot Table: Ticket to Self-service Dashboards Glossary/ Index
  XLSXPivot Tables Advanced (Practice)
  XLSMPivot Tables Advanced (Answer)
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM