How to Use This Book v
1 Understanding Pivottables and Pivotcharts 2
Understanding Data Analysis 2
Introducing the PivotTable 4
Learn PivotTable Benefits 6
Learn When to Use PivotTables 8
Explore PivotTable Features 10
Introducing the PivotChart 12
2 Building a Pivottable 14
Prepare Your Worksheet Data 14
Create a Table for a PivotTable Report 16
Build a PivotTable from an Excel Table 18
Build a PivotTable from an Excel Range 20
Recreate an Existing PivotTable 22
3 Manipulating Your Pivottable 24
Turn the PivotTable Field List On and Off 24
Customize the PivotTable Field List 25
Select PivotTable Items 26
Remove a PivotTable Field 28
Refresh PivotTable Data 30
Display the Details Behind PivotTable Data 32
Create a Chart from PivotTable Data 34
Enable the Classic PivotTable Layout 38
Add Multiple Fields to the Row or Column Area 40
Add Multiple Fields to the Data Area 42
Add Multiple Fields to the Report Filter 44
Publish a PivotTable to a Web Page 46
Convert a PivotTable to Regular Data 50
Delete a PivotTable 52
4 Changing the Pivottable View 54
Move a Field to a Different Area 54
Change the Order of Fields within an Area 56
Change the Report Layout 58
Sort PivotTable Data with AutoSort 60
Move Row and Column Items 62
Group Numeric Values 64
Group Date and Time Values 66
Group Text Values 68
Hide Group Details 70
Show Group Details 71
Ungroup Values 72
5 Filtering a Pivottable 74
Apply a Report Filter 74
Change the Report Filter Layout 76
Filter Row or Column Items 78
Filter PivotTable Values 80
Hide Items in a Row or Column Field 82
Use Search to Display Multiple Items 84
Show Hidden Items in a Row or Column Field 86
Filter a PivotTable with a Slicer 88
Connect a PivotTable to an Existing Slicer 90
Connect a Slicer to Multiple PivotTables 92
6 Customizing Pivottable Fields 94
Rename a PivotTable Field 94
Rename a PivotTable Item 96
Format a PivotTable Cell 98
Apply a Numeric Format to PivotTable Data 100
Apply a Date Format to PivotTable Data 102
Apply a Conditional Format to PivotTable Data 104
Show Items with No Data 106
Exclude Items from a Report Filter 108
Repeat Item Labels in Fields 110
7 Creating a Pivotchart 112
Understanding PivotChart Limitations 112
Create a PivotChart from a PivotTable 113
Create a PivotChart beside a PivotTable 114
Create a PivotChart from an Excel Table 116
Move a PivotChart to another Sheet 118
Filter a PivotChart 120
Change the PivotChart Type 122
Sort the PivotChart 124
Add PivotChart Titles 126
Move the PivotChart Legend 128
Display a Data Table with the PivotChart 130
8 Setting Pivottable Options 132
Apply a PivotTable Quick Style 132
Create a Custom PivotTable Quick Style 134
Preserve PivotTable Formatting 136
Rename the PivotTable 138
Turn Off Grand Totals 140
Merge Item Labels 142
Specify Characters for Errors and Empty Cells 144
Protect a PivotTable 146
9 Performing Pivottable Calculations 148
Change the PivotTable Summary Calculation 148
Create a Difference Summary Calculation 150
Create a Percentage Summary Calculation 152
Create a Running Total Summary Calculation 154
Create an Index Summary Calculation 156
Turn Off Subtotals for a Field 158
Display Multiple Subtotals for a Field 160
10 Creating Custom Pivottable Calculations 162
Introducing Custom Calculations 162
Understanding Custom Calculation Limitations 163
Insert a Custom Calculated Field 164
Insert a Custom Calculated Item 166
Edit a Custom Calculation 168
Change the Solve Order of Calculated Items 170
List Your Custom Calculations 172
Delete a Custom Calculation 173
11 Building Formulas for Pivottables 174
Introducing Formulas 174
Understanding Formula Types 176
Introducing Worksheet Functions 178
Understanding Function Types 180
Build a Function 182
Build a Formula 184
Work with Custom Numeric and Date Formats 186
12 Using Microsoft Query with Pivottables 188
Understanding Microsoft Query 188
Define a Data Source 190
Start Microsoft Query 194
Tour the Microsoft Query Window 195
Add a Table to the Query 196
Add Fields to the Query 198
Filter the Records with Query Criteria 200
Sort the Query Records 202
Return the Query Results 204
13 Importing Data for Pivottables 206
Understanding External Data 206
Import Data from a Data Source 208
Import Data from an Access Table 210
Import Data from a Word Table 212
Import Data from a Text File 214
Import Data from a Web Page 218
Import Data from an XML File 220
Create a PowerPivot Data Connection 222
Refresh Imported Data 224
14 Building More Advanced Pivottables 226
Create a PivotTable from Multiple Consolidation Ranges 226
Create a PivotTable from an Existing PivotTable 230
Create a PivotTable from External Data 232
Create a PivotTable Using PowerPivot 236
Automatically Refresh a PivotTable that Uses External Data 238
Save Your Password with an External Data Connection 240
Export an Access PivotTable Form to Excel 242
Reduce the Size of PivotTable Workbooks 244
Use a PivotTable Value in a Formula 245
15 Building a Pivottable From An Olap Cube 246
Understanding OLAP 246
Create an OLAP Cube Data Source 248
Create a PivotTable from an OLAP Cube 252
Show and Hide Details for Dimensions and Levels 254
Hide Levels 256
Display Selected Levels and Members 257
Display Multiple Report Filter Items 258
Include Hidden Items in PivotTable Totals 260
Performing What-if Analysis on the PivotTable 262
Create an Offline OLAP Cube 264
16 Learning Vba Basics for Pivottables 268
Open the VBA Editor 268
Add a Macro to a Module 270
Run a Macro 272
Set Macro Security 274
Assign a Shortcut Key to a Macro 278
Appendix: Glossary of Pivottable Terms 280
