Pivot Table Data Crunching

Pivot Table Data Crunching

3.86 (37 ratings by Goodreads)
By (author)  , By (author) 

List price: US$35.99

Currently unavailable

Add to wishlist

AbeBooks may have this title (opens in new window).

Try AbeBooks

Description

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.
show more

Product details

  • Paperback | 288 pages
  • 185.42 x 228.6 x 17.78mm | 226.8g
  • Que Corporation,U.S.
  • Indianapolis, IN, United States
  • English
  • ill
  • 0789734354
  • 9780789734358
  • 981,850

Back cover copy

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.
show more

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 Steps2. 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 Steps3. 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 Steps4. 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 Steps5. 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 Steps6. 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 Steps7. 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 Steps8. 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 Steps9. 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 Steps10. 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 Steps11. 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 Steps12. 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 StepsA. 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 SolutionIndex.
show more

Review quote

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
show more

About Bill Jelen

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.
show more

Rating details

37 ratings
3.86 out of 5 stars
5 32% (12)
4 35% (13)
3 22% (8)
2 8% (3)
1 3% (1)
Book ratings by Goodreads
Goodreads is the world's largest site for readers with over 50 million reviews. We're featuring millions of their reader ratings on our book pages to help you find your new favourite book. Close X