Excel PivotTables and PivotCharts: Your visual blueprint for creating dynamic spreadsheets

Excel PivotTables and PivotCharts: Your visual blueprint for creating dynamic spreadsheets, 2nd Edition......Page 1
Credits......Page 5
Author’s Acknowledgments......Page 6
How to Use This Visual Blueprint Book......Page 7
TABLE OF CONTENTS......Page 8
Understanding Data Analysis......Page 14
Introducing the PivotTable......Page 16
Learn PivotTable Benefits......Page 18
Learn When to Use PivotTables......Page 20
Explore PivotTable Features......Page 22
Introducing the PivotChart......Page 24
Prepare Your Worksheet Data......Page 26
Create a Table for a PivotTable Report......Page 28
Build a PivotTable from an Excel Table......Page 30
Build a PivotTable from an Excel Range......Page 32
Recreate an Existing PivotTable......Page 34
Turn the PivotTable Field List On and Off......Page 36
Customize the PivotTable Field List......Page 37
Select PivotTable Items......Page 38
Remove a PivotTable Field......Page 40
Refresh PivotTable Data......Page 42
Display the Details Behind PivotTable Data......Page 44
Create a Chart from PivotTable Data......Page 46
Enable the Classic PivotTable Layout......Page 50
Add Multiple Fields to the Row or Column Area......Page 52
Add Multiple Fields to the Data Area......Page 54
Add Multiple Fields to the Report Filter......Page 56
Publish a PivotTable to a Web Page......Page 58
Convert a PivotTable to Regular Data......Page 62
Delete a PivotTable......Page 64
Move a Field to a Different Area......Page 66
Change the Order of Fields within an Area......Page 68
Change the Report Layout......Page 70
Sort PivotTable Data with AutoSort......Page 72
Move Row and Column Items......Page 74
Group Numeric Values......Page 76
Group Date and Time Values......Page 78
Group Text Values......Page 80
Hide Group Details......Page 82
Show Group Details......Page 83
Ungroup Values......Page 84
Apply a Report Filter......Page 86
Change the Report Filter Layout......Page 88
Filter Row or Column Items......Page 90
Filter PivotTable Values......Page 92
Hide Items in a Row or Column Field......Page 94
Use Search to Display Multiple Items......Page 96
Show Hidden Items in a Row or Column Field......Page 98
Filter a PivotTable with a Slicer......Page 100
Connect a PivotTable to an Existing Slicer......Page 102
Connect a Slicer to Multiple PivotTables......Page 104
Rename a PivotTable Field......Page 106
Rename a PivotTable Item......Page 108
Format a PivotTable Cell......Page 110
Apply a Numeric Format to PivotTable Data......Page 112
Apply a Date Format to PivotTable Data......Page 114
Apply a Conditional Format to PivotTable Data......Page 116
Show Items with No Data......Page 118
Exclude Items from a Report Filter......Page 120
Repeat Item Labels in Fields......Page 122
Understanding PivotChart Limitations......Page 124
Create a PivotChart from a PivotTable......Page 125
Create a PivotChart beside a PivotTable......Page 126
Create a PivotChart from an Excel Table......Page 128
Move a PivotChart to another Sheet......Page 130
Filter a PivotChart......Page 132
Change the PivotChart Type......Page 134
Sort the PivotChart......Page 136
Add PivotChart Titles......Page 138
Move the PivotChart Legend......Page 140
Display a Data Table with the PivotChart......Page 142
Apply a PivotTable Quick Style......Page 144
Create a Custom PivotTable Quick Style......Page 146
Preserve PivotTable Formatting......Page 148
Rename the PivotTable......Page 150
Turn Off Grand Totals......Page 152
Merge Item Labels......Page 154
Specify Characters for Errors and Empty Cells......Page 156
Protect a PivotTable......Page 158
Change the PivotTable Summary Calculation......Page 160
Create a Difference Summary Calculation......Page 162
Create a Percentage Summary Calculation......Page 164
Create a Running Total Summary Calculation......Page 166
Create an Index Summary Calculation......Page 168
Turn Off Subtotals for a Field......Page 170
Display Multiple Subtotals for a Field......Page 172
Introducing Custom Calculations......Page 174
Understanding Custom Calculation Limitations......Page 175
Insert a Custom Calculated Field......Page 176
Insert a Custom Calculated Item......Page 178
Edit a Custom Calculation......Page 180
Change the Solve Order of Calculated Items......Page 182
List Your Custom Calculations......Page 184
Delete a Custom Calculation......Page 185
Introducing Formulas......Page 186
Understanding Formula Types......Page 188
Introducing Worksheet Functions......Page 190
Understanding Function Types......Page 192
Build a Function......Page 194
Build a Formula......Page 196
Work with Custom Numeric and Date Formats......Page 198
Understanding Microsoft Query......Page 200
Define a Data Source......Page 202
Start Microsoft Query......Page 206
Tour the Microsoft Query Window......Page 207
Add a Table to the Query......Page 208
Add Fields to the Query......Page 210
Filter the Records with Query Criteria......Page 212
Sort the Query Records......Page 214
Return the Query Results......Page 216
Understanding External Data......Page 218
Import Data from a Data Source......Page 220
Import Data from an Access Table......Page 222
Import Data from a Word Table......Page 224
Import Data from a Text File......Page 226
Import Data from a Web Page......Page 230
Import Data from an XML File......Page 232
Create a PowerPivot Data Connection......Page 234
Refresh Imported Data......Page 236
Create a PivotTable from Multiple Consolidation Ranges......Page 238
Create a PivotTable from an Existing PivotTable......Page 242
Create a PivotTable from External Data......Page 244
Create a PivotTable Using PowerPivot......Page 248
Automatically Refresh a PivotTable that Uses External Data......Page 250
Save Your Password with an External Data Connection......Page 252
Export an Access PivotTable Form to Excel......Page 254
Reduce the Size of PivotTable Workbooks......Page 256
Use a PivotTable Value in a Formula......Page 257
Understanding OLAP......Page 258
Create an OLAP Cube Data Source......Page 260
Create a PivotTable from an OLAP Cube......Page 264
Show and Hide Details for Dimensions and Levels......Page 266
Hide Levels......Page 268
Display Selected Levels and Members......Page 269
Display Multiple Report Filter Items......Page 270
Include Hidden Items in PivotTable Totals......Page 272
Performing What-if Analysis on the PivotTable......Page 274
Create an Offline OLAP Cube......Page 276
Open the VBA Editor......Page 280
Add a Macro to a Module......Page 282
Run a Macro......Page 284
Set Macro Security......Page 286
Assign a Shortcut Key to a Macro......Page 290
APPENDIX: GLOSSARY OF PIVOTTABLE TERMS......Page 292
INDEX......Page 296