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.
Prerequisites
Basic Excel knowledge
Excel Pivot Table: basics
Example: be able to open one Excel file and build a simple pivot table, etc.
Learning Objective
- 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
(0) Reviews
(26 rating)Lesson Questions and Answers0 Questions
There are no questions.
Answers to Frequently Asked Questions (FAQs)
Lenny Wu
Management Reporting Analyst, Seaspan Ship Management
Introduction and Overview
- Introduction to Excel Pivot Table Intermediate: Mastering Pivot Tables like a PRO 0:58
- Recap 0: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 table 3:20
- How to show a running total 2:37
- How to show averages? What average? 4:03
- How to show a list of formulas used 2:00
- How to count a unique times 3:55
Intermediate Taming Maneuvers – Display and Layout
- How to customize pivot table fields window 1:36
- How to add or remove fields quickly 1:26
- How to turn grand total off 1:33
- How to repeat all row labels 1:07
- How to quickly open value field settings 1:00
- How to keep formatting upon update 0:46
- How to set up a schedule to refresh pivot tables 2:26
Advanced Taming Maneuvers
- How to remove old data from the filter 2:46
- How to automatically update manual filtered list 3:40
- How to set up a customized sorting order 4:50
- How to cut down big file size 2:59
- How to get original Field name for a Renamed one 1:14
- How to generate pivot reports for all entities 1:49
10 Keyboard Shortcuts
- To convert data list to data table 1:18
- To create a pivot table 0:38
- To open Calculated Field Window 0:33
- To insert a pivot chart 0:30
- To refresh 1 pivot table 0:35
- To refresh all pivot tables 0:52
- To group fields 0:34
- To ungroup fields 0:24
- To filter out an item 0:38
- To open the old PivotTable Wizard window 2:10
CONCLUSION
- Takeaways 1:56
- Next Course & Q&A 0:26
CONTINUOUS PLAY
SUPPORTING MATERIAL
- Slides: Pivot Table Intermediate: Mastering Pivot Tables like a PRO PDF
- Pivot Table Intermediate: Mastering Pivot Tables like a PRO Glossary/ Index PDF
- Workbook: Pivot Table Intermediate: Mastering Pivot Tables like a PRO XLSX
REVIEW AND TEST
- REVIEW QUESTIONS quiz
- FINAL EXAM exam
Ask the instructor a question about this lesson