A Complete Guide to Pivot Tables 261
| 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.
What IS a pivot table anyway? (Score:5, Insightful)
'something really useful for analyzing data, and really great'
Sorry, sahib, but if you want me to read (let alone purchase) a book about pivot tables, I'm going to need some explanation of what they are first.
I don't pretend that I'm speaking for any number of people here. Perhaps every slashdotter besides me knows what a pivot table is, but the description of the review would seem to imply otherwise.
Could anybody else describe a pivot table?
***********
Heh, heh. Google's first hit for "pivot table tutorial" is already slashdotted.
What's a pivot table (Score:5, Insightful)
Re:Whats the most power feature in Excel? (Score:2, Insightful)
Each table of data is named for the week, ie "11-12-2004". The INDIRECT function lets a user type in the date he/she needs to look at and the model will pull in the data from the appropriate tab.
Or, using a combination of INDIRECT and MATCH, I can take two tables of data from two different sources and merge them together, as long as both tables have one column of roughly similar data (ie a list of names or serial numbers).
Pivot tables are basically SQL aggregates? (Score:1, Insightful)
ORDER BY Sport, Quarter ASC
The pivot table looks a little different, but that is essentially the same thing. Anything Pivot tables do that isn't possible in SQL?
Re:Whats the most power feature in Excel? (Score:2, Insightful)
Re:Pivot Table History (Score:2, Insightful)
Well, that pretty much cements it, Microsoft is probably fondling a patent on these at this very moment, waiting to spring forth and attack some open source project, only to have prior art slapped in their faces.
Re:Summary of what a Pivot Table is (Score:4, Insightful)
Region Product Sales
West Skirt $150
East Pants $160
West Pants $80
East Skirt $90
Pivot:
Region Skirt Pants
West $150 $80
East $90 $160
For those who don't deal with pivot tables, they are used primarily to organize data so that relationships between the dimensions can be analyzed. In the example above, you can see that sales for pants and skirts are higher in one region than the other. While you can see this relationship right away in the raw data, it beomes harder when you have much more data. Pivot provide a way to slice data
Ughhh... (Score:4, Insightful)
Part of what makes it so painful is you get stuck spending your time trying to figure out how to do something the-right-way in excel when the same task would be trivial in a database. It's not just a matter of burning everything to the ground because, if you take that approach, you are forced into accepting responsibility for rewriting the thing. Some of the stuff I've seen people make is just so thoroughly messed up as to be conceptually, not just technically, beyond redemption.
Of course, the flip side is, if people can do more stuff on their own, that's one less thing they have to bug me about... but the things they do hit you with become all the more painful.
deep hurting...
sandstorm....
When viewing the page with Firefox . . . (Score:3, Insightful)
Would you like some FUD with those tips?
Re:What IS a pivot table anyway? (Score:3, Insightful)
I'm just sayin'. .
Re:Multi-dimensional databases (Score:3, Insightful)
I don't think that's a good way to look at it.
A relation (a single SQL table, view, query) has rows and columns, yes. But the column headings are fixed. So it's only one-dimensional.
However, this one-dimensional structure is adequate to represent N-dimensional data. (By having a table with a composite primary key of N columns.) It's pointless to "take a table and make a copy of it to separate records by year"; the relational way is to simply introduce a new column called "year". And you can have different tables to represent data of different arities. (One table to store stuff independent of year, one table to store stuff that is dependent on year and whatever else, one table to store stuff that is dependent only on year. However many tables it takes to properly normalize your data.)
One dimensional may not be adequate to understand N-dimensional data, though. If you make a query, you get a one-dimensional list. If you want to view a two-dimensional matrix, you need to use a matrix report/pivot table. This can make things much easier to look at and get a feel for.
Re:Pivot tables are basically SQL aggregates? (Score:4, Insightful)
I use OO and the data pilot just ain't there yet.
If you've never used pivots (and have Excel) go play.
Are they perfect? No. I'd love to see an option to use something besides bar charts - XY plots with regressions would be the one big thing I'd like to see.
Dogu
Re:Pivot tables are basically SQL aggregates? (Score:3, Insightful)
I've created Excel pivot tables and pivot charts as front ends to OLAP cubes stored in a MS SQL Server[1].
The main advantage is end-users can drag and drop predefined data elements to change the representation of data quickly. They can also choose which members of a data element to include / exclude.
So sure, lots of this can be done with SQL where clauses and group by/order by clauses. However, its a LOT faster with a pivot table / pivot chart.
As other posters have mentioned, this is one case where the GUI beats CLI.[2]
[1] All those Microsoft products... Karma, we hardly knew ya.
[2] This from a guy who's favorite IDE is still VIM.
Re:Pivot Table History (Score:3, Insightful)
No, it's a review of a book about a feature of Excel. I see nothing that states or implies that MS invented them, other than a note that the equivalent functionality in OpenOffice isn't as advanced or comprehensive.
True, it says that OO "mimics" many of the features of Office, but it's a fair point - it's often described as a (potential) Office killer...
Lol...Pivot tables are old news but still powerful (Score:1, Insightful)
Amen, brother! (Score:2, Insightful)
Gnuplot, Python, awk, bash, and sometimes even C make your day when analyzing megabytes of data.
I ususally find it more convenient to come up with some hacked-up solution rather than to study Excel or OOo documentation only to realize later that Excel doesn't like the raw ASCII format my data is saved in.