Excel 2016: Pivot Tables in Depth

Course details

Learn how to use PivotTables to summarize, sort, count, and chart your data in Microsoft Excel 2016. Author Curt Frye shows you how to navigate the complexity of PivotTables while taking advantage of their power. This course shows how to build PivotTables from single or multiple data sources, add calculated fields, filter your results, and format your layout to make it more readable. Plus, learn how to create PivotCharts to visualize your data, enhance PivotTables with macros, and use the Data Model feature to build PivotTables from related tables.

Learning objectives
Identify how to format data for use in a PivotTable.
Discover how to create a new PivotTable.
Define how to connect to data sources.
Evaluate and consolidate data from multiple data sources.
Identify how to create calculated fields.
Summarize field data.
Review PivotTable sorting.
Review PivotTable filters.
Discover how to work with Excel slicers.
Recall how to format PivotTables with styles and formats.
Assess how to build PivotCharts.
Assess how to run macros.
Evaluate how to create a PivotTable using the data model.

Concepts

0. Introduction

01 - Welcome
02 - Using the exercise files

1. Creating and Pivoting PivotTables

03 - Introducing PivotTables
04 - Formatting data for use in a PivotTable
05 - Creating a PivotTable
06 - Creating a recommended PivotTable
07 - Pivoting a PivotTable
08 - Configuring a PivotTable
09 - Connecting to an external data source
10 - Consolidating data from multiple sources
11 - Managing PivotTables

2. Summarizing PivotTable Data

12 - Managing subtotals and grand totals
13 - Changing the data field summary operation
14 - Summarizing more than one data field
15 - Creating a calculated field
16 - Using PivotTable data in a formula
17 - Drilling down to the underlying data

3. Sorting and Filtering PivotTable Data

18 - Sorting PivotTable data
19 - Creating a custom sort order
20 - Filtering a PivotTable field by selection
21 - Filtering a PivotTable by rule
22 - Filtering a PivotTable using a search filter
23 - Filtering a PivotTable using slicers
24 - Formatting slicers
25 - Filtering with report filter fields
26 - Clearing and reapplying PivotTable filters

4. Formatting PivotTables

27 - Applying a PivotTable style
28 - Creating a PivotTable style
29 - Changing the PivotTable layout
30 - Changing the data field number format

5. Applying Conditional Formats to PivotTables

31 - Highlighting cells by applying a rule
32 - Highlighting the top or bottom values in a PivotTable
33 - Formatting cells using data bars
34 - Formatting cells using color scales
35 - Formatting cells using icon sets
36 - Editing a conditional formatting rule
37 - Controlling how multiple rules are applied
38 - Deleting a conditional formatting rule

6. Creating and Manipulating PivotCharts

39 - Creating a PivotChart
40 - Pivoting a PivotChart
41 - Filtering a PivotChart
42 - Formatting a PivotChart
43 - Changing a PivotChart's layout
44 - Changing a PivotChart's chart type
45 - Adding a trendline to a PivotChart

7. Printing PivotTables

46 - Printing a PivotTable
47 - Setting PivotTable print options
48 - Printing each item on its own page
49 - Printing a PivotChart

8. Manipulating PivotTables

50 - Recording and reviewing an Excel macro
51 - Running an Excel macro
52 - Creating a simple PivotTable presentation kit
53 - Working with PivotTables using keyboard shortcuts

9. Creating PivotTables Using the Data Model

54 - Creating relationships between tables
55 - Creating a PivotTable using the data model
56 - Editing table relationships

Conclusion

57 - Next steps