Excel: PivotTables in Depth

Excel: PivotTables in Depth

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 3h 50m | 733 MB

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.

Table of Contents

1 Apply the power of Excel PivotTables
2 What you should know before starting
3 The changing Excel interface

1. Creating and Pivoting PivotTables
4 Introduction to PivotTables
5 Format data for use in a PivotTable
6 Create a PivotTable
7 Pivot a PivotTable
8 Configure a PivotTable
9 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 Filter a PivotTable using a Timeline
21 Sort PivotTable data
22 Create a custom sort order
23 Filter a PivotTable field by selection
24 Filter a PivotTable by rule
25 Filter a PivotTable using a search filter
26 Filter a PivotTable using slicers
27 Format slicers
28 Filter a PivotTable with report filter fields
29 Clear and reapply PivotTable filters

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

62 Additional resources