Excel PivotTables Part 1: PivotTables 101

This class is Part 1 of a three-part series covering data analysis with Excel PivotTables and PivotCharts.

PivotTables are an absolutely essential tool for anyone working with data in Microsoft Excel. Pivots allow you to quickly explore and analyze raw data, reveal powerful insights and trends otherwise buried in the noise, and provide fast, accurate and intuitive solutions to even the most complicated questions.

In Part 1 of this course, I'll show you when, why, and how to use PivotTables, introduce advanced sorting, filtering, and calculation tools, and guide you through interactive, hands-on demos and exercises every step of the way.

We'll cover everything you need to know to get up and running with PivotTables, including:

• Raw data structure
• Table layouts & styles
• Design & formatting options
• Sorting, filtering, & grouping tools
• Calculated fields, items & values

From there we'll take what we've learned and move on to Part 2, which introduces PivotCharts, slicers, timelines and custom dashboards. Finally we'll wrap up with Part 3, where we'll tie it all together and explore several advanced, real-world case studies!

Requirements: 

• Microsoft Excel (2010, 2013, 2016), ideally for PC
• Mac users are welcome, but note that the PivotTable interface varies across platforms
• Basic experience with Excel functionality (charts & formulas a plus!)

• Class Materials and Outline
• Getting to Know the IMBD Dataset
• Setting Expectations
• Why Pivot Tables?
• Structuring Your Source Data
• Inserting Your First Pivot Table
• Navigating the Field List
• Pivot Table Options: Analyze and Design
• Selecting, Clearing, Moving & Copying Pivots
• Refreshing & Updating Pivots
• PRO TIP: Dealing with Growing Source Data
• How Pivot Tables Actually Work
• Number Formatting
• PRO TIP: Automatically Formatting Empty Cells
• Table Layouts & Styles
• PRO TIP: Using Tabular Tables to Create New Source Data
• Customizing Headers & Labels
• Conditional Formatting
• PRO TIP: Data Bars with Invisible Text
• Sorting Options
• PRO TIP: Why is my Alphabetical Sorting Wrong?
• Label Filters & Manual Selections
• PRO TIP: Using Label Filters with Wildcards
• Value Filters
• PRO Tip: Enabling Multiple Filters
• Grouping Data
• PRO TIP: Automatic Date Grouping
• Using Slicers & Timelines to Filter Data
• Breaking Out Report Filter Pages
• "Summarize Values By" Options
• PRO TIP: Avoiding the "Count Of" Trap
• "Show Value As" Calculations
• Show Values As: % of Column/Row
• Show Values As: % of Parent
• Show Values As: Difference From
• Show Values As: Running Total
• Show Values As: Rank
• Show Values As: Index
• Inserting Calculated Fields
• Calculations in Pivots vs. Raw Data
• PRO TIP: Calculating Using Counts
• PRO TIP: Calculating Using Counts (part 2)
• Inserting a Calculated Item (not recommended!)
• The Solve Order & List Formulas Tools