Excel Pivot Tables Recipe Book: A Problem-Solution Approach

Excel Pivot Tables Recipe Book: A Problem-Solution Approach

Released February 2006
Publisher(s): Apress
ISBN: 9781590596296

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

Excel Pivot Tables Recipe Book: A Problem-Solution Approach is for anyone who uses Excel frequently. This book follows a problem-solution format that covers the entire breadth of situations you might encounter when working with PivotTables—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 cannot be found in Excel's Help section, and she carefully explains the most confusing features of PivotTables.

All chapters have been organized into a collection of recipes that take you step-by-step from the problem you are experiencing to the solution you are aiming for. There's no fuss to this book, only clear and precise information to help you assess your situationwhether common or uniqueand solve your problem. The book includes real-world examples of complex PivotTables, as well as numerous PivotTable programming examples.

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 Pivot Table: Accessing the Source Data
    3. 1.3. Planning a Pivot Table: Source Data Fields
    4. 1.4. Planning a Shared Pivot Table
    5. 1.5. Preparing the Source Data: Using an Excel List
    6. 1.6. Preparing the Source Data: Excel List Invalid Field Names
    7. 1.7. Preparing the Source Data: Using a Filtered Excel List
    8. 1.8. Preparing the Source Data: Using an Excel List with Monthly Columns
    9. 1.9. Preparing the Source Data: Using an Excel List with Monthly Columns and Text Fields
    10. 1.10. Preparing the Source Data: Using an Access Query
    11. 1.11. Preparing the Source Data: Using an Access Query with Parameters
    12. 1.12. Preparing the Source Data: Using a Text File
    13. 1.13. Preparing the Source Data: Using an OLAP Cube
    14. 1.14. Preparing the Source Data: Creating an OLAP Cube
    15. 1.15. Preparing the Source Data: Using Multiple Consolidation Ranges
    16. 1.16. Preparing the Source Data: Alternatives to Using Multiple Consolidation Ranges
    17. 1.17. Preparing the Source Data: Setting Up Multiple Consolidation Ranges
    18. 1.18. Preparing the Source Data: Benefits of Using Another PivotTable or PivotChart Report
    19. 1.19. Preparing the Source Data: Problems Caused by Using Another PivotTable or PivotChart Report
    20. 1.20. Preparing the Source Data: Page Field Settings When Using Another PivotTable or PivotChart Report
    21. 1.21. Connecting to the Source Data: Selecting a Large Range in an Excel List
    22. 1.22. Connecting to the Source Data: Using Arrow Keys in an Excel List
    23. 1.23. Connecting to the Source Data: Installing Drivers for External Data
    24. 1.24. Connecting to the Source Data: Creating a New Source for External Data
    25. 1.25. Connecting to the Source Data: Excel Hangs When Using External Data
    26. 1.26. Connecting to the Source Data: Using Pages with Multiple Consolidation Ranges
    27. 1.27. Understanding PivotTable Options: Table Name
    28. 1.28. Understanding PivotTable Options: Table Naming Rules
    29. 1.29. Understanding PivotTable Options: Row and Column Grand Totals
    30. 1.30. Understanding PivotTable Options: AutoFormat Table
    31. 1.31. Understanding PivotTable Options: Subtotal Hidden Page Items
    32. 1.32. Understanding PivotTable Options: Merge Labels
    33. 1.33. Understanding PivotTable Options: Merge Labels Formatting
    34. 1.34. Understanding PivotTable Options: Preserve Formatting
    35. 1.35. Understanding PivotTable Options: Repeat Item Labels on Each Printed Page
    36. 1.36. Understanding PivotTable Options: Page Layout
    37. 1.37. Understanding PivotTable Options: Fields per Column/Fields per Row
    38. 1.38. Understanding PivotTable Options: Error Values
    39. 1.39. Understanding PivotTable Options: Empty Cells
    40. 1.40. Understanding PivotTable Options: Set Print Titles
    41. 1.41. Understanding PivotTable Options: Mark Totals with *
    42. 1.42. Understanding PivotTable Options: Save Data with Table Layout
    43. 1.43. Understanding PivotTable Options: Enable Drill to Details
    44. 1.44. Understanding PivotTable Options: Refresh on Open
    45. 1.45. Understanding PivotTable Options: Refresh Every n Minutes
    46. 1.46. Understanding PivotTable Options: Save Password
    47. 1.47. Understanding PivotTable Options: Background Query
    48. 1.48. Understanding PivotTable Options: Optimize Memory
    49. 1.49. Understanding Pivot Table Layout
  9. CHAPTER 2: Sorting and Grouping Pivot Table Data
    1. 2.1. Sorting a Pivot Field: One Row Field
    2. 2.2. Sorting a Pivot Field: Outer Row Field
    3. 2.3. Sorting a Pivot Field: Inner Row Field
    4. 2.4. Sorting a Pivot Field: Renamed Numeric Items
    5. 2.5. Sorting a Pivot Field: New Items Out of Order
    6. 2.6. Sorting a Pivot Field: Sorting Items Geographically
    7. 2.7. Sorting a Pivot Field: Data Source Order
    8. 2.8. Sorting a Pivot Field When Some Items Won't Sort Correctly
    9. 2.9. Using Top 10 AutoShow: Specifying Top Items Overall
    10. 2.10. Using Top 10 AutoShow: Specifying Items Over a Set Amount
    11. 2.11. Using Top 10 AutoShow: Referring to a Cell Value
    12. 2.12. Grouping: Error Message When Grouping Items in a Date Field
    13. 2.13. Grouping: Error Message When Grouping Items in a Numeric Field
    14. 2.14. Grouping: Error Message When Grouping Items in a Date Field with No Blanks or Text
    15. 2.15. Grouping the Items in a Page Field
    16. 2.16. Grouping the Items in a Page Field: Using an External Source
    17. 2.17. Grouping: Incorrect Error Message About Calculated Items
    18. 2.18. Grouping Text Items
    19. 2.19. Grouping Dates by Week
    20. 2.20. Grouping Dates by Fiscal Quarter
    21. 2.21. Grouping Renamed Numeric Items
    22. 2.22. Grouping Months
    23. 2.23. Grouping Dates Using the Starting Date
    24. 2.24. Grouping Dates by Months and Weeks
    25. 2.25. Grouping the Items in a Pivot Table Based on an Existing Pivot Table
    26. 2.26. Grouping Dates Outside the Range
    27. 2.27. Grouping Nonadjacent Items
  10. CHAPTER 3: Calculations in a Pivot Table
    1. 3.1. Using Summary Functions
    2. 3.2. Using Summary Functions: Default Functions
    3. 3.3. Using Summary Functions: Counting Blank Cells
    4. 3.4. Using Custom Calculations: Difference From
    5. 3.5. Using Custom Calculations: % Of
    6. 3.6. Using Custom Calculations: % Difference From
    7. 3.7. Using Custom Calculations: Running Total
    8. 3.8. Using Custom Calculations: % of Row
    9. 3.9. Using Custom Calculations: % of Column
    10. 3.10. Using Custom Calculations: % of Total
    11. 3.11. Using Custom Calculations: Index
    12. 3.12. Using Formulas: Calculated Field vs. Calculated Item
    13. 3.13. Using Formulas: Adding Items with a Calculated Item
    14. 3.14. Using Formulas: Modifying a Calculated Item
    15. 3.15. Using Formulas: Temporarily Removing a Calculated Item
    16. 3.16. Using Formulas: Permanently Removing a Calculated Item
    17. 3.17. Using Formulas: Using Index Numbers in a Calculated Item
    18. 3.18. Using Formulas: Using Relative Position Numbers in a Calculated Item
    19. 3.19. Using Formulas: Modifying a Calculated Item Formula in a Cell
    20. 3.20. Using Formulas: Creating a Calculated Field
    21. 3.21. Using Formulas: Modifying a Calculated Field
    22. 3.22. Using Formulas: Temporarily Removing a Calculated Field
    23. 3.23. Using Formulas: Permanently Removing a Calculated Field
    24. 3.24. Using Formulas: Determining the Type of Formula
    25. 3.25. Using Formulas: Adding a Calculated Item to a Field With Grouped Items
    26. 3.26. Using Formulas: Calculating the Difference Between Plan and Actual
    27. 3.27. Using Formulas: Correcting the Grand Total for a Calculated Field
    28. 3.28. Using Formulas: Counting Unique Items in a Calculated Field
    29. 3.29. Using Formulas: Correcting Results in a Calculated Field
    30. 3.30. Using Formulas: Listing All Formulas
    31. 3.31. Using Formulas: Accidentally Creating a Calculated Item
    32. 3.32. Using Formulas: Solve Order
  11. CHAPTER 4: Formatting a Pivot Table
    1. 4.1. Using AutoFormat: Applying a Predefined Format
    2. 4.2. Using AutoFormat: Removing an AutoFormat
    3. 4.3. Using AutoFormat: Applying a Standard Table AutoFormat
    4. 4.4. Using the Enable Selection Option
    5. 4.5. Losing Formatting When Refreshing the Pivot Table
    6. 4.6. Retaining the Source Data Formatting
    7. 4.7. Hiding Data Errors on Worksheet
    8. 4.8. Hiding Errors When Printing
    9. 4.9. Showing Zero in Empty Data Cells
    10. 4.10. Using Conditional Formatting in a Pivot Table
    11. 4.11. Creating Custom Number Formats in the Source Data
    12. 4.12. Totaling Hours in a Time Field
    13. 4.13. Displaying Hundredths of Seconds in a Pivot Table
    14. 4.14. Centering Field Labels Vertically
    15. 4.15. Applying an Indented AutoFormat
    16. 4.16. Creating an Indented Format
    17. 4.17. Applying a Tabular AutoFormat
    18. 4.18. Displaying Subtotals at the Top of a Group
    19. 4.19. Separating Field Items with Blank Rows
    20. 4.20. Turning Off Subtotals
    21. 4.21. Repeating Row Headings
    22. 4.22. Retaining Formatting for Temporarily Removed Fields
    23. 4.23. Applying Formatting with the Format Painter
    24. 4.24. Grouping Dates Based on Source Data Formatting
    25. 4.25. Changing Alignment for Merged Labels
    26. 4.26. Displaying Line Breaks in Pivot Table Cells
    27. 4.27. Showing Only the Top Items
    28. 4.28. Freezing Heading Rows
    29. 4.29. Using the Always Display Items Option
    30. 4.30. Applying Number Formatting to Page Fields
    31. 4.31. Displaying Hyperlinks
    32. 4.32. Changing Total Label Text
    33. 4.33. Changing Subtotal Label Text
    34. 4.34. Formatting Date Field Subtotal Labels
    35. 4.35. Showing Additional Subtotals
    36. 4.36. Showing Subtotals for Inner Fields
    37. 4.37. Changing the Grand Total Label Text
    38. 4.38. Changing Labels for Grand Totals
    39. 4.39. Displaying Grand Totals at Top of Pivot Table
    40. 4.40. Hiding Grand Totals
    41. 4.41. Using a Worksheet Template
    42. 4.42. Displaying Multiple Pivot Tables in a Dashboard
  12. CHAPTER 5: Extracting Pivot Table Data
    1. 5.1. Using Drill to Details: Extracting Underlying Data
    2. 5.2. Using Drill to Details: Re-creating Source Data Table
    3. 5.3. Using Drill to Details: Receiving Error Messages in a Non-OLAP Pivot Table
    4. 5.4. Using Drill to Details: Receiving the Saved Without Underlying Data Error Message
    5. 5.5. Using Drill to Details: Formatting
    6. 5.6. Using Drill to Details: New Sheets Are Not Using the Worksheet Template
    7. 5.7. Using Drill to Details: Updating Source Data
    8. 5.8. Using Drill to Details: Outputting Specific Fields
    9. 5.9. Using Drill to Details: Preventing Sheet Creation
    10. 5.10. Using Drill to Details: Deleting Created Sheets
    11. 5.11. Using GetPivotData: Automatically Inserting a Formula
    12. 5.12. Using GetPivotData: Turning Off Automatic Insertion of Formulas
    13. 5.13. Using GetPivotData: Referencing Pivot Tables in Other Workbooks
    14. 5.14. Using GetPivotData: Using Cell References Instead of Text Strings
    15. 5.15. Using GetPivotData: Using Cell References in an OLAP-Based Pivot Table
    16. 5.16. Using GetPivotData: Preventing Cell Reference Errors for Data_Field
    17. 5.17. Using GetPivotData: Preventing Errors in Data_Fields for OLAP-Based Pivot Tables
    18. 5.18. Using GetPivotData: Extracting Data for Blank Field Items
    19. 5.19. Using GetPivotData: Preventing Errors for Missing Field Items
    20. 5.20. Using GetPivotData: Referencing Two Pivot Tables
    21. 5.21. Using GetPivotData: Preventing Errors for Custom Subtotals
    22. 5.22. Using GetPivotData: Preventing Errors for Date References
    23. 5.23. Using GetPivotData: Referring to a Pivot Table
    24. 5.24. Using Show Pages: Creating Pivot Table Copies
    25. 5.25. Using Show Pages: Creating Incorrect Sheet Names
    26. 5.26. Using Show Pages: Not Creating Sheets for All Items
    27. 5.27. Using Show Pages: Not Formatting New Sheets
    28. 5.28. Using Show Pages: Enabling the Show Pages Command
  13. CHAPTER 6: Modifying a Pivot Table
    1. 6.1. Using Page Fields: Shifting Up When Adding Page Fields
    2. 6.2. Using Page Fields: Arranging Fields Horizontally
    3. 6.3. Using Page Fields: Hiding Entries in Page Field Item List
    4. 6.4. Using Page Fields: Hiding Page Field Items in OLAP-Based Pivot Tables
    5. 6.5. Using Page Fields: Including Hidden Items in Total
    6. 6.6. Using Page Fields: Filtering for a Date Range
    7. 6.7. Using Page Fields: Filtering for Future Dates
    8. 6.8. Using Data Fields: Changing Content in the Data Area
    9. 6.9. Using Data Fields: Renaming Fields
    10. 6.10. Using Data Fields: Changing the “Total” Field Name
    11. 6.11. Using Data Fields: Arranging Horizontally
    12. 6.12. Using Data Fields: Restoring Hidden Fields in the Data Field List
    13. 6.13. Using Data Fields: Fixing Source Data Number Fields
    14. 6.14. Using Data Fields: Showing the Source Text Instead of the Count
    15. 6.15. Using Pivot Fields: Adding Comments to Pivot Table Cells
    16. 6.16. Using Pivot Fields: Showing Detail for Inner Fields
    17. 6.17. Using Pivot Fields: Showing Detail for All Items in the Selected Field
    18. 6.18. Using Pivot Fields: Showing Details in OLAP-Based Pivot Tables
    19. 6.19. Using Pivot Fields: Changing Field Names in the Source Data
    20. 6.20. Using Pivot Fields: Clearing Old Items from Field Dropdown Lists
    21. 6.21. Using Pivot Fields: Changing (Blank) Items in Row and Column Fields
    22. 6.22. Using Pivot Items: Showing All Months for Grouped Dates
    23. 6.23. Using Pivot Items: Showing All Field Items
    24. 6.24. Using Pivot Items: Hiding Items with No Data
    25. 6.25. Using Pivot Items: Ignoring Trailing Spaces When Summarizing Data
    26. 6.26. Using a Pivot Table: Deleting the Entire Table
    27. 6.27. Using a Pivot Table: Changing the Automatically Assigned Name
  14. CHAPTER 7: Updating a Pivot Table
    1. 7.1. Using Source Data: Locating and Changing the Source Excel List
    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 List
    5. 7.5. Using Source Data: Changing the Source Excel List
    6. 7.6. Using Source Data: Locating the Source Access File
    7. 7.7. Using Source Data: Trying to Change an OLAP Source
    8. 7.8. Using Source Data: Changing the Data Source Name File
    9. 7.9. Using Source Data: Changing the Source Access File
    10. 7.10. Using Source Data: Changing the Source for a Shared Cache
    11. 7.11. Using Source Data: Changing the Source CSV File
    12. 7.12. Refreshing When a File Opens
    13. 7.13. Preventing a Refresh When a File Opens
    14. 7.14. Refreshing Every 10 Minutes
    15. 7.15. Refreshing All Pivot Tables in a Workbook
    16. 7.16. Reenabling the Refreshing External Data Message
    17. 7.17. Problems Obtaining Data When Refreshing
    18. 7.18. Stopping a Refresh in Progress
    19. 7.19. New Data Doesn't Appear When Refreshing an OLAP Cube
    20. 7.20. Refreshing an OLAP Cube Causes Client Safety Options Error Message
    21. 7.21. Refreshing Pivot Tables with the Same Pivot Cache
    22. 7.22. Refreshing Part of a Pivot Table
    23. 7.23. Enabling Automatic Refresh
    24. 7.24. Refreshing a Pivot Table on a Protected Sheet
    25. 7.25. Refreshing Automatically When Data Changes
    26. 7.26. Refreshing When Two Tables Overlap
    27. 7.27. Refreshing Creates an Error Message After Fields Are Deleted
    28. 7.28. Refreshing Pivot Tables After Queries Have Been Executed
    29. 7.29. Refreshing Creates a Too Many Row or Column Items Error Message
    30. 7.30. Refreshing a Scenario Pivot Table
  15. CHAPTER 8: Securing a Pivot Table
    1. 8.1. Using a Password-Protected Data Source
    2. 8.2. Using a Data Source: No Prompt for Password with OLAP Cube
    3. 8.3. Using a Data Source: Access Database with User-Level Security
    4. 8.4. Protection: Preventing Changes to a Pivot Table
    5. 8.5. Protection: Allowing Changes to a Pivot Table on a Protected Sheet
    6. 8.6. Protection: Refreshing or Creating a Pivot Table
    7. 8.7. Privacy: Preventing Viewing of Others' Data
    8. 8.8. Privacy: Disabling Drill to Details
    9. 8.9. Privacy: Disabling Show Pages
  16. CHAPTER 9: Pivot Table Limits and Performance
    1. 9.1. Understanding Limits: 32,500 Unique Items with External Data Source
    2. 9.2. Understanding Limits: 32,500 Unique Items with Excel Data Source
    3. 9.3. Understanding Limits: Only the First 255 Items Displayed
    4. 9.4. Understanding Limits: 8,000 Items in a Column Field
    5. 9.5. Understanding Limits: Too Many Row or Column Items
    6. 9.6. Understanding Limits: Text Truncated in a Pivot Table Cell
    7. 9.7. Understanding Limits: Number of Records in the Source Data
    8. 9.8. Improving Performance When Changing Layout
    9. 9.9. Improving Performance with the Optimize Memory Option
    10. 9.10. Reducing File Size: Excel Data Source
  17. CHAPTER 10: Publishing a Pivot Table
    1. 10.1. Publishing a Pivot Table: Understanding HTML
    2. 10.2. Publishing Without Interactivity: Preparing the Excel File
    3. 10.3. Publishing with Interactivity: Pivot Charts and Pivot Tables
    4. 10.4. Publishing: Interactive Pivot Table Blocked
    5. 10.5. Using AutoRepublish: The Don't Show This Message Again Option
  18. CHAPTER 11: Printing a Pivot Table
    1. 11.1. Repeating Pivot Table Headings
    2. 11.2. Setting the Print Area to Fit the Pivot Table
    3. 11.3. Compacting the Space Required for Row Labels
    4. 11.4. Printing the Pivot Table for Each Page Item
    5. 11.5. Printing Field Items: Starting Each Item on a New Page
    6. 11.6. Printing Field Items: Keeping All Rows for an Item on One Page
    7. 11.7. Printing Field Items: Including Labels on Each Page
    8. 11.8. Using Report Manager: Printing Pivot Table Data
  19. CHAPTER 12: Pivot Charts
    1. 12.1. Stepping Through the Chart Wizard to Create a Pivot Chart
    2. 12.2. Creating a Normal Chart from Pivot Table Data
    3. 12.3. Restoring Lost Series Formatting
    4. 12.4. Adjusting Hidden Pie Chart Labels
    5. 12.5. Formatting Category Axis Date Labels
    6. 12.6. Changing Pivot Chart Layout Affects Pivot Table
    7. 12.7. Resizing and Moving Pivot Chart Elements
    8. 12.8. Including Grand Totals in a Pivot Chart
    9. 12.9. Converting a Pivot Chart to a Static Chart
    10. 12.10. Using Page Fields: Page Fields with Hidden Items Shows (All)
  20. CHAPTER 13: Programming a Pivot Table
    1. 13.1. Using Sample Code
    2. 13.2. Recording a Macro While Printing a Pivot Table
    3. 13.3. Modifying Recorded Code
    4. 13.4. Showing Top 10 Items over a Set Amount
    5. 13.5. Changing the Summary Function for All Data Fields
    6. 13.6. Hiding Rows with a Zero Total for Calculated Items
    7. 13.7. Hiding All Pivot Field Subtotals
    8. 13.8. Naming and Formatting the Drill to Details Sheet
    9. 13.9. Automatically Deleting Worksheets When Closing a Workbook
    10. 13.10. Changing the Page Field Selection in Related Tables
    11. 13.11. Clearing Old Items from Field Dropdown Lists
    12. 13.12. Hiding All Items in a Pivot Field
    13. 13.13. Changing Content in the Data Area
    14. 13.14. Identifying a Pivot Table's Pivot Cache
    15. 13.15. Changing a Pivot Table's Pivot Cache
    16. 13.16. Identifying the Query Used as the Data Source
    17. 13.17. Refreshing a Pivot Table on a Protected Sheet
    18. 13.18. Refreshing Automatically When Source Data Changes
    19. 13.19. Preventing Selection of (All) in a Page Field
    20. 13.20. Disabling Pivot Field Dropdowns
    21. 13.21. Preventing Layout Changes in a Pivot Table
    22. 13.22. Preventing Changes to the Pivot Table
    23. 13.23.Viewing Information on Pivot Caches
    24. 13.24. Resetting the Print Area to Include the Entire Pivot Table
    25. 13.25. Printing the Pivot Table for Each Page Field
    26. 13.26. Reformatting Pivot Charts After Changing the Pivot Table
    27. 13.27. Scrolling Through Page Field Items on a Pivot Chart
  21. Index

Product information

  • Title: Excel Pivot Tables Recipe Book: A Problem-Solution Approach
  • Author(s):
  • Release date: February 2006
  • Publisher(s): Apress
  • ISBN: 9781590596296