Slashdot Mirror
A Complete Guide to Pivot Tables
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.
I'd really like to know what the hell a pivot table is. From reading the review at Amazon and the one here a 'pivot table' is:
'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.
********* sig: If you don't like the law, get filthy stinking rich, and buy a better one.
...is you do not tell anybody what PivotTables are.
Tubal-Cain smokes the white owl.
For those who do not understand what multi-dimensional database is, here is how I explain it to myself:
A traditional relational database has two dimensions - rows and columns. Now if you take a table and make a copy of it to separate records by year (e.g. 2004data, 2003data, 2002data, etc.), you get a 3rd dimension. And if you were to take your entire database and make a copy of it to separate it by something else, you now have a 4th dimension. Of course the problem with all this would be inability to tie data in one database to data in another in an easy way.
A multi-dimensional database is something that allows you to add dimensions ad-infinitum _and_ query it in a clean way.
I rank Pivot Tables about #1 in usefullness. Followed by autofilter, Vlookup, conditional Formating and regular old little VBS scripts. What are some of the higher features you guys use?
I'm on the edge of my seat. Why would I want to read a book about Pivot Tables? It could be the best book every written in the history of Pivot Tables, but it may as well be a book on Gorglemopenchausers. I mean, I realize it's a book review, but even a sentence or a short paragraph saying, "A pivot table is basically XYZ" would have added a great deal of value to me as a reader.
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.
Pivot tables allow you to cross count units withing categories. Imagine you hava a table of stock, you have shoes. Shoes have size number and model id. A pivot table can easily tell you the number of shoes of each size of each model. Of course you can get the same values out of simple SQL query (i.e. select count(*),model,size from shoes group by model,size) The difference is the pivot table will look like a double entry table instead of the long listing the query above might provide. I'm not sure I know enoguh to full a book about this. Cheers.
Pivot tables were originally developed in Lotus Improv, Lotus's incredibly advanced word processor for the NeXT machine. Lotus attempted to develop a new paradigm (can't believe I used that word) for spreadsheet interaction, something more sophisticated that +A1+@sum(B1..B3). Pivot tables were a component of this formulaless spreadsheet.
Improv was, is friggin' amazing.
I suppose this is another example of Microsoft getting credit for company's innovations?
/* Dang, I can't type that well. */
Say you have data like this:
:)):
Name Age Height (cm)
---------------------
Jane 22 174
Dick 22 212
Mary 24 150
Greg 24 198
Dave 23 244
You can use pivot tables to come up with tables like this (but with correctly calculated values
Age Avg Height
--------------
22 190.5
23 244
24 174.5
For the most simple case, take any N-dimensional data, choose one of the dimensions to be your rows, choose one of the dimensions to be your columns, and choose a function of a dimension to be your field values. The more dimensions and data points you have, the more useful this becomes.
Many, many people live and die by pivot tables, well people that use Excel to any extent do, it is only obscure to people that use Excel as if it was a pad of paper. As such, any spreadsheet product that did not have a similar feature that was at least as usefull and powerfull would indeed be primative, and pretty much useless to anyone using Excel professionally. Don't let zealotry dictate what tools you use.
"I use a Mac because I'm just better than you are."
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.
"Look Lois, the two symbols of the Republican Party: an elephant, and a fat white guy who is threatened by change."
Many, many people live and die by pivot tables,
And I would suspect many more die trying to figure out what they are.
Whaaaa? 38 pivot-table posts on /. and nobody has mentioned Lotus Improv yet?
Fixed.
Using a pivot table I could end up with a matrix that looks like:Now I have the data sorted by continent. If I decide I'm not interested in population but instead in GDP, it is a simple drag and drop operation to get a table like:Basically pivot tables let you explore lists of data very quickly and efficiently. If you deal with lists of data regularly like I do, they are one of the most indespensible features in a spreadsheet. Excel has the best ones I've used but most modern spreadsheets have some version of them.
Just sort of a general comment ... I think the capability of office suites to do complicated things is directly proportional to the amount of pain felt by regular IT staff. Seriously, it's nice that people can do all sorts of neat stuff and track data outside of the system... because, often due to time constraints, it's not always possible for IT staff to facilitate all requests... but then they come ask us for an on-demand parameterized report that works off an ms sql server database, mysql, access, and excel.... and we get stuck doing it regardless of the relative value proposition and it takes a lot of time and ends up crashing when someone locks the excel file or fubar's the access database... and it's our fault.
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....
Anybody reading this article who actually thinks these pivot tables sound "powerful" should look into some of the real row-based data mining tools out there. For starters I suggest looking at Weka and Orange.
Weka in particular is extremely easy to use and you don't have to be a researcher to figure it out.
A pivot table:
- A rectangular table with rows and columns
- Along the top you some pick attribute(s) of the widget
- Along the left side you pick other attribute(s) of the widget
- You pick what goes into the interior of the table and how it is summurized
So you could compare color(along top) with location(along left side) and choose to see the sum of the inventory levels in the interior.or...
But in matrix form so that you end you with one cell for each color+location instead of one record. So the size of the matrix grows approximately by a power of 0.5 with respect to the number of results.
Simple people talk of people, better people talk of events, great people talk of ideas.
Would you like some FUD with those tips?
CEE5210S The signal SIGHUP was received.
There seems to be some natural and/or knee-jerk confusion that pivot tables are some Microsoft-only creation. For some folks that naturally means that they'll never want to use them. That's too bad, a little bit like concluding that referential integrity or relational table structures are "so Microsoft" just because they're discussed in SQL Server documentation and literature.
Best discussion I've read of pivot tables as a generic tool for managing data appears in O'reilly's thin "Transact SQL Cookbook". Excellent book. The first few chapters are devoted to pivot tables alone. According to the authors, PTs are fundamental to solving many data storage and display problems. Have a read, then post more. :)
I'm no M$ lover, but pivot tables beat the crap out of SQL. Why? Ease of use. To build a pivot table takes about, ohhh, 30 seconds if you're clueless. To then generate a graph, 1 second. Don't like the particular pivot/chart you have set up? No problem, grab the field you don't want, pull it out, drag a couple more in and you're done. They make it incredibly easy to visualize the data you're working with and take very little effort to set up or change. They also provide almost effort free data filtering and very simple but powerful drill down options. We use them to figure out failure trends on production data.
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
Disclaimer: I'm not a shill--not even a customer, in fact. But I'm a friend of the author, and was a contributor to Quantrix's NeXTstep based predecessor.
Anything Pivot tables do that isn't possible in SQL?
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.
A Human Right
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
You guys have it easy.
Unfortunately my mod points expired yesterday ...
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.