Pivot Table Data Crunching

Author:   Bill Jelen ,  Michael Alexander
Publisher:   Pearson Education (US)
ISBN:  

9780789734358


Pages:   288
Publication Date:   07 July 2005
Format:   Paperback
Availability:   In Print   Availability explained
Limited stock is available. It will be ordered for you and shipped pending supplier's limited stock.

Our Price $105.57 Quantity:  
Add to Cart

Share |

Pivot Table Data Crunching


Add your own review!

Overview

Offers a comprehensive review of all the functionalities of Pivot Tables, making it a single guide to Pivot Tables.; Authors have an experience base that would be difficult to replicate in any other team.; Provides practical scenarios that demonstrate the benefits of Pivot Tables and also explain how to avoid common pitfalls of every day data crunching.; All the best functionalities of Pivot Tables in one guide, creating a tutorial that offers practical solutions to day-to-day problems. In a survey of 5000 Excel users at MrExcel.com, only 42 per cent of users know how to use pivot tables. The Excel project team at Microsoft estimates that only 15 per cent 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. All the sample data sets will be available for download from www quepublishing.com.

Full Product Details

Author:   Bill Jelen ,  Michael Alexander
Publisher:   Pearson Education (US)
Imprint:   Que Corporation,U.S.
Dimensions:   Width: 23.10cm , Height: 1.50cm , Length: 18.40cm
Weight:   0.494kg
ISBN:  

9780789734358


ISBN 10:   0789734354
Pages:   288
Publication Date:   07 July 2005
Audience:   College/higher education ,  Tertiary & Higher Education
Format:   Paperback
Publisher's Status:   Out of Print
Availability:   In Print   Availability explained
Limited stock is available. It will be ordered for you and shipped pending supplier's limited stock.

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.

Reviews

Excel users may already be aware of the power of pivot tables, which allow summarization of 50,000 rows of data with one click of the mouse -- but many user's guides don't begin to touch upon its powerful features. PIVOT TABLE DATA CRUNCHING provides a tutorial which promises users and prior Excel fans the ability to get up to speed quickly, using real-world examples such as how to highlight productivity channels and produce meaningful reports. An excellent business guide. --California Bookwatch, 10/1/06


Author Information

Bill Jelen is Mr. Excel! He is principal behind the leading Excel website, MrExcel.com. As an Excel consultant, he has written Excel VBA solutions for hundreds of clients around the English-speaking world. His website hosts over 12 million page views annually. Michael Alexander is a Microsoft Certified Application Developer (MCAD) with over 13 years experience developing business solutions with Microsoft Office, VBA, and VB.Net. He currently lives in Plano, TX where heads an analytical services department for a $700 million company, and has written numerous articles at the www.DataPigTechnologies.com website.

Tab Content 6

Author Website:  

Customer Reviews

Recent Reviews

No review item found!

Add your own review!

Countries Available

All regions
Latest Reading Guide

ls

Shopping Cart
Your cart is empty
Shopping cart
Mailing List