Pivot Table Data Crunching

( 8 )

Overview

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

See more details below
Other sellers (Paperback)
  • All (30) from $1.99   
  • New (6) from $4.29   
  • Used (24) from $1.99   
Pivot Table Data Crunching

Available on NOOK devices and apps  
  • NOOK Devices
  • Samsung Galaxy Tab 4 NOOK 7.0
  • Samsung Galaxy Tab 4 NOOK 10.1
  • NOOK HD Tablet
  • NOOK HD+ Tablet
  • NOOK eReaders
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac

Want a NOOK? Explore Now

NOOK Book (eBook)
$15.49
BN.com price
(Save 44%)$27.99 List Price

Overview

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.

Read More Show Less

Product Details

  • ISBN-13: 9780789734358
  • Publisher: Que
  • Publication date: 6/22/2005
  • Series: Business Solutions Series
  • Pages: 274
  • Product dimensions: 7.38 (w) x 9.08 (h) x 0.69 (d)

Meet the Author

Bill Jelen is the principal behind the leading Excel website, MrExcel.com. As an Excel consultant, he has written Excel VBA solutions for hundreds of clients around the English-speaking world. His website hosts more than 12 million page views annually. Prior to founding MrExcel.com, Bill spent 12 years "in the trenches," working as a financial analyst for the finance, marketing, accounting, and operations departments of a $500 million public company. His duties included turning large amounts of mainframe data into meaningful reports. Working initially with Lotus 1-2-3 and then Excel, Bill honed techniques to take massive amounts of data and produce meaningful reports in record time. Bill is the author of seven books on Microsoft Excel.

Michael Alexander is a Microsoft Certified Application Developer (MCAD) with more than 13 years' experience consulting and developing office solutions. Michael started his career in consulting and development at the White House Communications Agency in Washington D.C., later parleying his experience with VBA and VB into a successful career in the private sector, developing middleware and reporting solutions for a wide variety of industries. He currently lives in Plano, Texas, where he heads an analytical services group for a $700 million company. In his spare time he runs a free tutorial site, http://www.datapigtechnologies.com, where he shares basic Access and Excel tips with intermediate users.

© Copyright Pearson Education. All rights reserved.

Read More Show Less

Read an Excerpt

INTRODUCTIONINTRODUCTIONIn this 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

Pivot tables are the single most powerful feature in all of Excel. They came along during the 90s when Microsoft and Lotus were locked in a bitter battle for dominance of the spreadsheet market. The race to continually add enhanced features to their respective products during the mid-90s led to many incredible features, but none as powerful as the pivot table.

With a pivot table, you can take 65,000 rows of transactional data and transform it into a summary report in seconds. If you can drag a mouse, you can create a pivot table. In addition to quickly summarizing and calculating data, pivot tables allow you to change your analysis on the fly by simply dragging fields from one area of a report to another.

There is simply no other tool in Excel that gives you the flexibility and analytical power that pivot tables can give you.

What You Will Learn from This Book

It is widely agreed upon that close to 50% of Excel users leave 80% of Excel untouched. That is to say that most users don't tap into the full potential of Excel's built-in utilities. Of these utilities, the most prolific by far is the pivot table. Despite the fact that pivot tables have been a cornerstone of Excel for more than 10 years now, they remain one of the most underutilized tools in the entire Microsoft Office Suite. If you have picked up this book, you are savvy enough to have heard of pivot tables or even have used them on occasion. You have a sense that there is power in pivot tables that you are not using and you want to learn how to leverage that power to quickly increase your productivity.

Within the first two chapters, you will be able to create basic pivot tables, increase your productivity, and produce reports in minutes instead of hours. Within the first seven chapters, you will be able to output complex pivot reports with drill-down capabilities accompanying charts. By the end of the book, you will be able to build a dynamic pivot table reporting system.

Skills Required to Use This Book

