Pivot Table Data Crunching: Microsoft Excel 2010 (MrExcel Library)

Contents......Page 4
Introduction......Page 14
What Is a Pivot Table?......Page 24
Why Should You Use a Pivot Table?......Page 25
When Should You Use a Pivot Table?......Page 26
Values Area......Page 27
Column Area......Page 28
Pivot Tables Behind the Scenes......Page 29
Limitations of Pivot Table Reports......Page 30
A Word About Compatibility......Page 31
Next Steps......Page 32
Preparing Data for Pivot Table Reporting......Page 34
Avoid Storing Data in Section Headings......Page 35
Avoid Repeating Groups as Columns......Page 36
Summary of Good Data Source Design......Page 37
Creating a Basic Pivot Table......Page 39
Adding Fields to the Report......Page 42
Rearranging a Pivot Table......Page 45
Creating a Report Filter......Page 47
Introducing Slicers......Page 48
Keeping Up with Changes in the Data Source......Page 52
Sharing the Pivot Cache......Page 53
Side Effects of Sharing a Pivot......Page 54
Deferring Layout Updates......Page 55
Relocating a Pivot Table......Page 56
Next Steps......Page 57
3 Customizing a Pivot Table......Page 58
Making Common Cosmetic Changes......Page 59
Applying a Table Style to Restore Gridlines......Page 60
Changing the Number Format to Add Thousands Separators......Page 61
Replacing Blanks with Zeros......Page 62
Changing a Field Name......Page 63
Making Layout Changes......Page 64
Using the New Compact Layout......Page 65
Using the Outline Form Layout......Page 66
Using the Traditional Tabular Layout......Page 67
Controlling Blank Lines, Grand Totals, Subtotals, and Other Settings......Page 70
Customizing the Pivot Table Appearance with Styles and Themes......Page 71
Customizing a Style......Page 73
Choosing a Default Style for Future Pivot Tables......Page 74
Modifying Styles with Document Themes......Page 75
Understanding Why One Blank Cell Causes a Count......Page 76
Using Functions Other Than Count or Sum......Page 77
Suppress Subtotals When You Have Many Row Fields......Page 78
Using Running Total, % of, Rank Options......Page 79
Tracking Percent of Running Total......Page 83
Tracking Percent of Parent Item......Page 84
Showing Rank......Page 85
Track Relative Importance with the Index Option......Page 87
Next Steps......Page 91
4 Grouping, Sorting, and Filtering Pivot Data......Page 92
Grouping Date Fields......Page 93
Including Years When Grouping by Months......Page 94
Grouping Date Fields by Week......Page 95
Grouping Two Date Fields in One Report......Page 96
Looking at the PivotTable Field List......Page 98
Docking and Undocking the PivotTable Field List......Page 99
Rearranging the PivotTable Field List......Page 100
Using the Areas Section Drop-Downs......Page 101
Using the Fields Drop-Down......Page 102
Sorting in a Pivot Table......Page 103
Sorting Using the Sort Icons on the Options Tab......Page 104
Sorting Using the Field List Hidden Drop-Down......Page 105
Using a Manual Sort Sequence......Page 106
Using a Custom List for Sorting......Page 108
Filtering the Pivot Table......Page 109
Using Filters in the Label Areas......Page 110
Filtering Using the Report Filter Area......Page 114
Choosing One Item from a Report Filter......Page 115
Choosing Multiple Items from a Report Filter......Page 116
Using Slicers......Page 117
Next Steps......Page 119
Introducing Calculated Fields and Calculated Items......Page 120
Method 2: Use a Formula Outside the Pivot Table to Create the Calculated Field......Page 121
Method 3: Insert a Calculated Field Directly into the Pivot Table......Page 122
Creating Your First Calculated Field......Page 124
Creating Your First Calculated Item......Page 131
Remembering the Order of Operator Precedence......Page 135
Using Constants......Page 136
Rules Specific to Calculated Fields......Page 137
Rules Specific to Calculated Items......Page 138
Editing and Deleting Pivot Table Calculations......Page 139
Documenting Formulas......Page 140
Next Steps......Page 141
What Is a Pivot Chart...Really?......Page 142
Creating Your First Pivot Chart......Page 143
Placement of Data Fields in a Pivot Table Might Not Be Suited for a Pivot Chart......Page 146
A Few Formatting Limitations Still Exist in Excel 2010......Page 148
Examining Alternatives to Using Pivot Charts......Page 153
Method 1: Turn the Pivot Table into Hard Values......Page 154
Method 4: Use Cells Linked to the Pivot Table as the Source Data......Page 155
Using Conditional Formatting with Pivot Tables......Page 158
Next Steps......Page 164
7 Analyzing Disparate Data Sources with Pivot Tables......Page 166
Using Multiple Consolidation Ranges......Page 167
Analyzing the Anatomy of a Multiple Consolidation Range Pivot Table......Page 172
The Value Field......Page 173
Building a Pivot Table Using External Data Sources......Page 174
Building a Pivot Table with Microsoft Access Data......Page 175
Building a Pivot Table with SQL Server Data......Page 178
Next Steps......Page 181
Sharing a Pivot Table with Other Versions of Office......Page 182
Compatibility Issues Between Excel 2007 and Excel 2010......Page 183
Features Unavailable When a Legacy Pivot Table Is Opened in Excel 2010......Page 185
Using Web Services in Excel 2003......Page 186
Using Save As HTML in Excel 2010 Provides a Static image......Page 187
Publishing Pivot Tables to Excel Services in Excel 2010......Page 188
Viewing Your Excel 2010 Pivot Table on the SkyDrive......Page 194
Next Steps......Page 196
What Is OLAP?......Page 198
Connecting to an OLAP Cube......Page 199
Understanding the Structure of an OLAP Cube......Page 201
Creating Offline Cubes......Page 203
Breaking Out of the Pivot Table Mold with Cube Functions......Page 206
Next Steps......Page 208
Mega-Benefits of PowerPivot......Page 210
Why Is This Free?......Page 211
Benefits of the Server Version of PowerPivot......Page 212
Installing PowerPivot......Page 213
Import a Text File......Page 214
Add Excel Data by Copying and Pasting......Page 218
Add Excel Data By Linking......Page 219
Define Relationships......Page 220
Add Calculated Columns Using DAX......Page 221
Build a Pivot Table......Page 222
Slicers in PowerPivot......Page 224
Some Things Are Different......Page 225
Two Kinds of DAX Calculations......Page 226
DAX Calculations for Calculated Columns......Page 227
Using RELATED() to Base a Column Calculation on Another Table......Page 229
Using DAX to Create New Measures......Page 231
Count Distinct Using DAX......Page 232
When “Filter, Then Calculate” Does Not Work in DAX Measures......Page 235
Mix in Those Amazing Time Intelligence Functions......Page 238
Combination Layouts......Page 241
Report Formatting......Page 243
Next Steps......Page 244
Why Use Macros with Your Pivot Table Reports?......Page 246
Recording Your First Macro......Page 247
Creating a User Interface with Form Controls......Page 249
Altering a Recorded Macro to Add Functionality......Page 251
Next Steps......Page 259
Enabling VBA in Your Copy of Excel......Page 260
Using a File Format That Enables Macros......Page 261
Visual Basic Editor......Page 262
The Macro Recorder......Page 263
Writing Code to Handle Any Size Data Range......Page 264
Using Super-Variables: Object Variables......Page 265
Understanding Versions......Page 266
Concepts Introduced in Excel 2007......Page 267
Building a Pivot Table in Excel VBA......Page 269
Formatting the Pivot Table......Page 271
Dealing with Limitations of Pivot Tables......Page 273
Learning Why You Cannot Move or Change Part of a Pivot Report......Page 274
Controlling Totals......Page 275
Determining Size of a Finished Pivot Table to Convert the Pivot Table to Values......Page 276
Pivot Table 201: Creating a Report Showing Revenue by Category......Page 279
Ensuring Tabular Layout Is Utilized......Page 281
Rolling Daily Dates Up to Years......Page 282
Eliminating Blank Cells......Page 283
Changing Default Number Format......Page 284
Suppressing Subtotals for Multiple Row Fields......Page 285
Copying Finished Pivot Table as Values to a New Workbook......Page 286
Handling Final Formatting......Page 287
Adding Subtotals to Get Page Breaks......Page 288
Putting It All Together......Page 289
Calculating with a Pivot Table......Page 291
Addressing Issues with Two or More Data Fields......Page 292
Using Calculations Other Than Sum......Page 293
Generating a Count Distinct......Page 295
Calculated Data Fields......Page 297
Calculated Items......Page 298
Calculating Groups......Page 299
Using Show Values As to Perform Other Calculations......Page 301
Using AutoShow to Produce Executive Overviews......Page 303
Using ShowDetail to Filter a Recordset......Page 305
Creating Reports for Each Region or Model......Page 307
Using the Conceptual Filters......Page 311
Using the Search Filter......Page 314
Setting Up Slicers to Filter a Pivot Table......Page 315
Filtering an OLAP Pivot Table Using Named Sets......Page 317
Applying a Table Style......Page 320
Changing the Layout......Page 321
Applying a Data Visualization......Page 322
Next Steps......Page 323
Tip 1: Force Pivot Tables to Refresh Automatically......Page 324
Tip 3: Sort Data Items in a Unique Order (Not Ascending or Descending)......Page 325
Tip 4: Turn Pivot Tables into Hard Data......Page 326
Tip 5: Fill the Empty Cells Left by Row Fields......Page 327
Tip 6: Add a Rank Number Field to Your Pivot Table......Page 329
Tip 7: Reduce the Size of Your Pivot Table Reports......Page 331
Tip 8: Create an Automatically Expanding Data Range......Page 333
Tip 9: Comparing Tables with a PivotTable......Page 334
Tip 10: AutoFilter a PivotTable......Page 335
Tip 11: Transposing a Data Set with a PivotTable......Page 336
Tip 12: Forcing Two Number Formats in a Pivot Table......Page 339
Tip 13: Creating a Frequency Distribution with a Pivot Table......Page 341
Tip 14: Use a Pivot Table to Explode a Data Set to Different Tabs......Page 343
Tip 15: Use a Pivot Table to Explode a Data Set to Different Workbooks......Page 344
Next Steps......Page 346
14 Dr. Jekyll and Mr. GetPivotData......Page 348
Turning Off the Evil GetPivotData Problem......Page 349
Preventing GetPivotData by Typing the Formula......Page 351
GetPivotData Is Surely Evil—Turn It Off......Page 352
Why Did Microsoft Force GetPivotData on Us?......Page 353
Using GetPivotData to Solve Pivot Table Annoyances......Page 354
Build an Ugly Pivot Table......Page 355
Build the Shell Report......Page 358
Using GetPivotData to Populate the Shell Report......Page 360
Updating the Report in Future Months......Page 364
Next Steps......Page 366
A: Finding Pivot Table Commands on the Ribbon......Page 368
C......Page 376
D......Page 377
F......Page 378
J-K-L......Page 379
P......Page 380
S......Page 381
V......Page 382
W......Page 383