A Complete Guide to Pivot Tables

r3lody (Raymond Lodato) writes "Like most people, I've only scratched the surface (well, maybe I gouged it a bit) of the capabilities of the Microsoft Office products. There are more features buried in them than most users ever discover. I use Microsoft Excel frequently in my job to analyze all sorts of data. When they came out with Pivot Tables, I dabbled in them and found several uses for them. However, documentation being what it is, I never really got to understand and utilize Pivot Tables' full capabilities. Now, Apress has published A Complete Guide to Pivot Tables: A Visual Approach, by Paul Cornell." Read on for the rest of Lodato's review.

A Complete Guide to Pivot Tables: A Visual Approach
author	Paul Cornell
pages	368
publisher	Apress
rating	10/10
reviewer	Raymond Lodato (rlodato AT yahoo DOT com)
ISBN	1590594320
summary	A well-researched step-by-step tutorial on the use and programming of Microsoft Excel PivotTables for data analysis.

If you have any need to analyze data in Excel, you must read this book. I learned so much more about PivotTables after I read it that they are now actually useful, rather than just being something I would occasionally try out. Every chapter is packed with excellent information in an easy-to-follow format. A beginning to intermediate user can understand most of the book; only the chapter on programming PivotTables requires intermediate to advanced knowledge to fully comprehend.

Cornell takes a tutorial approach to explaining what PivotTables are, what they are capable of doing, and how you can apply them to your needs. Each chapter in the Complete Guide gives you an overview of a single topic, a series of step-by-step examples, a Try-It section for more practice, and a summary of next steps. The book was written for Excel 2003, but most of the techniques can be applied to Excel 2002 and even Excel 2000.

When you read this book, I would recommend that you sit at your computer to try these techniques as you read them. I tried to just read the book at first, but you really get itchy to try each feature out. Take it in sequence, as there is a definite building from one chapter to the next.

Chapter 1 gives you an overview of the PivotTable feature, what it's meant to do, and why you would use it. Chapter 2 starts the in-depth training of building basic PivotTables from Excel Lists, external data sources, other PivotTables, etc. It also includes tips on formatting the information and tweaking the fields and table to your liking. Chapter 3 goes even deeper, with information on advanced settings, filters, calculated fields, and other little gems that make analysis easier. These three chapters complete your basic training and lead to chapter 4, "Using PivotTables in the Real World." Paul proceeds to give not just one, but three examples of how PivotTables could be used to provide insight into company operations.

While everything I'd known about PivotTables before picking up this book was covered in the first three chapters, the last three chapters explain additional capabilities that really make Excel valuable for data analysis. Chapter 5 explains PivotCharts, which are simply a graphical representation of the information shown in a PivotTable. Paul goes into detail on the different chart types and how they interact with the underlying PivotTable.

If you need to distill and analyze multidimensional, relational data, PivotTables are up to the task, as chapter 6 will demonstrate. The book describes how you use cube files, OLAP databases, and Microsoft Query to get the data and manipulate it. If you're a really capable programmer, comfortable with VBA, chapter 7 shows you how to work with PivotTable programmatically. There is program after program showing you exactly how to get at the data, massage it, and create the resulting PivotTable.

Finally, there is one appendix that describes the differences between Excel 2000, Excel 2002, and Excel 2003. I was originally reviewing this book while using a computer with Excel 2000. I was delighted to find out that most of the information in the book works exactly as Paul described (although the screen shots didn't match). I did recheck some of the examples on my other machine running Excel 2002, and had no problems at all.

Granted, this book is specifically written for Microsoft Excel. However, OpenOffice, the free competitor from Sun MicroSystems, mimics most of the Microsoft Office suite. How does it compare, you ask? Well, OpenOffice has a similar facility to PivotTables, called DataPilot; however, DataPilot is primitive in comparison. For example, you must select the data to summarize, choose (from the menu bar) Data --> DataPilot --> Start, then drag the fields to the appropriate place in the diagram and click OK. Like Excel, you can freely move the fields between row, column, and data areas, and change the data operation from Sum to Min, Max, or a number of others. Unlike Excel, there isn't much more you can do. You don't have Page fields; you can't sort fields on their data; PivotCharts aren't represented; and there's no programming. If you only want to do simple data analysis in OpenOffice, you can get the basics from chapters 1 and 2 of this book.

---

You can purchase A Complete Guide to PivotTables: A Visual Approach from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.

The first rule of PivotTables...

[Aardpig]

...is you do not tell anybody what PivotTables are.

Re:What IS a pivot table anyway?

[KernelHappy]

Think of it like a movie, if the article told us what a pivot table was, we wouldn't need to buy the book, then book sales would plummet and then the publishing industry would start building anti-concept copying mechanisms into text. I think it's all for the best it remains a mystery.

And then !

[ilikeitraw]

Pivot tables are priceless. I worked in a company with about 100 client service people, and they had no idea how they worked.
I would have to query the data out of oracle for them, and pivot the data for them, so they could send the report to the client.

Then, they would get HUGE commissions checks for pushing the "send" button in Outlook, and I would get nothing.

Then.... I quit.

Re:What IS a pivot table anyway?

[Chundra]

I'm going to put MCPTE (Microsoft Certified Pivot Table Engineer) on my resume. Wheee!

Re:What IS a pivot table anyway?

[roman_mir]

Pivot table means that; it's pivotal. I mean, you're a -- you've been given pivotality, and you're viewed as a pivotal entity. And therefore the relationship between the data and tables is one between pivotal entities."

Pivot table was the foosball table

[FerretFrottage]

where I last worked. Sure everyone one knew what you meant when you said "let's take a look at the data in a pivot table", but at least it sounded better then "let's foos". Plus you could claim to be the PTC (pivot table champion) and not know a damn thing about Excel.

Re:Dissing OpenOffice

[Pedrito]

Many, many people live and die by pivot tables,

And I would suspect many more die trying to figure out what they are.

Re:Whats the most power feature in Excel?

[T3kno]

I like the export to xml feature so that I can xslt out all of the cruft and insert the data where it belongs, in PostgreSQL.

Re:What IS a pivot table anyway?

[Anonymous Coward]

Was that meant to sound like something George W. Bush would say? If he knew big words like data and table...

SysAdmins, please play dumb about Pivot Tables...

[ManyLostPackets]

I don't don't know about anyone else, but I was foolish enough to show seasoned analysts the minor miricle of pivot tables.

Guess who the new data analyst is?
(along with the e-mail guy, the PBX guy, script monkey, tech support dude, etc...

Grokk-me-not

Much easier than the old way

[Animats]

Back in the old days, we had to run all the cards through the sorter to sort on the desired field. Then we'd wire a IBM 407 tabulator board to do a control break on the desired field, and print the total. If someone wanted an average, we had to cable up the 519 summary punch to the 407, and wire a tabulator board to send the total and count to the punch. The 407 couldn't divide, so then we'd run the summary cards through the 602A multiplier to do the divide. The 602A would do A=B/C and punch A into the same card that contained B and C. Then the summary cards could be resorted into the desired report order and run through the 407 with yet another board to generate the report.

You guys have it easy.