We have created a reference that is comprehensive enough for hardcore analysts, yet relevant to casual users of Excel. The bulk of the book will cover how to use pivot tables in the Excel user interface. The final chapter includes information on how to create pivot tables in Excel's powerful VBA macro language. This means that any user who has a firm grasp of the basics (preparing data, copying, pasting, entering simple formulas) should have no problem understanding the concepts in this book.

Life Before Pivot Tables

Imagine that it is 1992. You are using Lotus 1-2-3 or Excel 4. You have 40,000 rows of transactional data, as shown in Figure I.1. Your manager asks you to prepare a summary report showing revenue by region and model.

Figure I.1
As a financial analyst in 1992, your job is to produce a summary from this 40,000 row dataset.

In 1992, this was a daunting task. It required superhuman spreadsheet skills that few could master. Here are the steps you would need to take:

  1. You need to get a list of the unique regions in the dataset. Use the Advanced Filter command with Unique Records Only (see Figure I.2) to extract a list of the unique regions.

Figure I.2
Even today, the Advanced Filter command is not a lot of fun to use.

  1. You need to get a list of the unique models in the dataset. Do a second Advanced Filter with Unique Records Only to extract a list of the unique models.
  2. You need to turn the list of models sideways so that it runs across the columns. Copy the list of unique models. Then, do a Edit, Paste Special, Transpose to arrange the models as headings going across the report. You now have a skeleton of the report, as shown in Figure I.3.

Figure I.3
After a second Advanced Filter command and Edit, Paste Special, Transpose, you have this skeleton of the final report. There is still a long way to go.

  1. The DSUM function could be used to total a column based on one criteria, but not based on two criteria. Therefore, you need to abandon typical functions and instead rely on an array formula. Before entering the array formula, set up two fields above the report to hold a sample region and a sample model.
  2. In the corner cell of the report, build an array formula to test whether the region column is North and the Model column is 4055T, and if so, add up the corresponding row from the Revenue column. After typing this formula, remember to press Ctrl+Shift+Enter or else the formula will not work. The formula is shown in the formula bar in Figure I.4.

Tip - For a complete explanation of two-condition sums using array formulas, see http://www.MrExcel.com/tip031.shtml.

Figure I.4
With the array formula in the corner of the report, you are ready to use the not-so-intuitive Data Table 2 command.

  1. You know you're a hardcore data analyst if you can still imagine hitting the keystrokes for /Data Table 2 in Lotus 1-2-3. Figure I.5 shows the equivalent function in Excel. The Table command on the Data menu will allow you to set up a table.

Figure I.5
The Data Table command replicates the formula in the top-left corner of the table, but replaces two references in the formula with the headings at the top and left of the report.

  1. Finally, after using two advanced filters, a Paste Special, writing the hardest formula in the world, and then using the Data Table command, you have the result your manager is looking for, as shown in Figure I.6. If you could pull this analysis off in 10 minutes, you were doing an amazing job.

Figure I.6
After 10 minutes displaying knowledge of obscure spreadsheet commands, you have produced the needed report.

Now, if your manager takes a look at the report and asks you to add Market to the analysis, you are nearly back at square one and are looking at an additional 15 minutes to produce the new report.

The Invention of the Pivot Table

The concept that led to today's pivot table came from the halls of the Lotus Development Corporation with a revolutionary spreadsheet program called Lotus Improv. Improv was envisioned in 1986 by Pito Salas of the Advanced Technology Group at Lotus. Realizing that spreadsheets often have patterns of data, Pito concluded that if one could build a tool that could recognize these patterns, then one could build enhanced data models. Lotus ran with the concept and started developing the next-generation spreadsheet.

Throughout 1987, Lotus demoed its new program to a few companies. In 1988, Steve Jobs saw the program and immediately wanted it developed for his upcoming NeXT computer platform. The program, finally named Lotus Improv, was eventually shipped in 1991 for the NeXT platform. A version for Windows was introduced in 1993.

Pito Salas, inventor of the pivot table concept, is always working on cutting-edge products at http://www.salas.com.

