Excel PivotTables
iii
Filtering by Dates ........................................................................................................................................... 48
Filtering Using Top 10 Filter ........................................................................................................................... 54
Filtering Using Timeline ................................................................................................................................. 56
Clearing the Filters ......................................................................................................................................... 59
8. Filtering data using Slicers ....................................................................................................................... 61
Inserting Slicers ............................................................................................................................................. 62
Filtering with Slicers ...................................................................................................................................... 63
Clearing the Filter in a Slicer .......................................................................................................................... 64
Removing a Slicer .......................................................................................................................................... 64
Slicer Tools ..................................................................................................................................................... 65
Slicer Caption ................................................................................................................................................. 65
Slicer Settings ................................................................................................................................................ 66
Report Connections ....................................................................................................................................... 67
Selection Pane ............................................................................................................................................... 71
9. PivotTable – Nesting ............................................................................................................................... 72
Nesting Order of the Fields............................................................................................................................ 72
Changing the Nesting Order .......................................................................................................................... 73
10. PivotTable – Tools ................................................................................................................................... 75
ANALYZE Commands ..................................................................................................................................... 75
Expanding and Collapsing a Field .................................................................................................................. 75
Grouping and Ungrouping Field Values ......................................................................................................... 78
Grouping by a Date Field ............................................................................................................................... 82
Active Value Field Settings ............................................................................................................................ 84
PivotTable Options ........................................................................................................................................ 85
11. PivotTable – Summarizing Values ........................................................................................................... 87
Sum ................................................................................................................................................................ 87
Value Field Settings ....................................................................................................................................... 87
% of Grand Total ............................................................................................................................................ 89
% of Column Total ......................................................................................................................................... 91
% of Row Total ............................................................................................................................................... 92
Count ............................................................................................................................................................. 93
Average .......................................................................................................................................................... 94
Max ................................................................................................................................................................ 98
Min ................................................................................................................................................................ 99
12. PivotTable ─ Updating Data .................................................................................................................. 101
Updating PivotTable Layout ........................................................................................................................ 101
Refreshing PivotTable Data ......................................................................................................................... 102
Changing the Source Data of a PivotTable .................................................................................................. 103
Changing to External Data Source ............................................................................................................... 104
Deleting a PivotTable ................................................................................................................................... 106
13. PivotTable – Reports ............................................................................................................................. 109
Hierarchies ................................................................................................................................................... 109
Report Filter ................................................................................................................................................. 113
Slicers ........................................................................................................................................................... 115
Timeline in PivotTable ................................................................................................................................. 116
DESIGN Commands ..................................................................................................................................... 116