About the Technical Reviewer
by Debra Dalgleish
Excel 2007 PivotTables Recipes: A Problem-Solution Approach
- Title Page
- Contents at a Glance
- Contents
- About the Author
- About the Technical Reviewer
- Acknowledgments
- Introduction
- CHAPTER 1: Creating a Pivot Table
- 1.1. Planning a Pivot Table: Getting Started
- 1.2. Planning a Shared Pivot Table
- 1.3. Preparing the Source Data: Using Excel Data
- 1.4. Preparing the Source Data: Creating an Excel Table
- 1.5. Preparing the Source Data: Excel Field Names Not Valid
- 1.6. Preparing the Source Data: Using Filtered Excel Data
- 1.7. Preparing the Source Data: Using an Excel Table with Monthly Columns
- 1.8. Preparing the Source Data: Using an Access Query
- 1.9. Preparing the Source Data: Using a Text File
- 1.10. Preparing the Source Data: Using an OLAP Cube
- 1.11. Creating the Pivot Table: Using Excel Data as the Source
- 1.12. Creating the Pivot Table: Using Excel Data on Separate Sheets
- 1.13. Creating the Pivot Table: Using the PivotTable Field List
- 1.14. Creating the Pivot Table: Changing the Field List Order
- CHAPTER 2: Sorting and Filtering Pivot Table Data
- 2.1. Sorting a Pivot Field: Sorting Row Labels
- 2.2. Sorting a Pivot Field: New Items Out of Order
- 2.3. Sorting a Pivot Field: Sorting Items Left to Right
- 2.4. Sorting a Pivot Field: Sorting Items in a Custom Order
- 2.5. Sorting a Pivot Field: Items Won't Sort Correctly
- 2.6. Filtering a Pivot Field: Filtering Row Label Text
- 2.7. Filtering a Pivot Field: Applying Multiple Filters to a Field
- 2.8. Filtering a Pivot Field: Filtering Row Label Dates
- 2.9. Filtering a Pivot Field: Filtering Values for Row Fields
- 2.10. Filtering a Pivot Field: Filtering for Nonconsecutive Dates
- 2.11. Filtering a Pivot Field: Including New Items in a Manual Filter
- 2.12. Filtering a Pivot Field: Filtering by Selection
- 2.13. Filtering a Pivot Field: Filtering for Top Items
- 2.14. Using Report Filters: Hiding Report Filter Items
- 2.15. Using Report Filters: Filtering for a Date Range
- 2.16. Using Report Filters: Filtering for Future Dates
- CHAPTER 3: Calculations in a Pivot Table
- 3.1. Using Summary Functions: Defaulting to Sum or Count
- 3.2. Using Summary Functions: Counting Blank Cells
- 3.3. Using Custom Calculations: Difference From
- 3.4. Using Custom Calculations: % Of
- 3.5. Using Custom Calculations: % Difference From
- 3.6. Using Custom Calculations: Running Total
- 3.7. Using Custom Calculations: % of Row
- 3.8. Using Custom Calculations: % of Column
- 3.9. Using Custom Calculations: % of Total
- 3.10. Using Custom Calculations: Index
- 3.11. Using Formulas: Calculated Field vs. Calculated Item
- 3.12. Using Formulas: Adding Items With a Calculated Item
- 3.13. Using Formulas: Modifying a Calculated Item
- 3.14. Using Formulas: Removing a Calculated Item
- 3.15. Using Formulas: Using Index Numbers in a Calculated Item
- 3.16. Using Formulas: Modifying a Calculated Item Formula in Cell
- 3.17. Using Formulas: Creating a Calculated Field
- 3.18. Using Formulas: Modifying a Calculated Field
- 3.19. Using Formulas: Removing a Calculated Field
- 3.20. Using Formulas: Determining the Type of Formula
- 3.21. Using Formulas: Adding a Calculated Item to a Field with Grouped Items
- 3.22. Using Formulas: Calculating the Difference Between Amounts
- 3.23. Using Formulas: Correcting the Grand Total for a Calculated Field
- 3.24. Using Formulas: Calculated Field-Count of Unique Items
- 3.25. Using Formulas: Correcting Results in a Calculated Field
- 3.26. Using Formulas: Listing All Formulas
- 3.27. Using Formulas: Accidentally Creating a Calculated Item
- 3.28. Using Formulas: Solve Order
- CHAPTER 4: Formatting a Pivot Table
- 4.1. Using PivotTable Styles: Applying a Predefined Format
- 4.2. Using PivotTable Styles: Removing a PivotTable Style
- 4.3. Using PivotTable Styles: Changing the Default Style
- 4.4. Using PivotTable Styles: Creating a Custom Style
- 4.5. Using PivotTable Styles: Copying a Custom Style to a Different Workbook
- 4.6. Using Themes: Impacting PivotTable Styles
- 4.7. Using the Enable Selection Option
- 4.8. Losing Formatting When Refreshing the Pivot Table
- 4.9. Hiding Error Values on Worksheet
- 4.10. Showing Zero in Empty Values Cells
- 4.11. Hiding Buttons and Labels
- 4.12. Applying Conditional Formatting: Using a Color Scale
- 4.13. Applying Conditional Formatting: Using an Icon Set
- 4.14. Applying Conditional Formatting: Using Bottom 10 Items
- 4.15. Applying Conditional Formatting: Formatting Cells Between Two Values
- 4.16. Applying Conditional Formatting: Formatting Labels in a Date Period
- 4.17. Applying Conditional Formatting: Using Data Bars
- 4.18. Applying Conditional Formatting: Changing the Data Range
- 4.19. Applying Conditional Formatting: Changing the Order of Rules
- 4.20. Removing Conditional Formatting
- 4.21. Creating Custom Number Formats in the Source Data
- 4.22. Changing the Report Layout
- 4.23. Increasing the Row Labels Indentation
- 4.24. Repeating Row Labels
- 4.25. Separating Field Items with Blank Rows
- 4.26. Centering Field Labels Vertically
- 4.27. Changing Alignment for Merged Labels
- 4.28. Displaying Line Breaks in Pivot Table Cells
- 4.29. Freezing Heading Rows
- 4.30. Applying Number Formatting to Report Filter Fields
- 4.31. Displaying Hyperlinks
- 4.32. Changing Subtotal Label Text
- 4.33. Formatting Date Field Subtotal Labels
- 4.34. Changing the Grand Total Label Text
- CHAPTER 5: Grouping and Totaling Pivot Table Data
- 5.1. Grouping: Error Message When Grouping Dates
- 5.2. Grouping: Error Message When Grouping Numbers
- 5.3. Grouping the Items in a Report Filter
- 5.4. Grouping: Error Message About Calculated Items
- 5.5. Grouping Text Items
- 5.6. Grouping Dates by Month
- 5.7. Grouping Dates Using the Starting Date
- 5.8. Grouping Dates by Fiscal Quarter
- 5.9. Grouping Dates by Week
- 5.10. Grouping Dates by Months and Weeks
- 5.11. Grouping Dates in One Pivot Table Affects Another Pivot Table
- 5.12. Grouping Dates Outside the Range
- 5.13. Summarizing Formatted Dates
- 5.14. Creating Multiple Values for a Field
- 5.15. Displaying Multiple Value Fields Vertically
- 5.16. Displaying Subtotals at the Bottom of a Group
- 5.17. Preventing Subtotals from Appearing
- 5.18. Creating Multiple Subtotals
- 5.19. Showing Subtotals for Inner Row Labels
- 5.20. Simulating an Additional Grand Total
- 5.21. Hiding Specific Grand Totals
- 5.22. Totaling Hours in a Time Field
- 5.23. Displaying Hundredths of Seconds
- CHAPTER 6: Modifying a Pivot Table
- 6.1. Using Report Filters: Shifting Up When Adding Report Filters
- 6.2. Using Report Filters: Arranging Fields Horizontally
- 6.3. Using Values Fields: Changing Content in the Values Area
- 6.4. Using Values Fields: Renaming Fields
- 6.5. Using Values Fields: Arranging Vertically
- 6.6. Using Values Fields: Fixing Source Data Number Fields
- 6.7. Using Values Fields: Showing Text in the Values Area
- 6.8. Using Pivot Fields: Adding Comments to Pivot Table Cells
- 6.9. Using Pivot Fields: Collapsing Row Labels
- 6.10. Using Pivot Fields: Collapsing All Items in the Selected Field
- 6.11. Using Pivot Fields: Changing Field Names in the Source Data
- 6.12. Using Pivot Fields: Clearing Old Items from Filter Lists
- 6.13. Using Pivot Fields: Changing (Blank) Row and Column Labels
- 6.14. Using Pivot Items: Showing All Months for Grouped Dates
- 6.15. Using Pivot Items: Showing All Field Items
- 6.16. Using Pivot Items: Hiding Items with No Data
- 6.17. Using Pivot Items: Ignoring Trailing Spaces When Summarizing Data
- 6.18. Using a Pivot Table: Allowing Drag-and-Drop
- 6.19. Using a Pivot Table: Deleting the Entire Table
- CHAPTER 7: Updating a Pivot Table
- 7.1. Using Source Data: Locating the Source Excel Table
- 7.2. Using Source Data: Automatically Including New Data
- 7.3. Using Source Data: Automatically Including New Data in an External Data Range
- 7.4. Using Source Data: Moving the Source Excel Table
- 7.5. Using Source Data: Changing the Source Excel Table
- 7.6. Using Source Data: Locating the Source Access File
- 7.7. Using Source Data: Changing the Source Access File
- 7.8. Using Source Data: Changing the Source CSV File
- 7.9. Refreshing When a File Opens
- 7.10. Preventing a Refresh When a File Opens
- 7.11. Refreshing Every 30 Minutes
- 7.12. Refreshing All Pivot Tables in a Workbook
- 7.13. Stopping a Refresh in Progress
- 7.14. Creating an OLAP-Based Pivot Table Causes Client Safety Options Error Message
- 7.15. Refreshing a Pivot Table on a Protected Sheet
- 7.16. Refreshing When Two Tables Overlap
- 7.17. Refreshing Pivot Tables After Queries Have Been Executed
- 7.18. Refreshing Pivot Tables: Defer Layout Update
- CHAPTER 8: Pivot Table Security, Limits, and Performance
- 8.1. Security: Storing a Database Password
- 8.2. Security: Enabling Data Connections
- 8.3. Protection: Preventing Changes to a Pivot Table
- 8.4. Protection: Disabling Show Report Filter Pages
- 8.5. Privacy: Preventing Viewing of Others' Data
- 8.6. Understanding Limits: 16,384 Items in the Column Area
- 8.7. Understanding Limits: Number of Records in the Source Data
- 8.8. Improving Performance When Changing Layout
- 8.9. Reducing File Size: Excel Data Source
- CHAPTER 9: Printing and Extracting Pivot Table Data
- 9.1. Repeating Pivot Table Headings
- 9.2. Setting the Print Area to Fit the Pivot Table
- 9.3. Printing the Pivot Table for Each Report Filter Item
- 9.4. Printing Field Items: Starting Each Item on a New Page
- 9.5. Printing in Black and White
- 9.6. Extracting Underlying Data for a Value Cell
- 9.7. Re-creating the Source Data Table
- 9.8. Formatting the Extracted Data
- 9.9. Deleting Sheets Created by Extracted Data
- 9.10. Using GetPivotData: Automatically Inserting a Formula
- 9.11. Using GetPivotData: Turning Off Automatic Insertion of Formulas
- 9.12. Using GetPivotData: Referencing Pivot Tables in Other Workbooks
- 9.13. Using GetPivotData: Using Cell References Instead of Text Strings
- 9.14. Using GetPivotData: Using Cell References in an OLAP-Based Pivot Table
- 9.15. Using GetPivotData: Using Cell References for Value Fields
- 9.16. Using GetPivotData: Extracting Data for Blank Field Items
- 9.17. Using GetPivotData: Preventing Errors for Missing Items
- 9.18. Using GetPivotData: Preventing Errors for Custom Subtotals
- 9.19. Using GetPivotData: Preventing Errors for Date References
- 9.20. Using GetPivotData: Referring to a Pivot Table
- 9.21. Creating Customized Pivot Table Copies
- CHAPTER 10: Pivot Charts
- 10.1. Planning and Creating a Pivot Chart
- 10.2. Quickly Creating a Pivot Chart
- 10.3. Creating a Normal Chart from Pivot Table Data
- 10.4. Filtering the Pivot Chart
- 10.5. Changing the Series Order
- 10.6. Changing Pivot Chart Layout Affects Pivot Table
- 10.7. Changing Number Format in Pivot Table Affects Pivot Chart
- 10.8. Formatting the Data Table
- 10.9. Including Grand Totals in a Pivot Chart
- 10.10. Converting a Pivot Chart to a Static Chart
- 10.11. Showing Field Names on the Pivot Chart
- 10.12. Refreshing the Pivot Chart
- 10.13. Creating Multiple Series for Years
- 10.14. Locating the Source Pivot Table
- 10.15. Creating a Combination Pivot Chart
- 10.16. Moving a Pivot Chart from a Chart Sheet
- 10.17. Removing a Pivot Chart
- CHAPTER 11: Programming a Pivot Table
- 11.1. Using Sample Code
- 11.2. Recording a Macro While Printing a Pivot Table
- 11.3. Modifying Recorded Code
- 11.4. Changing the Summary Function for All Value Fields
- 11.5. Naming and Formatting the Show Details Sheet
- 11.6. Automatically Deleting Worksheets When Closing a Workbook
- 11.7. Changing the Report Filter Selection in Related Tables
- 11.8. Removing Filters in a Pivot Field
- 11.9. Changing Content in the Values Area
- 11.10. Identifying a Pivot Table's Pivot Cache
- 11.11. Changing a Pivot Table's Pivot Cache
- 11.12. Refreshing a Pivot Table on a Protected Sheet
- 11.13. Refreshing Automatically When Source Data Changes
- 11.14. Setting a Minimum Width for Data Bars
- 11.15. Preventing Selection of (All) in a Report Filter
- 11.16. Disabling Pivot Field Drop-Downs
- 11.17. Preventing Layout Changes in a Pivot Table
- 11.18. Resetting the Print Area to Include the Entire Pivot Table
- 11.19. Printing the Pivot Table for Each Report Filter Field
- 11.20. Scrolling Through Report Filter Items on a Pivot Chart
- Index
About the Technical Reviewer
ROGER GOVIER is an independent IT consultant based in the UK, where he specializes in developing solutions for clients utilizing Excel worksheet functions and VBA programming.
Following an Honours B.Sc. in Agricultural Economics and Business Management, Roger gained considerable hands-on management experience by running companies both for himself, and for other private and public companies. During this time Roger developed many accounting skills and focused on control through the better utilization of company data.
Roger has been involved with computing from 1980 and, since 1997, most of his work has centered on Excel. Microsoft recently awarded Roger the prestigious Most Valuable Professional (MVP) status as recognition of his Excel skills and help to the community through newsgroups.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.