Pivot Table Data Crunching, by Bill Jelen & Michael Alexander, ISBN 0789734354

Reviewed by: Thomas V. Kappel, December 2005 Published by: QUE Requires: N/A MSRP: US$29.99, CAN$41.99 Pivot Table Data Crunching is a 274 page book focused solely on the Pivot Table feature in Microsoft Excel. The book is centered entirely on the purpose, creation and uses for pivot tables. This should immediately tell you something about the complexity and, possibly, the power of this single program feature. The difficulty level suggested for this book is Power User and you'd better believe it. This is not a book for someone wanting to learn about pivot tables because they heard that they were good. It’s not a book for someone who understands the value of pivot tables and needs a beginner's course. Intermediate Excel users should also stay away. It's strictly for advanced Excel users even though the book states that the information included is relevant to the casual user. It is relevant and interesting, but it takes considerable skill and knowledge in spreadsheet design, creation, and function to obtain full use and reward from the investment in the book. Chapter one is devoted to Pivot Table Fundamentals and the first subheading is, “What is a Pivot Table.” If you work at the four paragraphs the authors use to attempt to define a pivot table, you might be able to create enough clarity to actually define one. Here is my attempt: a pivot table is a view of a portion of the data you have entered into an Excel spreadsheet and workbook. The pivot table views can be used as a tool to examine these subsets of data from various aspects and provide a different perspective. Some people call it data mining.

Perhaps the book's definition makes more sense to you: “Think of a pivot table as a kaleidoscope that is pointed at your dataset. When you look at your dataset through a pivot table, you have the opportunity to see the details in your data that you may not have noticed before. Furthermore, you can turn your pivot table to see your data from different perspectives.” Once again, the idea of data mining fits this definition.

Understand that in order to make the most use of a pivot table and view the data effectively, you must have the data designed and laid out appropriately in the main spreadsheet. In other words, you must know something about spreadsheet design to make the most use of pivot tables. Unfortunately, authors Jelen and Alexander don't go into design much beyond giving you a few basic rules. Your pivot table must have column headings. Your pivot table report cannot be created if you don't have labels in the first row of your spreadsheet or data area describing what is in each column. A few more rules follow in the book including ensuring that your data is in tabular layout, uses unique headings that occupy only a single row of data, data is not stored in section headings, groups and columns aren't repeated, and three or four rules for pivot table creation. Jelen and Alexander also don't discuss the existing spreadsheets you have that might benefit from pivot table mining. The authors do go into using disparate and external data sources in your pivot tables later in the book.

Important note: If you buy the book, don't miss the small TIP at the top of page 23. It’s easy to miss as you work your way through creating a basic pivot table and learning about the Pivot Table Wizard, but it’s the only place you'll find the web site address to download the data source files used in the rest of the book. Otherwise, you'll have to create them yourself as you work through the examples.

Okay, so we know that pivot tables are good and we can view subsections or pieces of data differently. We can also perform calculations within this subset of data, but usually what we really want is to display this data in presentations, meetings, and to the boss in great beautiful charts. Consequently, Chapter 7 is a focal point in the book and one that should be examined in a little more depth. There are four sections after you get through a five paragraph discussion on what a pivot chart really is. The first subsection is titled Creating Your First Pivot Chart. It's a two and a half page discussion with examples that take you through the basic chart creation in Excel with the reduced pivot table data. There's nothing new here as the chart choices and creation processes seem the same as the main program. The second subsection of the chapter is where it gets interesting. This section is titled Rules and Limitations of Pivot Charts and it is almost nine pages long!

The subsections in this chapter take you through layout optimization for producing the best results from charts. Once again, this is closely linked to the original design layout of the spreadsheet and the data selection for the pivot table and chart. The rest of the chapter discusses the limitations of pivot table data mining: scatter, bubble, and stock charts off limits; limitations on element size and location; certain customizations that aren't permanent. The final subsection is title Alternatives to Using Pivot Charts and provides some alternatives presentation methods and approaches.

I believe that the last claim on the back cover of the book is true: “By the end of the book you will truly be a pivot table guru, automating pivot tables using VBA, creating pivot tables with external data in OLAP cubes, and even creating dynamic reporting systems so that your managers can answer their own queries with a few mouse clicks.” If you are a power user or research specialist and this is what you want to learn to do, then, by all means, run out and buy this book. It will definitely get you there.

Comments? Questions? Qualms? Technical problems? Send an e-mail!