The core concept behind Improv was that data, data views, and formulas should be encapsulated as separate entities and treated as different animals. For the first time in a spreadsheet program, a dataset was given a name that could be grouped into larger categories. This naming and grouping capability paved the way for the most powerful feature in Improv, rearranging data. With Improv, a user could define and store a set of categories and then change the view by simply dragging the category names with the mouse. The user could also create totals and group summaries.

Microsoft eventually picked up on this concept in its pivot table functionality in Excel 5. Years later, with the release of Excel 97, Microsoft offered users an enhanced pivot table wizard and key improvements to pivot table functionality, such as the ability to add calculated fields. Excel 97 also opened up the pivot cache to developers, fundamentally changing the way pivot tables are created and managed. Microsoft introduced the pivot chart with Excel 2000, providing users a way to represent pivot tables graphically. Since Excel 2000, changes made to pivot tables have been mainly cosmetic, much to the chagrin of pivot table fans everywhere.

Life After Pivot Tables

You have 40,000 rows of transactional data, as discussed in the previous case study. Your manager asks you to prepare a summary report showing revenue by Region and Model. Luckily, you have pivot tables at your disposal. Here are the steps you would follow today:

  1. Select a single cell in your dataset. Choose PivotTable Report from the Data menu. Click Finish. You are given a blank pivot table, as shown in Figure I.7.

Figure I.7
After three mouse clicks, you have a blank pivot table report. Three more mouse clicks to go.

  1. From the pivot table field list, drag Region and drop it where it reads "Drop Row Fields Here." Drag the Model field where it reads "Drop Column Fields Here." Drag the Revenue field where it reads "Drop Data Items Here." After a total of six mouse clicks, you have the required report, as shown in Figure I.8.

Figure I.8
Drag three headings to the report, and Excel calculates your report.

If you are racing, you can actually create the report shown in Figure I.8 in exactly 10 seconds. This is an amazing accomplishment. Realistically, it would take you about 50 seconds at normal speed to create the report. If you are a spreadsheet wizard and are instead following the steps in the previous case study, the non–pivot table solution would take you at least 12 times longer.

Further, when your manager comes back with the request to add Market to the analysis, it takes just seconds to drag the Market field from the field list and drop it on the report, as shown in Figure I.9.

Figure I.9
Creating a new report with the Market field is as simple as dragging the field to the report.

Conventions Used in This Book

This book contains the following special elements.

Note - Notes provide additional information outside the main thread of the chapter discussion that might still be useful for you to know.

Tip - Tips provide you with quick workarounds and timesaving techniques to help you do your work more efficiently.

Caution - Cautions warn you about potential pitfalls you might encounter. Pay attention to these, because they alert you to problems that otherwise could cause you hours of frustration.

Case Study - Case studies provide a real-world look at topics previously introduced in the chapter.

© Copyright Pearson Education. All rights reserved.

Read More Show Less

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.

Read More Show Less

Preface

INTRODUCTION

In this 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

Pivot tables are the single most powerful feature in all of Excel. They came along during the 90s when Microsoft and Lotus were locked in a bitter battle for dominance of the spreadsheet market. The race to continually add enhanced features to their respective products during the mid-90s led to many incredible features, but none as powerful as the pivot table.

With a pivot table, you can take 65,000 rows of transactional data and transform it into a summary report in seconds. If you can drag a mouse, you can create a pivot table. In addition to quickly summarizing and calculating data, pivot tables allow you to change your analysis on the fly by simply dragging fields from one area of a report to another.

There is simply no other tool in Excel that gives you the flexibility and analytical power that pivot tables can give you.

What You Will Learn from This Book

It is widely agreed upon that close to 50% of Excel users leave 80% of Excel untouched. That is to say that most users don't tap into the full potential of Excel's built-in utilities. Of these utilities, the most prolific by far is the pivot table. Despite the fact that pivot tables have been a cornerstone of Excel for more than 10 years now, they remain one of the most underutilized tools in the entire Microsoft Office Suite. If you have picked up this book, you are savvy enough to have heard of pivot tables or even have used them on occasion. You have a sense that there is power in pivot tables that you are not using and you want to learn how to leverage that power to quickly increase your productivity.

