Instructor for this course
more

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

1 Review (6 ratings)Reviews

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

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