Excel 2007 PivotTables Recipes (eBook)

Debra Dalgleish is a computer consultant in Mississauga, Ontario, Canada, serving local and international clients. Self-employed since 1985, she has extensive experience in designing complex Excel and Access applications, as well as sophisticated Word forms and documents. She has led hundreds of Microsoft Office corporate training sessions, from beginner to advanced level. In recognition of her contributions to the Excel newsgroups, she has received the Microsoft Office Excel "Most Valuable Professional" award each year since 2001. You can find a wide variety of Excel tips and tutorials, and sample files, on her Contextures website: Contextures.com/tiptech.html.

Contents at a Glance 4
Contents 5
About the Author 13
About the Technical Reviewer 14
Acknowledgments 15
Introduction 16
Creating a Pivot Table 19
1.1. Planning a Pivot Table: Getting Started 19
1.2. Planning a Shared Pivot Table 20
1.3. Preparing the Source Data: Using Excel Data 22
1.4. Preparing the Source Data: Creating an Excel Table 24
1.5. Preparing the Source Data: Excel Field Names Not Valid 26
1.6. Preparing the Source Data: Using Filtered Excel Data 26
1.7. Preparing the Source Data: Using an Excel Table with Monthly Columns 27
1.8. Preparing the Source Data: Using an Access Query 31
1.9. Preparing the Source Data: Using a Text File 32
1.10. Preparing the Source Data: Using an OLAP Cube 32
1.11. Creating the Pivot Table: Using Excel Data as the Source 33
1.12. Creating the Pivot Table: Using Excel Data on Separate Sheets 33
1.13. Creating the Pivot Table: Using the PivotTable Field List 36
1.14. Creating the Pivot Table: Changing the Field List Order 38
Sorting and Filtering Pivot Table Data 39
2.1. Sorting a Pivot Field: Sorting Row Labels 39
2.2. Sorting a Pivot Field: New Items Out of Order 41
2.3. Sorting a Pivot Field: Sorting Items Left to Right 42
2.4. Sorting a Pivot Field: Sorting Items in a Custom Order 43
2.5. Sorting a Pivot Field: Items Won’t Sort Correctly 45
2.6. Filtering a Pivot Field: Filtering Row Label Text 46
2.7. Filtering a Pivot Field: Applying Multiple Filters to a Field 47
2.8. Filtering a Pivot Field: Filtering Row Label Dates 49
2.9. Filtering a Pivot Field: Filtering Values for Row Fields 50
2.10. Filtering a Pivot Field: Filtering for Nonconsecutive Dates 51
2.11. Filtering a Pivot Field: Including New Items in a Manual Filter 52
2.12. Filtering a Pivot Field: Filtering by Selection 53
2.13. Filtering a Pivot Field: Filtering for Top Items 54
2.14. Using Report Filters: Hiding Report Filter Items 55
2.15. Using Report Filters: Filtering for a Date Range 56
2.16. Using Report Filters: Filtering for Future Dates 56
Calculations in a Pivot Table 58
3.1. Using Summary Functions: Defaulting to Sum or Count 58
3.2. Using Summary Functions: Counting Blank Cells 62
3.3. Using Custom Calculations: Difference From 63
3.4. Using Custom Calculations: % Of 65
3.5. Using Custom Calculations: % Difference From 66
3.6. Using Custom Calculations: Running Total 67
3.7. Using Custom Calculations:% of Row 69
3.8. Using Custom Calculations: % of Column 70
3.9. Using Custom Calculations: % of Total 71
3.10. Using Custom Calculations: Index 72
3.11. Using Formulas: Calculated Field vs. Calculated Item 73
3.12. Using Formulas: Adding Items With a Calculated Item 74
3.13. Using Formulas: Modifying a Calculated Item 75
3.14. Using Formulas: Removing a Calculated Item 76
3.15. Using Formulas: Using Index Numbers in a Calculated Item 76
3.16. Using Formulas: Modifying a Calculated Item Formula in Cell 77
3.17. Using Formulas: Creating a Calculated Field 78
3.18. Using Formulas: Modifying a Calculated Field 79
3.19. Using Formulas: Removing a Calculated Field 80
3.20. Using Formulas: Determining the Type of Formula 80
3.21. Using Formulas: Adding a Calculated Item to a Field with Grouped Items 81
3.22. Using Formulas: Calculating the Difference Between Amounts 81
3.23. Using Formulas: Correcting the Grand Total for a Calculated Field 82
3.24. Using Formulas: Calculated Field—Count of Unique Items 83
3.25. Using Formulas: Correcting Results in a Calculated Field 84
3.26. Using Formulas: Listing All Formulas 84
3.27. Using Formulas: Accidentally Creating a Calculated Item 84
3.28. Using Formulas: Solve Order 85
Formatting a Pivot Table 87
4.1. Using PivotTable Styles: Applying a Predefined Format 87
4.2. Using PivotTable Styles: Removing a PivotTable Style 89
4.3. Using PivotTable Styles: Changing the Default Style 90
4.4. Using PivotTable Styles: Creating a Custom Style 90
4.5. Using PivotTable Styles: Copying a Custom Style to a Different Workbook 92
4.6. Using Themes: Impacting PivotTable Styles 93
4.7. Using the Enable Selection Option 94
4.8. Losing Formatting When Refreshing the Pivot Table 95
4.9. Hiding Error Values on Worksheet 95
4.10. Showing Zero in Empty Values Cells 96
4.11. Hiding Buttons and Labels 97
4.12. Applying Conditional Formatting: Using a Color Scale 97
4.13. Applying Conditional Formatting: Using an Icon Set 98
4.14. Applying Conditional Formatting: Using Bottom 10 Items 100
4.15. Applying Conditional Formatting: Formatting Cells Between Two Values 101
4.16. Applying Conditional Formatting: Formatting Labels in a Date Period 102
4.17. Applying Conditional Formatting: Using Data Bars 103
4.18. Applying Conditional Formatting: Changing the Data Range 105
4.19. Applying Conditional Formatting: Changing the Order of Rules 107
4.20. Removing Conditional Formatting 108
4.21. Creating Custom Number Formats in the Source Data 108
4.22. Changing the Report Layout 109
4.23. Increasing the Row Labels Indentation 110
4.24. Repeating Row Labels 111
4.25. Separating Field Items with Blank Rows 112
4.26. Centering Field Labels Vertically 112
4.27. Changing Alignment for Merged Labels 113
4.28. Displaying Line Breaks in Pivot Table Cells 113
4.29. Freezing Heading Rows 114
4.30. Applying Number Formatting to Report Filter Fields 114
4.31. Displaying Hyperlinks 114
4.32. Changing Subtotal Label Text 115
4.33. Formatting Date Field Subtotal Labels 115
4.34. Changing the Grand Total Label Text 116
Grouping and Totaling Pivot Table Data 117
5.1. Grouping: Error Message When Grouping Dates 117
5.2. Grouping: Error Message When Grouping Numbers 118
5.3. Grouping the Items in a Report Filter 120
5.4. Grouping: Error Message About Calculated Items 121
5.5. Grouping Text Items 122
5.6. Grouping Dates by Month 123
5.7. Grouping Dates Using the Starting Date 123
5.8. Grouping Dates by Fiscal Quarter 124
5.9. Grouping Dates by Week 124
5.10. Grouping Dates by Months and Weeks 126
5.11. Grouping Dates in One Pivot Table Affects Another Pivot Table 126
5.12. Grouping Dates Outside the Range 128
5.13. Summarizing Formatted Dates 128
5.14. Creating Multiple Values for a Field 129
5.15. Displaying Multiple Value Fields Vertically 130
5.16. Displaying Subtotals at the Bottom of a Group 131
5.17. Preventing Subtotals from Appearing 132
5.18. Creating Multiple Subtotals 133
5.19. Showing Subtotals for Inner Row Labels 134
5.20. Simulating an Additional Grand Total 135
5.21. Hiding Specific Grand Totals 136
5.22. Totaling Hours in a Time Field 137
5.23. Displaying Hundredths of Seconds 137
Modifying a Pivot Table 138
6.1. Using Report Filters: Shifting Up When Adding Report Filters 138
6.2. Using Report Filters: Arranging Fields Horizontally 139
6.3. Using Values Fields: Changing Content in the Values Area 141
6.4. Using Values Fields: Renaming Fields 142
6.5. Using Values Fields: Arranging Vertically 142
6.6. Using Values Fields: Fixing Source Data Number Fields 143
6.7. Using Values Fields: Showing Text in the Values Area 143
6.8. Using Pivot Fields: Adding Comments to Pivot Table Cells 144
6.9. Using Pivot Fields: Collapsing Row Labels 145
6.10. Using Pivot Fields: Collapsing All Items in the Selected Field 146
6.11. Using Pivot Fields: Changing Field Names in the Source Data 147
6.12. Using Pivot Fields: Clearing Old Items from Filter Lists 147
6.13. Using Pivot Fields: Changing (Blank) Row and Column Labels 148
6.14. Using Pivot Items: Showing All Months for Grouped Dates 149
6.15. Using Pivot Items: Showing All Field Items 149
6.16. Using Pivot Items: Hiding Items with No Data 150
6.17. Using Pivot Items: Ignoring Trailing Spaces When Summarizing Data 151
6.18. Using a Pivot Table: Allowing Drag-and-Drop 152
6.19. Using a Pivot Table: Deleting the Entire Table 152
Updating a Pivot Table 154
7.1. Using Source Data: Locating the Source Excel Table 154
7.2. Using Source Data: Automatically Including New Data 156
7.3. Using Source Data: Automatically Including New Data in an External Data Range 158
7.4. Using Source Data: Moving the Source Excel Table 159
7.5. Using Source Data: Changing the Source Excel Table 160
7.6. Using Source Data: Locating the Source Access File 161
7.7. Using Source Data: Changing the Source Access File 161
7.8. Using Source Data: Changing the Source CSV File 162
7.9. Refreshing When a File Opens 164
7.10. Preventing a Refresh When a File Opens 164
7.11. Refreshing Every 30 Minutes 165
7.12. Refreshing All Pivot Tables in a Workbook 166
7.13. Stopping a Refresh in Progress 166
7.14. Creating an OLAP-Based Pivot Table Causes Client Safety Options Error Message 167
7.15. Refreshing a Pivot Table on a Protected Sheet 167
7.16. Refreshing When Two Tables Overlap 168
7.17. Refreshing Pivot Tables After Queries Have Been Executed 168
7.18. Refreshing Pivot Tables: Defer Layout Update 169
Pivot Table Security, Limits, and Performance 170
8.1. Security: Storing a Database Password 170
8.2. Security: Enabling Data Connections 171
8.3. Protection: Preventing Changes to a Pivot Table 172
8.4. Protection: Disabling Show Report Filter Pages 175
8.5. Privacy: Preventing Viewing of Others’Data 175
8.6. Understanding Limits: 16,384 Items in the Column Area 177
8.7. Understanding Limits: Number of Records in the Source Data 177
8.8. Improving Performance When Changing Layout 178
8.9. Reducing File Size: Excel Data Source 179
Printing and Extracting Pivot Table Data 181
9.1. Repeating Pivot Table Headings 181
9.2. Setting the Print Area to Fit the Pivot Table 184
9.3. Printing the Pivot Table for Each Report Filter Item 184
9.4. Printing Field Items: Starting Each Item on a New Page 186
9.5. Printing in Black and White 187
9.6. Extracting Underlying Data for a Value Cell 187
9.7. Re-creating the Source Data Table 188
9.8. Formatting the Extracted Data 189
9.9. Deleting Sheets Created by Extracted Data 190
9.10. Using GetPivotData: Automatically Inserting a Formula 190
9.11. Using GetPivotData: Turning Off Automatic Insertion of Formulas 192
9.12. Using GetPivotData: Referencing Pivot Tables in OtherWorkbooks 193
9.13. Using GetPivotData: Using Cell References Instead of Text Strings 193
9.14. Using GetPivotData: Using Cell References in an OLAP- Based Pivot Table 194
9.15. Using GetPivotData: Using Cell References for Value Fields 195
9.16. Using GetPivotData: Extracting Data for Blank Field Items 196
9.17. Using GetPivotData: Preventing Errors for Missing Items 196
9.18. Using GetPivotData: Preventing Errors for Custom Subtotals 197
9.19. Using GetPivotData: Preventing Errors for Date References 199
9.20. Using GetPivotData: Referring to a Pivot Table 200
9.21. Creating Customized Pivot Table Copies 201
Pivot Charts 203
10.1. Planning and Creating a Pivot Chart 203
10.2. Quickly Creating a Pivot Chart 206
10.3. Creating a Normal Chart from Pivot Table Data 208
10.4. Filtering the Pivot Chart 209
10.5. Changing the Series Order 211
10.6. Changing Pivot Chart Layout Affects Pivot Table 211
10.7. Changing Number Format in Pivot Table Affects Pivot Chart 212
10.8. Formatting the Data Table 212
10.9. Including Grand Totals in a Pivot Chart 212
10.10. Converting a Pivot Chart to a Static Chart 213
10.11. Showing Field Names on the Pivot Chart 213
10.12. Refreshing the Pivot Chart 215
10.13. Creating Multiple Series for Years 215
10.14. Locating the Source Pivot Table 216
10.15. Creating a Combination Pivot Chart 217
10.16. Moving a Pivot Chart from a Chart Sheet 217
10.17. Removing a Pivot Chart 218
Programming a Pivot Table 219
11.1. Using Sample Code 219
11.2. Recording a Macro While Printing a Pivot Table 222
11.3. Modifying Recorded Code 226
11.4. Changing the Summary Function for All Value Fields 227
11.5. Naming and Formatting the Show Details Sheet 228
11.6. Automatically Deleting Worksheets When Closing a Workbook 230
11.7. Changing the Report Filter Selection in Related Tables 232
11.8. Removing Filters in a Pivot Field 234
11.9. Changing Content in the Values Area 236
11.10. Identifying a Pivot Table’s Pivot Cache 237
11.11. Changing a Pivot Table’s Pivot Cache 238
11.12. Refreshing a Pivot Table on a Protected Sheet 239
11.13. Refreshing Automatically When Source Data Changes 240
11.14. Setting a Minimum Width for Data Bars 240
11.15. Preventing Selection of (All) in a Report Filter 241
11.16. Disabling Pivot Field Drop-Downs 242
11.17. Preventing Layout Changes in a Pivot Table 243
11.18. Resetting the Print Area to Include the Entire Pivot Table 245
11.19. Printing the Pivot Table for Each Report Filter Field 246
11.20. Scrolling Through Report Filter Items on a Pivot Chart 247
Index 251