Free Shipping on all orders in Australia
Over 7 million books in stock
We aim to be carbon neutral by 2022
Pivot Table Data Crunching By Bill Jelen

Pivot Table Data Crunching by Bill Jelen

Condition - Very Good
Only 4 left


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. This book offers a comprehensive review of the functionalities of Pivot Tables. It demonstrates the benefits of Pivot Tables and how to avoid the common pitfalls of every day data crunching.

Pivot Table Data Crunching Summary

Pivot Table Data Crunching by Bill Jelen

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, 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.

Pivot Table Data Crunching Reviews

Excel users may already be aware of the power of pivot tables, which allow summarization of 50,000 rows of data with one click of the mouse - but many user's guides don't begin to touch upon its powerful features. PIVOT TABLE DATA CRUNCHING provides a tutorial which promises users and prior Excel fans the ability to get up to speed quickly, using real-world examples such as how to highlight productivity channels and produce meaningful reports. An excellent business guide.

--California Bookwatch, 10/1/06

About Bill Jelen

Bill Jelen is Mr. Excel! He is principal behind the leading Excel website, As an Excel consultant, he has written Excel VBA solutions for hundreds of clients around the English-speaking world. His website hosts over 12 million page views annually. Michael Alexander is a Microsoft Certified Application Developer (MCAD) with over 13 years experience developing business solutions with Microsoft Office, VBA, and VB.Net. He currently lives in Plano, TX where heads an analytical services department for a $700 million company, and has written numerous articles at the website.

Table of Contents


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


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


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?



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?




Additional information

Pivot Table Data Crunching by Bill Jelen
Used - Very Good
Pearson Education (US)
Book picture is for illustrative purposes only, actual binding, cover or edition may vary.
This is a used book - there is no escaping the fact it has been read by someone else and it will show signs of wear and previous use. Overall we expect it to be in very good condition, but if you are not entirely satisfied please get in touch with us

Customer Reviews - Pivot Table Data Crunching