A Review of "Excel Pivot Tables and Charts"

by Peter G. Aitken

October, 2007
Reviewed by Michael S. Hines

About the Author

Mike Hines, Purdue University

Mike has 34 years of experience in data processing related jobs, mostly with mainframe computing. For 5 of those years he has been responsible for performance management and capacity planning at Purdue. He helped establish the first capacity planning and performance monitoring role in ADPC (now ITaP). He has served as a reviewer and session coordinator at CMG National.

Wiley Publishing, Inc., 2006, 276 pp
ISBN-10: 0-471-77240-2

Many of us probably have used the SAS® "PROC CROSSTABS" to look at the relationships between data. Batch SAS® requires program changes and batch runs to look at alternative analyses. Even interactive SAS® requires a program change and "PROC CROSSTABS" re-execution to see the new results in the output window. Microsoft Excel® Pivot Tables allows us to change our analysis models with the ease of point and click. For those not comfortable working with Excel or Pivot Tables there is even a Pivot Table Wizard to help you get started.

Pivot Tables work with data in Microsoft Excel spreadsheets. As you probably know - Excel data can come from many sources - tables entered by hand; data imported from ‘flat files' - either columnar data or field delimited data (comma separated variables [CSV] being a special case); or data connected to Excel via On Line Analytical Processing (OLAP). Part 2 in "Excel Pivot Tables and Charts" demonstrates various methods from connecting to data. The book uses a set of Excel data tables to allow you to carry out the steps and see the results presented in each ‘chapter'. This data is available for download from the Internet - allowing you to focus on learning the new techniques, rather that entering data tables.

The book contains ten major parts. Each part begins with a list of "Tips" that provide a quick reference to a topic in the part. What this book calls parts is usually referred to as chapters in other books, and what this book calls Tips are usually sections in a chapter. The notation can be a bit confusing at first.

Part 1 is "Understanding Pivot Tables and Charts". This section gets you started with Pivot Tables and Charts by quickly demonstrating setting up a Pivot Table and Pivot Chart. Detail step-by-step instructions provide rapid learning of the concepts presented and allow you to quickly see the results and power of using Pivot Tables and Charts. This method of instruction is used throughout the book - providing a ‘hands on' learning experience.

As previously noted, Part 2 is "Understanding Data Sources for Pivot Tables".

Part 3 covers "Using Pivot Tables and Formatting". This includes using the Pivot Table Wizard, Layout Box, Tool Box, Menu, and Options.

Part 4 addresses "Working with Pivot Table Components". This covers working with rows, columns and the data areas.

Part 5 continues Part 4 with "More About Pivot Table Components". This includes calculated fields, showing and hiding details, and methods for grouping data into categories.

Part 6 introduces Pivot Charts in the chapter titled "Understanding and Using Pivot Charts". We learn how to do graphing from Pivot Table data in this chapter.

Part 7 expands on the previous parts with "Using Pivot Tables with Multidimensional Data". The concept of multidimensional data is introduced, creating ‘cubes' is discussed, and creating of Pivot Tables from ‘cubes'.

Part 8 shows how to get data out of Pivot Tables with "Getting Hard Data from a Pivot Table". The "GETPIVOTDATA" function is demonstrated to extract various types of data from Pivot Tables.

Part 9 adds to Pivot Table extended options in "Pivot Table Alternatives". These are not other tools - but discussion of extended Pivot Table features to control data content and display options. For example: dealing with subtotal alternatives; methods of hiding and showing data; working with database functions; and filtering data are demonstrated.

Part 10 is an advanced chapter on using the Microsoft Visual Basic for Applications (VBA) to write programs that work with Pivot Tables. This is titled "Programming Pivot Tables with VBA".

The book concludes with three appendices:
Appendix A. Troubleshooting Pivot Tables and Pivot Charts.
Appendix B. Excel Version Differences for Pivot Tables
Appendix C. An Excel Chart Primer.

If you clearly understand data relationships and want to run periodic reports on data over time - then batch job that can be scheduled is the best approach. But if you are doing research and development to consider data interactions the Excel Pivot Table and Pivot Chart are probably the most efficient means to perform the analysis. The book "Excel Pivot Tables and Charts" will be a good investment of your time and money to get a quick start in learning how to use these capabilities efficiently and effectively.

I obtained this book through the World Wide Web from Amazon.com as a used book. I was able to get a copy at much less than retail price. The book arrived quickly and was in new book condition. I merely mention this because we are frequently asked to do a lot with minimal financial support. This is one way to advance your personal professional development at minimal cost.