Excel PivotTables: Mastering PivotTables and PivotCharts

Course details

While the power and time-saving value of PivotTables appeals to anyone who analyzes data in Excel, many users are too intimidated by the feature to add it to their workflow. In this course—the first installment in a two-part series—instructor Chris Dutton breaks down how to use PivotTables, as well as how to add PivotCharts to the mix to summarize your data visually. Throughout the course, Chris shares practical tips and demonstrations that help you grasp how these tools are used in the real world, as well as how PivotTable values are actually calculated and displayed. Learn how to prepare your raw data for analysis; leverage formatting, sorting, filtering, and grouping options; display values in multiple ways; create PivotCharts; and much more.

Learning objectives
Establishing a layer convention and property filters
Structuring your source data
How PivotTables actually work
Number formatting and conditional formatting
Sorting and grouping options
Troubleshooting incorrect sorting
Value summarization and value calculation modes
Inserting calculated items
Visualizing data with PivotCharts
Designing interactive dashboards

Concepts

Introduction

Part 1 - Introduction

Getting Started

Getting to know IMDb
Setting expectations

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

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

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

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

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

Wrapping Up

Part 1 - Conclusion