Within the first two chapters, you will be able to create basic pivot tables, increase your productivity, and produce reports in minutes instead of hours. Within the first seven chapters, you will be able to output complex pivot reports with drill-down capabilities accompanying charts. By the end of the book, you will be able to build a dynamic pivot table reporting system.

Skills Required to Use This Book

We have created a reference that is comprehensive enough for hardcore analysts, yet relevant to casual users of Excel. The bulk of the book will cover how to use pivot tables in the Excel user interface. The final chapter includes information on how to create pivot tables in Excel's powerful VBA macro language. This means that any user who has a firm grasp of the basics (preparing data, copying, pasting, entering simple formulas) should have no problem understanding the concepts in this book.

Life Before Pivot Tables

Imagine that it is 1992. You are using Lotus 1-2-3 or Excel 4. You have 40,000 rows of transactional data, as shown in Figure I.1. Your manager asks you to prepare a summary report showing revenue by region and model.

Figure I.1
As a financial analyst in 1992, your job is to produce a summary from this 40,000 row dataset.

In 1992, this was a daunting task. It required superhuman spreadsheet skills that few could master. Here are the steps you would need to take:

  1. You need to get a list of the unique regions in the dataset. Use the Advanced Filter command with Unique Records Only (see Figure I.2) to extract a list of the unique regions.

Figure I.2
Even today, the Advanced Filter command is not a lot of fun to use.

  1. You need to get a list of the unique models in the dataset. Do a second Advanced Filter with Unique Records Only to extract a list of the unique models.
  2. You need to turn the list of models sideways so that it runs across the columns. Copy the list of unique models. Then, do a Edit, Paste Special, Transpose to arrange the models as headings going across the report. You now have a skeleton of the report, as shown in Figure I.3.

Figure I.3
After a second Advanced Filter command and Edit, Paste Special, Transpose, you have this skeleton of the final report. There is still a long way to go.

  1. The DSUM function could be used to total a column based on one criteria, but not based on two criteria. Therefore, you need to abandon typical functions and instead rely on an array formula. Before entering the array formula, set up two fields above the report to hold a sample region and a sample model.
  2. In the corner cell of the report, build an array formula to test whether the region column is North and the Model column is 4055T, and if so, add up the corresponding row from the Revenue column. After typing this formula, remember to press Ctrl+Shift+Enter or else the formula will not work. The formula is shown in the formula bar in Figure I.4.

Tip - For a complete explanation of two-condition sums using array formulas, see http://www.MrExcel.com/tip031.shtml.


Figure I.4
With the array formula in the corner of the report, you are ready to use the not-so-intuitive Data Table 2 command.

  1. You know you're a hardcore data analyst if you can still imagine hitting the keystrokes for /Data Table 2 in Lotus 1-2-3. Figure I.5 shows the equivalent function in Excel. The Table command on the Data menu will allow you to set up a table.

Figure I.5
The Data Table command replicates the formula in the top-left corner of the table, but replaces two references in the formula with the headings at the top and left of the report.

  1. Finally, after using two advanced filters, a Paste Special, writing the hardest formula in the world, and then using the Data Table command, you have the result your manager is looking for, as shown in Figure I.6. If you could pull this analysis off in 10 minutes, you were doing an amazing job.

Figure I.6
After 10 minutes displaying knowledge of obscure spreadsheet commands, you have produced the needed report.

Now, if your manager takes a look at the report and asks you to add Market to the analysis, you are nearly back at square one and are looking at an additional 15 minutes to produce the new report.

The Invention of the Pivot Table

The concept that led to today's pivot table came from the halls of the Lotus Development Corporation with a revolutionary spreadsheet program called Lotus Improv. Improv was envisioned in 1986 by Pito Salas of the Advanced Technology Group at Lotus. Realizing that spreadsheets often have patterns of data, Pito concluded that if one could build a tool that could recognize these patterns, then one could build enhanced data models. Lotus ran with the concept and started developing the next-generation spreadsheet.

