Excel PivotTables: Mastering PivotTables and PivotCharts

Take an in-depth look at PivotTables and PivotCharts, two powerful data analysis tools in Excel. Learn how to more efficiently evaluate your data, by using them together.

Introduction

• Part 1: Introduction

1. Getting Started

• Getting to know IMDb
• Setting expectations

2. PivotTable 101

• Why PivotTables?
• Structuring your source data
• Inserting a PivotTable
• Navigating the Field List
• Analyze and design options
• Selecting, clearing, moving, and copying
• Refreshing and updating PivotTables
• Pro tip: Growing source data
• How PivotTables actually work, part 1
• How PivotTables actually work, part 2

3. PivotTable Formatting

• Number formatting
• Pro tip: Formatting empty cells
• Table layouts and styles
• Pro tip: Tabular layouts
• Customizing headers and labels
• Conditional formatting
• Pro tip: Data bars with invisible text
• Advanced conditional formatting

4. Sorting, Filtering, and Grouping

• Sorting options
• Troubleshooting incorrect sorting
• Label filters and manual selections
• Pro tip: Filtering with wildcards
• Value filters
• Pro tip: Enabling multiple filters
• Grouping options
• Automatic date grouping
• Filtering with slicers and timelines
• Breaking out report filter pages

5. Calculated Values, Fields, and Items

• Value summarization modes
• Pro tip: Avoiding the "count of" trap
• Value calculation modes
• 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 PivotTables vs. source data
• Calculating using counts, part 1
• Calculating using counts, part 2
• Inserting calculated items
• Solve order and list formula tools

6. Visualizing Data with PivotCharts

• Intro to PivotCharts
• Bar and column PivotCharts
• Pie and donut PivotCharts
• Clustered bar PivotCharts
• Pro tip: Prevent charts from resizing
• Changing chart types on the fly
• Stacked area PivotCharts
• Chart layouts and styles
• Moving PivotCharts to new sheets
• Applying slicers to multiple charts
• Designing interactive dashboards

7. Wrapping Up

• Part 1: Conclusion