Improve your PivotTables with Excel

Part 1 : Fundamentals

Chapter 1 : Creating  a PivotTable

1.1 What is a PivotTable ?

1.2 Structure of the source table

1.3 To create a PivotTable

1.4 Refresh the PivotTable

1.5 See the detail lines of a result

1.6 A horizontal example

1.7 The filters

1.8 The Design menu

Chapter 2 : The available calculations

2.1 Using calculation functions

2.2 Counting acronyms

2.3 Grouping some dates

2.4 Slicer and Timeline

2.4.1 The Slicer

2.4.2 The TimeLine

2.5 Counting with formulas

2.5.1 Using COUNTIF and COUNTIFS

2.5.2 Using SUMPRODUCT

2.6 Immediate calculations

2.6.1 Display the % of Grand Total

2.6.2 Add the Sum besides the %of Grand Total

2.6.3 The SubTotals and Design menu

2.6.4 Elaborate calculations in a PivotTable

2.7 Training with calculations 

2.7.1 Show the sum of amounts per country with a slicer

2.7.2 Display the average of amounts per year and month

2.7.3 Display the % of the GrandTotal

2.7.4 Display the % of Column Total

2.7.5 Display the % of Row Total

2.7.6 Display the % of

2.7.7 Display the % of Parent Row Total

2.7.8 Display the % of Parent Column Total

2.7.9 Display the % of Parent Total

2.7.10 Display the Difference From

2.7.11 Display the % Difference From

2.7.12 Display the Running Total In

2.7.13 Display the % Running Total In

2.7.14 Rank Smallest to Largest

2.7.15 Rank Largest to the smallest

2.7.16 Display the Index

Chapter 3 : Handling several fields

3.1 Creation of a computed field

3.2 Deleting a computed field

3.3 Using IF in a computed field

3.4 Adding a calculated element

Chapter 4 : Going further with options

4.1 The global options

4.1.1 General Options

4.1.2 Formulas Options

4.1.3 Data Options

4.1.4 Refresh a PivotTable at the opening of the book

4.2 Local options

4.2.1 Layout & Format options

4.2.2 Totals & Filters options

4.2.3 Display options

4.2.4 Printing options

4.2.5 Data options and cache memory

4.3 Using GetPivotData Function

4.3.1 Recover a value

4.3.2 A successful copy with GetPivotData

4.3.3 Deactivation of GetPivotData function

Part 2 : Relationships and Macros

Chapter 5 : The Format as Table

5.1 A problem of rows and columns

5.1.1 Using the Format as Table

5.1.2 The automatic Total Row

5.1.3 A PivotTable based upon the table with a slicer and its properties

Chapter 6 : VlookUp and Data Model

6.1 The VlookUp function

6.1.1 Syntax

6.1.2 Several tabs and VlookUp

6.1.3 VlookUp using a PivotTable

6.2 The Data Model

6.2.1 Tables for ranges of cells

6.2.2 A PivotTable based on a relationship

6.2.3 How to modify a relationship between two tables ?

6.2.4 The Workbook Data Model

Chapter 7 : Macros and PivotTables

7.1 The menu Developer

7.2 Managing macros

7.2.1 Record a macro

7.2.2 Running a macro

7.2.3 Delete a macro

7.2.4 Modify a macro

7.3 Macros can manage PivotTables

7.3.1 A macro to refresh an existing PivotTable

7.3.2 A macro to refresh all PivotTables

7.3.3 A recorded macro to count

7.3.4 Creating a PivotTable from a cache memory

7.3.5 A macro to create an empty PivotTable

7.3.6 A macro to do calculation in a PivotTable