Throughout 1987, Lotus demoed its new program to a few companies. In 1988, Steve Jobs saw the program and immediately wanted it developed for his upcoming NeXT computer platform. The program, finally named Lotus Improv, was eventually shipped in 1991 for the NeXT platform. A version for Windows was introduced in 1993.


Pito Salas, inventor of the pivot table concept, is always working on cutting-edge products at http://www.salas.com.

The core concept behind Improv was that data, data views, and formulas should be encapsulated as separate entities and treated as different animals. For the first time in a spreadsheet program, a dataset was given a name that could be grouped into larger categories. This naming and grouping capability paved the way for the most powerful feature in Improv, rearranging data. With Improv, a user could define and store a set of categories and then change the view by simply dragging the category names with the mouse. The user could also create totals and group summaries.

Microsoft eventually picked up on this concept in its pivot table functionality in Excel 5. Years later, with the release of Excel 97, Microsoft offered users an enhanced pivot table wizard and key improvements to pivot table functionality, such as the ability to add calculated fields. Excel 97 also opened up the pivot cache to developers, fundamentally changing the way pivot tables are created and managed. Microsoft introduced the pivot chart with Excel 2000, providing users a way to represent pivot tables graphically. Since Excel 2000, changes made to pivot tables have been mainly cosmetic, much to the chagrin of pivot table fans everywhere.

Life After Pivot Tables

You have 40,000 rows of transactional data, as discussed in the previous case study. Your manager asks you to prepare a summary report showing revenue by Region and Model. Luckily, you have pivot tables at your disposal. Here are the steps you would follow today:

  1. Select a single cell in your dataset. Choose PivotTable Report from the Data menu. Click Finish. You are given a blank pivot table, as shown in Figure I.7.

Figure I.7
After three mouse clicks, you have a blank pivot table report. Three more mouse clicks to go.

  1. From the pivot table field list, drag Region and drop it where it reads "Drop Row Fields Here." Drag the Model field where it reads "Drop Column Fields Here." Drag the Revenue field where it reads "Drop Data Items Here." After a total of six mouse clicks, you have the required report, as shown in Figure I.8.

Figure I.8
Drag three headings to the report, and Excel calculates your report.

If you are racing, you can actually create the report shown in Figure I.8 in exactly 10 seconds. This is an amazing accomplishment. Realistically, it would take you about 50 seconds at normal speed to create the report. If you are a spreadsheet wizard and are instead following the steps in the previous case study, the non–pivot table solution would take you at least 12 times longer.

Further, when your manager comes back with the request to add Market to the analysis, it takes just seconds to drag the Market field from the field list and drop it on the report, as shown in Figure I.9.

Figure I.9
Creating a new report with the Market field is as simple as dragging the field to the report.

Conventions Used in This Book

This book contains the following special elements.


Note - Notes provide additional information outside the main thread of the chapter discussion that might still be useful for you to know.



Tip - Tips provide you with quick workarounds and timesaving techniques to help you do your work more efficiently.



Caution - Cautions warn you about potential pitfalls you might encounter. Pay attention to these, because they alert you to problems that otherwise could cause you hours of frustration.



Case Study - Case studies provide a real-world look at topics previously introduced in the chapter.


© Copyright Pearson Education. All rights reserved.

Read More Show Less

Customer Reviews

Average Rating 3.5
( 8 )
Rating Distribution

5 Star

(3)

4 Star

(1)

3 Star

(1)

2 Star

(1)

1 Star

(2)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Anonymous

    Posted October 26, 2005

    why and when to make a pivot table

    The most cogent reasons for using this book are given in its first chapter. Namely why use a pivot table? And when to recognise when you might want to do so. While it is hard to strictly demarcate these questions, they are useful in focusing on the key issues. For why to use, it is to reduce the chance of manual error, by letting you be able to automate several or even many steps. This also permits greater productivity. From having less occasion to redo erroneous steps, and from doing faster analysis. For when, it might be due to having a large data set, or to having to find certain relationships within this. Given the above, the rest of the book proceeds to show how to make a pivot table. There is a clear description of the anatomy of the table, which helps greatly when going through the book.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)