Dalgleish Excel 2007 PivotTables Recipes

A Problem-Solution Approach
1. ed
ISBN: 978-1-4302-0504-3
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 272 Seiten

In this book, Debra Dalgleish, Microsoft Office Excel MVP since 2001 as well as an expert and trainer in Excel, brings together a one-stop resource for anyone curious about representing, analyzing, and using their data with PivotTables and PivotCharts. She covers the entire breadth of situations you could ever encounter, from planning and creating, to formatting and extracting data, to maximizing performance and troubleshooting. The author presents tips and techniques that can't be found in Excel's Help section, while carefully explaining the most confusing features of PivotTables to help you realize their powerful potential.

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.

Autoren/Hrsg.

Dalgleish, Debra

Inhaltsverzeichnis

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