Pivot Table Data Crunching for Microsoft Office Excel 2007

ISBN: 0789736012
EAN: 2147483647

Year: 2003
Pages: 140

Authors: Bill Jelen, Michael Alexander

BUY ON AMAZON

Pivot Table Data Crunching
Table of Contents
Copyright
About the Authors
Acknowledgments
We Want to Hear from You
Introduction
What You Will Learn from This Book
Skills Required to Use This Book
The Invention of the Pivot Table
Conventions Used in This Book
Chapter 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
Pivot Tables Behind the Scenes
Limitations of Pivot Table Reports
Next Steps
Chapter 2. Creating a Basic Pivot Table
Preparing Your Data for Pivot Table Reporting
Creating a Basic Pivot Table
Keeping Up with Changes in Your Data Source
Next Steps
Chapter 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
Adding and Removing Subtotals
Using Running Total Options
Next Steps
Chapter 4. Formatting Your Pivot Table Report
Using AutoFormat
Applying Your Own Style
Setting Table Options
Next Steps
Chapter 5. Controlling the Way You View Your Pivot Data
Showing and Hiding Options
Sorting in a Pivot Table
Producing Top 10 Reports
Grouping Pivot Fields
Next Steps
Chapter 6. Performing Calculations Within Your Pivot Tables
Introducing Calculated Fields and Calculated Items
Creating Your First Calculated Field
Creating Your First Calculated Item
Rules and Shortcomings of Pivot Table Calculations
Managing and Maintaining Your Pivot Table Calculations
Next Steps
Chapter 7. Creating and Using Pivot Charts
What Is a Pivot Chart Really?
Creating Your First Pivot Chart
Rules and Limitations of Pivot Charts
Alternatives to Using Pivot Charts
Next Steps
Chapter 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
Creating a Pivot Table from an Existing Pivot Table
Next Steps
Chapter 9. Using External Data Sources for Your Pivot Table
Building a Pivot Table Using External Data Sources
Importing and Using External Data Without the PivotTable Wizard
Creating Dynamic Pivot Table Reporting Systems
Pivot Table Data Options
Next Steps
Chapter 10. Leveraging the Power of OLAP Cubes
Defining OLAP?
Benefits of OLAP Cubes
Introduction to Data Warehouses and OLAP Cubes
Connecting to an OLAP Cube
Working with an OLAP Pivot Table
Comparing OLAP Cubes Pivot Tables to Excel Data
Other Considerations When Using OLAP Cubes
Next Steps
Chapter 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
Next Steps
Chapter 12. Using VBA to Create Pivot Tables
Introduction to VBA
Tricks of the Trade
Versions
Build a Pivot Table in Excel VBA
Revenue by Model for a Product Line Manager
Handle Additional Annoyances
Issues with Two or More Data Fields
Summarize Date Fields with Grouping
Advanced Pivot Table Techniques
Control the Sort Order Manually
Sum, Average, Count, Min, Max, and More
Report Percentages
Next Steps
Appendix A. Solutions to Common Questions and Issues with Pivot Tables
What does The PivotTable field name is not valid mean?
When I refresh my pivot table, my data disappears
When I try to group a field, I get an error message
Why can t I group my month fields into quarters?
My pivot table is showing the same data item twice
Why are deleted data items still showing up in the page field?
When I type a formula referencing a pivot table, I cannot copy the formula down
How can I sort data items in a unique order that is not ascending or descending?
How do I turn my pivot table into hard data?
Is there an easy way to fill the empty cells left by row fields?
Is there an easy way to fill the empty cells left by row fields in multiple columns?
How do I add a rank number field to my pivot table?
Why does my pivot chart exclude months for certain data items?
Can I create a pivot chart on the same sheet as my pivot table?
How can I turn my pivot table report into an interactive web page?
Index
SYMBOL
A
B
C
D
E
F
G
H
I
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y