Excel 2007 PivotTables Recipes: A Problem-Solution Approach

Excel 2007 PivotTables Recipes: A Problem-Solution Approach

by Debra Dalgleish
Released December 2007
Publisher(s): Apress
ISBN: 9781590599204

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Debra Dalgleish, Microsoft Office Excel "Most Valuable Professional" since 2001, and 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. You'll find this book inimitable when facing any new or difficult problem in PivotTables, covering 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 in this collection of recipes 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.

The chapters in this book have been organized into a collection of recipes to take you step by step from the problem you are experiencing to the solution you are aiming for. Without fuss, you'll find clear and precise information to help you assess your situation, whether common or unique, and solve your problem. Working examples of complex PivotTables and numerous PivotTable programming examples will help you solve problems quickly, without the need to digest heavy content.

Table of contents

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

Product information

  • Title: Excel 2007 PivotTables Recipes: A Problem-Solution Approach
  • Author(s): Debra Dalgleish
  • Release date: December 2007
  • Publisher(s): Apress
  • ISBN: 9781590599204