Excel PivotTables and Charts (Mr Spreadsheet)

✍ Scribed by Peter G. Aitken

• Publisher: Wiley
• Year: 2006
• Tongue: English
• Leaves: 289
• Edition: 1
• Category: Library

✦ Synopsis

These are powerful, flexible, and complex tools, and most Excel users never tap their full potential. But you can, with Peter Aitken's step-by-step tutorials, understandable instructions, and detailed explanations. You'll learn to create and use PivotTables and charts from the simplest to the most complex.

This book will help you get more from Excel than ever before.

• Chart Topper # 1 Creating a PivotTable Report
• Chart Topper # 3 Using Special PivotTable(r) Tools
• Chart Topper # 6 Understanding and Using PivotCharts
• Chart Topper # 7 Working with Multidimensional Data
• Chart Topper # 10 Programming PivotTables with VBA

✦ Table of Contents

Cover......Page 1
Contents at a Glance......Page 7
Contents......Page 8
Understanding PivotTables and Charts......Page 14
Tips and Where to Find Them......Page 15
Understanding How PivotTables Work......Page 16
Working with PivotTables......Page 17
Creating a PivotTable Report......Page 18
Creating a PivotTable Report with Multiple Columns......Page 22
Creating a PivotChart......Page 25
Understanding Data Sources for PivotTables......Page 28
Tips and Where to Find Them......Page 29
Using Excel Data from the Same Workbook......Page 30
Using Excel Data from Another Workbook......Page 33
Using Data from Other Sources......Page 36
Using Data from Databases......Page 38
Creating a PivotTable Report from Data in an Access Database......Page 44
Using Multiple Consolidation Ranges......Page 52
Letting Excel Create a Single Page Field......Page 55
Creating Your Own Page Fields......Page 57
Creating a Single-Page Field PivotTable Report from Multiple Consolidation Ranges......Page 60
Creating a Multiple-Page Field PivotTable Report from Multiple Consolidation Ranges......Page 63
Creating a PivotTable with Inner and Outer Row Fields......Page 67
Using PivotTable Tools and Formatting......Page 72
Tips and Where to Find Them......Page 73
Using the Layout Dialog Box in the PivotTable and PivotChart Wizard......Page 74
Customizing the PivotTable Toolbar......Page 76
Using the PivotTable Menu......Page 77
Setting PivotTable Options......Page 78
Formatting PivotTables......Page 82
Applying Formatting to a PivotTable Report......Page 87
Working with PivotTable Components......Page 90
Tips and Where to Find Them......Page 91
Using Drop Areas......Page 92
Using the Row Drop Area......Page 93
Using Multiple Row Fields......Page 95
Using the Column Drop Area......Page 97
Creating a PivotTable with Two Column Fields and Two Row Fields......Page 98
Using the Data Drop Area......Page 100
Using Multiple Data Fields......Page 101
Using the Page Drop Area......Page 103
Creating a PivotTable with Three Page Fields......Page 104
Working with Field Settings......Page 107
Using Settings for Data Fields......Page 108
Using Different Summary Functions......Page 109
Working with Settings for Row and Column Fields......Page 112
Working with Settings for Page Fields......Page 115
Using the Sort and AutoShow Options......Page 118
Setting Advanced Data Field Options......Page 120
More About PivotTable Components......Page 124
Tips and Where to Find Them......Page 125
Working with Calculated Fields and Items......Page 126
Creating and Using a Calculated Field......Page 127
Working with Calculated Items......Page 132
Creating and Using a Calculated Item......Page 133
Showing and Hiding Detail......Page 137
Grouping PivotTable Items......Page 141
Grouping Category Data......Page 145
Understanding and Using PivotCharts......Page 150
Tips and Where to Find Them......Page 151
Creating a PivotChart......Page 152
Understanding the Parts of a Chart......Page 157
Working with the PivotChart Menu......Page 158
Understanding and Changing PivotChart Types......Page 159
Understanding a PivotChart’s Structure......Page 161
Creating a 3-D PivotChart......Page 166
Using PivotTables with Multidimensional Data......Page 172
Tips and Where to Find Them......Page 173
Using Multidimensional Data......Page 174
Understanding Multidimensional Data......Page 175
Multidimensional Data Terminology......Page 184
Creating a Cube File......Page 185
Creating a PivotTable from a Cube File......Page 192
Working with OLAP PivotTables......Page 195
Getting Hard Data from a PivotTable......Page 196
Tips and Where to Find Them......Page 197
Understanding the GETPIVOTDATA Function......Page 198
Referencing PivotTable Cells by Address......Page 200
Using GETPIVOTDATA to Analyze PivotTable Data......Page 204
Copying and Pasting PivotTables......Page 207
PivotTable Alternatives......Page 210
Tips and Where to Find Them......Page 211
Working with Subtotals......Page 212
Nesting Subtotals......Page 215
Hiding and Showing Subtotal Detail......Page 218
Working With Database Functions......Page 219
Defining Criteria......Page 220
Working with Filters......Page 222
Programming PivotTables with VBA......Page 224
Tips and Where to Find Them......Page 225
Understanding the PivotTable Object Model......Page 226
Referencing and Creating PivotTables......Page 228
Creating a New PivotTable in Code......Page 229
Working with the PivotTable Object......Page 234
Adding and Removing Row, Column, and Page Fields......Page 236
Adding and Removing Data Fields......Page 237
Creating a PivotTable Using VBA Code......Page 238
Working with PivotTable Fields......Page 240
Using AutoShow and AutoSort......Page 244
Changing a Field’s Calculation......Page 245
Changing the Display Format of a Field......Page 246
Creating a PivotChart in Code......Page 248
Problems When Using External Data......Page 250
Problems Creating PivotTable Reports......Page 251
Problems with PivotTable Report Layout and Formatting......Page 252
Problems When Using OLAP Data......Page 253
User Changes......Page 254
Object Model Changes......Page 255
Formatting Data Series......Page 258
Formatting Chart Axes......Page 262
Changing Chart and Axis Titles......Page 269
The Chart Legend......Page 270
Using Data Labels......Page 271
Other Chart Options......Page 272
Index......Page 276