Pivot Table Data Crunching

Become a savvy Microsoft Excel user. Pivot tables are a great feature in Excel that help you organize and analyze data, but not many Excel users know how to use pivot tables. Pivot Table Data Crunching offers a comprehensive review of all the functionalities of Pivot Tables from author Bill Jelen, otherwise known as Mr. Excel from www.mrexcel.com, and Michael Alexander, a Microsoft Certified Application Developer. The authors' practical scenarios and real-world advice demonstrate the benefits of Pivot Tables and how to avoid the common pitfalls of every day data crunching. Each solution presented in the book can be accomplished with resources available in the Excel interface, making Pivot Table Data Crunching a beneficial resource for all levels of Excel users.

Table of Contents:
Introduction. 1. Pivot Table Fundamentals.     What Is a Pivot Table?     Why Should You Use a Pivot Table?     When Should You Use a Pivot Table?     The Anatomy of a Pivot Table       Data Area       Row Area       Column Area       Page Area     Pivot Tables Behind the Scenes     Limitations of Pivot Table Reports     Next Steps 2. Creating a Basic Pivot Table.     Preparing Your Data for Pivot Table Reporting       Ensure Your Data Is in a Tabular Layout       Use Unique Headings That Occupy Only a Single Row of Data       Avoid Storing Data in Section Headings       Avoid Repeating Groups as Columns       Eliminate Gaps and Blank Cells in Your Data Source       Apply Appropriate Type Formatting to Your Fields       Summary of Good Data Source Design       Cleaning Up Data for Pivot Table Analysis     Creating a Basic Pivot Table       Introduction to the PivotTable Wizard       Drag Fields to the Report       Adding Fields to the Pivot Table       Rearranging the Pivot Table       Revenue by Market and Model       Watch the Mouse Pointer to Learn Where You Are Dropping a Field       Redisplay the Pivot Table Field List       Redisplay the Pivot Table Toolbar       Activate the PivotTable Wizard     Keeping Up with Changes in Your Data Source       Changes Have Been Made to Your Existing Data Source       Your Data Source's Range Has Been Expanded with the Addition of Rows or Columns     Next Steps 3. Customizing Fields in a Pivot Table.     The Need to Customize     Displaying the PivotTable Field Dialog Box     Customizing Field Names     Applying Numeric Formats to Data Fields     Changing Summary Calculations       One Blank Cell Causes a Count       Using Functions Other Than Count or Sum     Adding and Removing Subtotals       Suppress Subtotals When You Have Many Row Fields       Adding Multiple Subtotals for One Field     Using Running Total Options       Display Change from Year to Year with Difference From       How Much Does Each Line of Business Contribute to the Total?       Seasonality Reports       Revenue by Line of Business Report     Next Steps 4. Formatting Your Pivot Table Report.     Using AutoFormat     Applying Your Own Style     Setting Table Options       Grand Totals for Columns       Grand Totals for Rows       AutoFormat Table       Subtotal Hidden Page Items       Merged Labels       Preserve Formatting       Repeat Item Labels on Each Printed Page       Mark Totals with *       Page Layout       For Error Values Show       For Empty Cells Show       Set Print Titles       Formatting a Pivot Table     Next Steps 5. Controlling the Way You View Your Pivot Data.     Showing and Hiding Options       The Basics of Hiding an Item       Showing All Items Again       Showing or Hiding Most Items       Hiding or Showing Items Without Data       Hiding or Showing Items in a Page Field       Showing or Hiding Items in a Data Field     Sorting in a Pivot Table       Sorting Using the Advanced Options Dialog Box       Note the Effect of Layout Changes on AutoSort       Sorting Using the Manual Method       Sorting Using the Sorting Buttons on the Standard Toolbar     Producing Top 10 Reports     Grouping Pivot Fields       Grouping Date Fields       When Grouping by Months, Include Years       Grouping Date Fields by Week       Grouping Two Date Fields in One Report       Order Lead-Time Report       Grouping Numeric Fields       Grouping Text Fields       Grouping and Ungrouping     Next Steps 6. Performing Calculations Within Your Pivot Tables.     Introducing Calculated Fields and Calculated Items       Method 1: Manually Add the Calculated Field to Your Data Source       Method 2: Use a Formula Outside of Your Pivot Table to Create the Calculated Field       Method 3: Insert a Calculated Field Directly into Your Pivot Table     Creating Your First Calculated Field       Summarizing Next Year's Forecast     Creating Your First Calculated Item       Creating a Mini-Dashboard     Rules and Shortcomings of Pivot Table Calculations       Order of Operator Precedence       Cell References and Named Ranges       Worksheet Functions       Constants       Referencing Totals       Rules Specific to Calculated Fields       Rules Specific to Calculated Items     Managing and Maintaining Your Pivot Table Calculations       Editing and Deleting Your Pivot Table Calculations       Changing the Solve Order or Your Calculated Items       Documenting Your Formulas     Next Steps 7. Creating and Using Pivot Charts.     What Is a Pivot Chart Really?     Creating Your First Pivot Chart     Rules and Limitations of Pivot Charts       Pivot Chart Layout Optimization       Scatter, Bubble, and Stock Charts Off Limits       Limitations on Element Size and Location       Certain Customizations Aren't Permanent       Create a Dynamic Year-Over-Year Chart     Alternatives to Using Pivot Charts       Avoiding Overhead       Avoid the Formatting Limitations of Pivot Charts     Next Steps 8. Using Disparate Data Sources for Your Pivot Table.     Working with Disparate Data Sources     Using Multiple Consolidation Ranges     The Anatomy of a Multiple Consolidation Range Pivot Table       The Row Field       The Column Field       The Value Field       The Page Fields       Redefining Your Pivot Table       Consolidate and Analyze Eight Datasets     Creating a Pivot Table from an Existing Pivot Table     Next Steps 9. Using External Data Sources for Your Pivot Table.     Building a Pivot Table Using External Data Sources       Working Around Excel's Data Management Limitations       About MS Query       Analyze a Dataset with More Than 83,000 Records with a Pivot Table     Importing and Using External Data Without the PivotTable Wizard     Creating Dynamic Pivot Table Reporting Systems       Create a Standalone Dynamic Pivot Table Reporting System     Pivot Table Data Options     Next Steps 10. Leveraging the Power of OLAP Cubes.     Defining OLAP?     Benefits of OLAP Cubes     Introduction to Data Warehouses and OLAP Cubes       Operational Data       Warehousing Your Data       Enter the Cube       Cubes Offer Prebuilt Data Views     Connecting to an OLAP Cube       Make the Connection to a Local Cube       Make the Connection to a Server Cube     Working with an OLAP Pivot Table       Arranging the Data       Drilling Into the Cube       Using Page Fields     Comparing OLAP Cubes' Pivot Tables to Excel Data       OLAP Handles More Data, Faster       Dimensions or Measures       OLAP Measures Are Already Grouped       Drill-Through of OLAP Data       Calculated Fields with OLAP       Other Pivot Table Features Operate the Same     Other Considerations When Using OLAP Cubes       Viewing an OLAP Cube Online       Writing Back to a Cube       Setting Actions in a Cube       Combining Cubes       Building a Local Cube     Next Steps 11. Enhancing Your Pivot Table Reports with Macros.     Why Use Macros with Your Pivot Table Reports?     Recording Your First Macro     Creating a User Interface with Form Controls     Altering a Recorded Macro to Add Functionality       Synchronize Two Pivot Tables with One Combo Box     Next Steps 12. Using VBA to Create Pivot Tables.     Introduction to VBA       Enable VBA in Your Copy of Excel       Visual Basic Editor       Visual Basic Tools       The Macro Recorder       Understanding Object-Oriented Code     Tricks of the Trade       Write Code to Handle Any Size Data Range       Use Super-Variables-Object Variables     Versions     Build a Pivot Table in Excel VBA       Getting a Sum Instead of a Count       Cannot Move or Change Part of a Pivot Report       Size of a Finished Pivot Table     Revenue by Model for a Product Line Manager       Eliminate Blank Cells in the Data Area       Control the Sort Order with AutoSort       Default Number Format       Suppress Subtotals for Multiple Row Fields       Suppress Grand Total for Rows     Handle Additional Annoyances       New Workbook to Hold the Report       Summary on a Blank Report Worksheet       Fill Outline View       Final Formatting       Add Subtotals       Put It All Together     Issues with Two or More Data Fields       Calculated Data Fields       Calculated Items     Summarize Date Fields with Grouping       Group by Week     Advanced Pivot Table Techniques       AutoShow Feature to Produce Executive Overviews       ShowDetail to Filter a Recordset       Create Reports for Each Region or Model       Manually Filter Two or More Items in a PivotField     Control the Sort Order Manually     Sum, Average, Count, Min, Max, and More     Report Percentages       Percentage of Total       Percentage Growth from Previous Month       Percentage of a Specific Item       Running Total       Special Considerations for Excel 97     Next Steps A. Solutions to Common Questions and Issues with Pivot Tables.     What does "The PivotTable field name is not valid" mean?       Problem       Solution     When I refresh my pivot table, my data disappears.       Problem       Solution     When I try to group a field, I get an error message.       Problem       Solution     Why can't I group my month fields into quarters?       Problem       Solution     My pivot table is showing the same data item twice.       Problem       Solution     Why are deleted data items still showing up in the page field?       Problem       Solution     When I type a formula referencing a pivot table, I cannot copy the formula down.       Problem       Solution     How can I sort data items in a unique order that is not ascending or descending?       Problem       Solution     How do I turn my pivot table into hard data?       Problem       Solution     Is there an easy way to fill the empty cells left by row fields?       Problem       Solution     Is there an easy way to fill the empty cells left by row fields in multiple columns?       Problem       Solution     How do I add a rank number field to my pivot table?       Problem       Solution     Why does my pivot chart exclude months for certain data items?       Problem       Solution     Can I create a pivot chart on the same sheet as my pivot table?       Problem       Solution     How can I turn my pivot table report into an interactive web page?       Problem       Solution Index.