Contents

Course Objectives: 6

Pivot Table Overview.. 9

Creating a Pivot Table. 10

Rearranging a Pivot Table. 14

Rearrange data in a Pivot Table by right-clicking a field name. 15

Rearrange data in a Pivot Table by dragging a field name into the Pivot Table. 15

Rearrange data in an existing Pivot Table. 15

To Add a Field to the Pivot Table. 17

To Remove a Field from the Pivot Table. 18

Review Questions - Section 1. 19

Modify a Pivot Table. 20

Filtering/Hiding Data. 20

Hide a Single Field using the Pivot Table Fields Pane. 22

Hiding an Item using Selected Items. 22

Slicers. 25

Slicers Properties. 28

Sorting and Grouping. 30

Creating A Sort Group. 31

Drill Down – the Detail 33

Creating Multiple Reports from one Pivot Table: 35

Grouping. 37

Ungroup. 39

................................ 39

Review - Feedback Exercise!. 40

Grouping Dates. 42

Review Questions -Section 2. 47

Review/Feedback Exercise. 48

PivotTable Calculations. 50

Summarizing A Value Field. 50

Formatting Calculations. 51

Subtotals and Totals. 52

Totals. 54

Using Excel preset calculations. 55

Create calculated fields within a Pivot Table. 59

Create a calculated item within a Pivot Table. 63

Modifying/Deleting Calculated Fields and Items. 66

Formula List. 66

Auto Formatting a Pivot Table. 68

PivotTable Styles Gallery. 68

To remove a Pivot Table style. 69

To Format a PivotTable. 69

To Format individual parts of the PivotTable. 69

Subtotals and Totals. 72

Review Questions – Section 3. 73

Pivot Table Charts. 74

Creating a Pivot Chart from Excel Data. 74

Creating Pivot Chart from a Pivot Table. 77

Pivot Charts in earlier Excel versions. 77

Review Questions -Section 4. 79

Options. 80

Data Source. 82

Automatically update your Pivot Table with new data. 83

Create a Table. 83

Create a Dynamic Range. 83

GET PIVOT DATA.. 85

Review Questions -Section 5. 87

Review/Feedback Exercise. 88

Glossary. 91

Answers to Review Questions. 92

INDEX. 98

CPE Exam.. 100