Excel: PivotTables in Depth

Course details

PivotTables are one of the most powerful features in Excel, yet many users are barely scratching the surface of what they can do. 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. Learn how to sort and filter PivotTable data, format your PivotTable, and apply conditional formats. Curt also explains 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 to your data
02 - What you should know before starting

1. Creating and Pivoting PivotTables

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

2. Summarizing PivotTable Data

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

3. Sorting and Filtering PivotTable Data

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

4. Formatting PivotTables

28 - Apply a PivotTable style
29 - Create a PivotTable style
30 - Change the PivotTable layout
31 - Change the data field number format

5. Applying Conditional Formats to PivotTables

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

6. Creating and Formatting PivotCharts

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

7. Printing PivotTables

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

8. Manipulating PivotTables

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

9. Enabling and Adding Tables to the Data Model

55 - Create relationships between tables
56 - Create a PivotTable using the data model
57 - Edit table relationships
58 - Deactivate and delete table relationships
59 - Define sets of values

Conclusion

60 - Further resources