Excel: PivotTables in Depth (2021)

Course details

Many Excel users aren’t sure how to use PivotTables. In this course, Excel power user Curt Frye shows you how to navigate the complexity of PivotTables while taking advantage of their power. Curt explains how to build PivotTables from single or multiple data sources, pivot and configure a PivotTable, define a default PivotTable layout, and more. He demonstrates ways to summarize your data, add calculated fields, filter your results, and format your layout to make it more readable. Curt steps you through how to sort and filter PivotTable data, format your PivotTable and, and apply conditional formats. Plus, he goes over how to create PivotCharts to visualize your data, enhance PivotTables with macros, and use the Data Model feature to build PivotTables from related tables.

Note: This course was recorded in Microsoft 365. However, anyone using a recent standalone version of Excel (Excel 2021, 2019, or 2016) should be able to follow along with the course contents.

Learning objectives
Determine when it is necessary to click Defer Layout Update in the fields area.
Recall the easiest way to create a PivotTable from external data that cannot be imported directly as an Excel table.
Explain the configuration that takes up the least width on the screen while keeping the Fields List area visible.
Identify the mistake users often make when creating filters.
Explore the function of slicers.
Review the use of data bars with a PivotTable.
Name the first step in viewing all the conditional rules applied to a PivotTable.
Recognize the appropriate way to save a macro.

Concepts

0. Introduction

01 - Apply the power of Excel PivotTables
02 - What you should know before starting
03 - The changing Excel interface

1. Creating and Pivoting PivotTables

04 - Introduction to PivotTables
05 - Format data for use in a PivotTable
06 - Create a PivotTable
07 - Pivot a PivotTable
08 - Configure a PivotTable
09 - Connect to an external data source
10 - Consolidate data from multiple sources
11 - Manage PivotTables
12 - Define a default PivotTable layout

2. Summarizing PivotTable Data

13 - Manage subtotals and grand totals
14 - Change the data field summary operation
15 - Summarize more than one data field
16 - Manage dates in a PivotTable
17 - Create a calculated field
18 - Use PivotTable data in a formula
19 - Drill down to the underlying data

3. Sorting and Filtering PivotTable Data

20 - Sort PivotTable data
21 - Create a custom sort order
22 - Filter a PivotTable field by selection
23 - Filter a PivotTable by rule
24 - Filter a PivotTable using a search filter
25 - Filter a PivotTable using slicers
26 - Format slicers
27 - Filter a PivotTable with report filter fields
28 - Clear and reapply PivotTable filters
29 - Filter a PivotTable using a Timeline

4. Formatting PivotTables

30 - Apply a PivotTable style
31 - Create a PivotTable style
32 - Change the PivotTable layout
33 - Change the data field number format

5. Applying Conditional Formats to PivotTables

34 - Highlight cells by applying a rule
35 - Highlight the top or bottom values in a PivotTable
36 - Format cells using data bars
37 - Format cells using color scales
38 - Format cells using icon sets
39 - Edit a conditional formatting rule
40 - Control how multiple rules are applied
41 - Delete a conditional formatting rule

6. Creating and Formatting PivotCharts

42 - Create a PivotChart
43 - Pivot a PivotChart
44 - Filter a PivotChart
45 - Format a PivotChart
46 - Change a PivotChart s layout
47 - Change a PivotChart s chart type
48 - Add a trendline to a PivotChart

7. Printing PivotTables

49 - Print a PivotTable
50 - Set PivotTable print options
51 - Print each item on its own page
52 - Print a PivotChart

8. Manipulating PivotTables

53 - Record and review an Excel macro
54 - Run an Excel macro
55 - Create a simple PivotTable presentation kit
56 - Change PivotTables using keyboard shortcuts

9. Enabling and Adding Tables to the Data Model

57 - Create relationships between tables
58 - Create a PivotTable using the data model
59 - Edit table relationships
60 - Deactivate and delete table relationships
61 - Define sets using the Data Model

Conclusion

62 - Additional resources