Pivot Table Data Crunching

June 2005

White collar productivity would skyrocket if everyone actually understood how to use Pivot Tables.

"Summarize 60,000 rows of data in seconds!"

The functionality behind pivot tables was actually invented by folks at the Lotus Advanced Technology Group in the late 1980s. Originally available only for Steve Job's NeXT computer, the technology became widely available in 1992 with the release of Improv. During Microsoft's bitter battle with Lotus for spreadsheet supremacy, the concept was added to Excel's data menu. Not many people understand how to actually make use of pivot tables.

As a data analyst in 1992, Bill Jelen quickly discovered the power behind pivot tables. "It was possible to create summary reports before Pivot Tables, but you had to build complicated DSUM formulas and weave them into an obscure feature called / Data Table 2. It would take an hour and make your head spin. If I ever tried to explain the process to a spreadsheet novice, their eyes would glaze over. It was something that was simply impossible to learn. Once Pivot Tables were added to Excel, anyone who can drag a mouse can create stunning summary reports in seconds.

"If you can drag a mouse, you can create a basic pivot table"

Imagine that you have 60,000 rows of transactional data like this:

Your department VP asks for a summary table showing sales by product and region. Without a pivot table, you are going to be learning CSE formulas or using the conditional sum wizard in combination with Data - Filter - Advanced Filter. But, with a pivot table, you can convert the 60,000 rows of data to this summary report with four mouse clicks and three mouse drags.

Once you have a pivot table, then one additional mouse drag allows you to transform the report to show detail by the customer or any other field.

Both of the above summary reports were created in seconds instead of hours.

"Includes features rarely seen in a pivot table!"

As if Pivot Tables were not powerful enough, you can also use them to create Top 10 reports or charts for each region, or tables showing year over year growth. Learn how to use Pivot Tables with Access or OLAP data. See how to use VBA to automate the creation of charts for each product or customer. This book will allow you to stop wasting time with obscure methods (I have to read Aladin's post every time that I try to use SUMPRODUCT...). This book will enable you to do analyses that were previously time-prohibitive. Become the Excel superstar in your office with just a few clicks of the mouse.