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.
Downloads
The files for the book can be downloaded here or at the author's web site: http://www.mrexcel.com/pivotbookdata.html
Source File used in Chapters 1-4 (1.4 MB) -- ptdc1.zip
Source data file used in Chapter 5 (1.3 MB) -- ptdc2.zip
Source data file used in Chapter 6-8 (1.5 MB) -- ptdc3.zip
Access database used in Chapter 9 (1.3 MB) -- ptdc4.zip
OLAP file used in Chapter 10 (2.4 MB) -- ptdc5.zip
OLAP file used in Chapter 11 (1.8 MB) -- ptdc6.zip
File with VBA code used in Chapter 12 (1.8 MB) -- ptdc7.zip
Online Sample Chapters
