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

0. Introduction

01 - Part 1 - Introduction

1. Getting Started

02 - Getting to know IMDb
03 - Setting expectations

2. PivotTable 101

04 - Why PivotTables
05 - Structuring your source data
06 - Inserting a PivotTable
07 - Navigating the Field List
08 - Analyze and design options
09 - Selecting, clearing, moving, and copying
10 - Refreshing and updating PivotTables
11 - Pro tip - Growing source data
12 - How PivotTables actually work, part 1
13 - How PivotTables actually work, part 2

3. PivotTable Formatting

14 - Number formatting
15 - Pro tip - Formatting empty cells
16 - Table layouts and styles
17 - Pro tip - Tabular layouts
18 - Customizing headers and labels
19 - Conditional formatting
20 - Pro tip - Data bars with invisible text
21 - Advanced conditional formatting

4. Sorting, Filtering, and Grouping

22 - Sorting options
23 - Troubleshooting incorrect sorting
24 - Label filters and manual selections
25 - Pro tip - Filtering with wildcards
26 - Value filters
27 - Pro tip - Enabling multiple filters
28 - Grouping options
29 - Automatic date grouping
30 - Filtering with slicers and timelines
31 - Breaking out report filter pages

5. Calculated Values, Fields, and Items

32 - Value summarization modes
33 - Pro tip - Avoiding the count of trap
34 - Value calculation modes
35 - Show Values As - of Column Row
36 - Show Values As - of Parent
37 - Show Values As - Difference From
38 - Show Values As - Running Total
39 - Show Values As - Rank
40 - Show Values As - Index
41 - Inserting calculated fields
42 - Calculations in PivotTables vs. source data
43 - Calculating using counts, part 1
44 - Calculating using counts, part 2
45 - Inserting calculated items
46 - Solve order and list formula tools

6. Visualizing Data with PivotCharts

47 - Intro to PivotCharts
48 - Bar and column PivotCharts
49 - Pie and donut PivotCharts
50 - Clustered bar PivotCharts
51 - Pro tip - Prevent charts from resizing
52 - Changing chart types on the fly
53 - Stacked area PivotCharts
54 - Chart layouts and styles
55 - Moving PivotCharts to new sheets
56 - Applying slicers to multiple charts
57 - Designing interactive dashboards

7. Wrapping Up

58 - Part 1 - Conclusion

Excel PivotTables: Mastering PivotTables and PivotCharts

Authors

Chris Dutton