Pivot Table Data Crunching

Name: Pivot Table Data Crunching
Availability: InStock
ISBN: 9780789734358

ISBN-10: 0789734354

ISBN-13: 9780789734358

Edition: 2005

Authors: Bill Jelen, Michael Alexander

List price: $39.99

30 day, 100% satisfaction guarantee!

Semester Rental: $19.09 Due date: 8/14/2024

Rental notice: supplementary materials (access codes, CDs, etc.) are not guaranteed with rental orders.

Buy used: $8.04

Marketplace

3 new & used from $6.70

what's this?

Rush Rewards U
Members Receive:

You have reached 400 XP and carrot coins. That is the daily max!

Description:

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…

Book details

List price: $39.99
Copyright year: 2005
Publisher: Pearson Education
Publication date: 6/21/2005
Binding: Paperback
Pages: 288
Size: 7.25" wide x 8.25" long x 0.50" tall
Weight: 1.100
Language: English

MICHAEL ALEXANDER is the pseudonym of a nurse who has previously worked in the UK and New Zealand.



Introduction



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



Creating a Basic Pivot Table


Preparing Your Data for


Pivot Table Reporting


Ensure Your Data Is 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 Pivot Table 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 of Columns


Next Steps



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



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



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 Data Fields


When Grouping by Months, Include Years


Grouping Data 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



Performing Calculations Within Your Pivot Tables


Introducing Calculated Fields and Calculated Items



Manually Add the Calculated Field to Your Data Source



Use a Formula Outside of Your Pivot Table to Create the Calculated Field



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 Colve Order or Your Calculated Items


Documenting Your Formulas


Next Steps



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



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



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



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



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



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 PivoField


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



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