Pivot table data crunching

In a survey of 5000 Excel users at MrExcel.com, only 42% of users know how to use pivot tables. The Excel project team at Microsoft estimates that only 15% of their survey pool makes use of pivot tables. In reality, probably only a quarter of Excel users can create a basic pivot table, and only a small percentage of that actually harness all power pivot tables afford them. The goal of this book is to consolidate all the best functionality of Pivot tables in one guide that provides basic and intermediate Excel users a meaningful tutorial that will offer some practical solutions to day-to-day problems. Most documentation on Pivot Tables offers examples that show clean environments with data that means nothing to users. Jelen and Alexander provide practical, real-world scenarios that not only demonstrate users the benefits of Pivot Tables, but also explain them how to avoid common pitfalls of every day data crunching. There are a lot of Excel savvy users that want to use the advanced features of Excel, but don't want to dedicate their lives to it. There is a huge segment of the Excel community that are not developers and don't want to be. Because the solutions presented in this book can be accomplished with available resources in the Excel interface, this book is beneficial to beginners, intermediate users, and experts.

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 Exce