Pivot Table Data Crunching

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.