Skip to content

Pivot Table Data Crunching

ISBN-10: 0789734354

ISBN-13: 9780789734358

Edition: 2005

Authors: Bill Jelen, Michael Alexander

List price: $39.99
Blue ribbon 30 day, 100% satisfaction guarantee!
Buy eBooks
what's this?
Rush Rewards U
Members Receive:
Carrot Coin icon
XP icon
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 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.
Customers also bought

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