Slashdot is powered by your submissions, so send in your scoop

 


Forgot your password?
Close
typodupeerror
×
Books Media Software Operating Systems Windows Book Reviews IT

A Complete Guide to Pivot Tables 261

Posted by timothy from the suspense-is-killing-me dept.
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.
This discussion has been archived. No new comments can be posted.

A Complete Guide to Pivot Tables More

A Complete Guide to Pivot Tables

Comments Filter:

  • What IS a pivot table anyway? (Score:5, Insightful)

    by Hanzie ( 16075 ) * on Friday November 19, 2004 @06:02PM (#10869568)
    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.

    • Re:What IS a pivot table anyway? (Score:5, Informative)

      by FrereTuck ( 711010 ) on Friday November 19, 2004 @06:03PM (#10869587)
      Read this: http://www.cpearson.com/excel/pivots.htm
      • OK, I did a little research. A pivot table is a fancy name for a feature in Excel that adjusts the calculation squares in your spreadsheet as you change the presentation format. This allows you to look at the data in your spreadsheet in a bunch of different formats without having to rearrange all the calculation squares yourself. I can see some aura of elegance there for the RDB and AWK impaired. You guessed it, I don't use excel.
        • Some might argue (I wouldn't, but some would) that the "RDB and AWK impaired" are the people who know too much about those tools, and not enough about anything else.

          I'm just sayin'. . .
          • It's possible, but this RDB and AWK impaired person was able to answer the question that dozens of excel impaired couldn't or wouldn't. See my other posts on topic. I can see it's handy, but I still can't see a friggin book on the topic. Then again, there was this book on the shelf at Fry;s the other day. Something about Windows XP hacks. It was nothing more than a list of all the features in windows and the suggestion that you might want to modify them and tha the registry was the place to do it and that t

    • Re:What IS a pivot table anyway? (Score:4, Funny)

      by KernelHappy ( 517524 ) on Friday November 19, 2004 @06:05PM (#10869605) Homepage
      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.
      • Bad analogy.

        Movie trailers tell you the whole shitty story, so we don't have to watch shitty movies. Then movie sales plummet so prices are raised and they compensate with stupid explosions in surround sound and digital video instead of film.

    • Re:What IS a pivot table anyway? (Score:5, Funny)

      by Chundra ( 189402 ) on Friday November 19, 2004 @06:11PM (#10869711)
      I'm going to put MCPTE (Microsoft Certified Pivot Table Engineer) on my resume. Wheee!

    • Re:What IS a pivot table anyway? (Score:5, Funny)

      by roman_mir ( 125474 ) on Friday November 19, 2004 @06:14PM (#10869749) Homepage Journal
      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 History (Score:5, Informative)

      by bstarrfield ( 761726 ) on Friday November 19, 2004 @06:15PM (#10869765)

      Pivot tables were originally developed in Lotus Improv [wikipedia.org], 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? [apple.com]

      • Pivot tables were originally developed in Lotus Improv

        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:Pivot Table History (Score:3, Insightful)

        by Tim C ( 15259 )
        I suppose this is another example of Microsoft getting credit for company's innovations?

        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...
      • I just read over the Wikipedia article. Is this not what Access is? Conceptually that is...
        • MS Access pivot tables are spelled "crosstab query".
          • After I posted that I fired up OOo, and saw what it did... A long time ago I did some Access work, so see it is very different. But they both do visual data manipulation. Pivot tables may be cool, but if you are doing that level of work in a spreadsheet, regardless of how good it is, you are using the wrong tool. (Not that Access would be the tool to use....). That said, Im the kinda guy who thinks a word processor should be used for word processing, and a desktop publishing system should be used for deskto

      • Re:Pivot Table History (Score:3, Informative)

        by geg81 ( 816215 )
        I suppose this is another example of Microsoft getting credit for company's innovations [apple.com]?

        I'm getting tired of just about every discussion about Microsoft being used as an opportunity by Apple fans to promote their favorite company. Keep that sort of stuff to the Apple groups, please. Whether or not Microsoft copied a feature from Lotus Improv has nothing to do with Apple.

        Furthermore, it is stupid for Apple fans to point fingers when it comes to copying: without copying other companies' innov
      • Pivot tables were originally developed in Lotus Improv,

        They're a lot older than that -- they're just cross-tabulations with a GUI. SAS "proc tabulate" had these in the 80s (without the GUI).
      • This sounds like a nifty product. Since it was written for NeXT I wonder if the sources are still around and usefully compilable for OS X.

        Which brings up the suite of office software that was developed for NeXT and which is owned by Sun but which will never see the light of day. IT should port quite easily.

        Sigh.

      • I suppose this is another example of Microsoft getting credit for company's innovations [apple.com]?

        Improv wasn't an Apple innovation; Lotus Improv has nothing to do with Apple at all. Improv was a third-party application for NEXTSTEP.

    • 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.

      Perhaps it could just be that if you don't know what it is, the book isn't trying to sell to you. For example, would you expect The Washington Manual Hematology and Oncology Subspecialty Consult [amazon.com] to explain to you what hematology and oncology are?
      • But I think one could expect to be given a clue if the review of The Washington Manual Hematology and Oncology Subspecialty Consult to explain it, especially if that review appeared in a general purpose tech site.

        Besides which, hematology and oncology are not obscure terms, and it is quite easy to find a definition. Pivot Tables, otoh, are a specialized phrase to a specific program (or programs, I guess, given the mentions of the Lotus product above), and even the review itself points out that this is a "
        • Saying, "If you have to ask, you don't need to know," in this situation is a bit rude.

          I raised the possibility that this book wasn't intended for a general audience and no rudeness was intended. The fact that it's posted on the front page of Slashdot doesn't *necessarily* imply that it's a general topic -- though of course it could be. However, you are correct in saying that if it IS intended for everyone that an overview of what pivot tables are would be helpful.
    • I'd really like to know what the hell a pivot table is.

      First, you've got to have some data to describe. Here's one I'm looking at right now: I've got a table in my Oracle database that lists services doctors performed on given dates, and how much they charged for them.

      A conventional report would describe that data one-dimensionally. Total amount of money each doctor charged or total amount of money charged in each date range.

      A pivot table (or matrix report, as Oracle Reports calls them) shows the sam

      • So there's a whole book dedicted to a friggin format change? Awk handles this with ease.
        • What IS Awk anyway?

          You insensitive clod
          • AWK is tool that lets you write scripts for editing.

            Plenty of tutorials about. I'm no expert so I use them.

            Quoting from the intro to one [wsi.edu.pl]. "Awk, named after its developers Aho, Weinberger, and Kernighan, is a programming language which permits easy manipulation of structured data and the generation of formatted reports. "

            Be careful, you'll never be the same again.

      • How about one Solaris sys admins can relate to??

        I take the output of sar -d, pretty it up using PERL so that each line looks the same and the fields are tab separated (each line contains the date/time, partition, and the associated sar output). I then bring it into a spreadsheet using the 'import external data' feature (so I can point it to different files) and see what the relative queue lengths, service times, et.al. of all the disks are relative to each other and across a given time frame. It is very
      • So essentially, a pivot table is just a tool for putting data from a RDBMS into the spreadsheet form you know and love.

        And allows you to choose any subset of the fields of the spreadsheet for display in a mini-spreadsheet, lets you choose any field you want to serve as a "grouping agent" and dynamically updates subtotals for you. Plus, the subtotals and totals field can be changed to a variety of formulae, including Sums, Averages, even just counts. And you can choose whether or not to show every record t
    • A pivot table can be used to combine data in a similar set together. Say you have a lot of data points in a spreadsheet, say inventory levels for socks by the purchase order they come in on. You have the number of socks, it's PO number, it's color, and the date the shipment is received.

      You can use a pivot table turn this data points (all data for that point is contained on a row, with the top row giving the header for each column quantity, color, PO, date,) into a table. This way you can have a table fo
    • I'd really like to know what the hell a pivot table is

      Obviously it's a table with one leg shorter than the other. They're useful for annoying the rest of your family at dinner time.

    • A simple example of pivot tables (Score:5, Informative)

      by sjbe ( 173966 ) on Friday November 19, 2004 @06:28PM (#10869920)
      The best way to understand pivot tables is probably by example. Lets say we have a spreadsheet with four categories of data: Country, Continent, GDP, and Population. These are listed in columns, with corresponding entries next to each other like so (apologies for bad formatting):
      Country Continent GDP Pop
      China Asia $1000 1,000,000,000
      Japan Asia $5000 100,000,000
      USA North Am $15000 280,000,000
      Then lets say we are interested in finding the population of countries on our list which a located in asia. Pivot table provide a fast way to sort data as well as conduct simple mathematical operations on lists of data.

      Using a pivot table I could end up with a matrix that looks like:
      Continent Country Population
      Asia China 1,000,000,000
      Japan 100,000,000
      North Am USA 280,000,000
      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:
      Continent Country GDP
      Asia China $1000
      Japan $5000
      North Am USA $15000
      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.
      • That's one-dimensional data. "Country" is a single-column primary key of that data. Each country exists in only one continent. Each country has only one population.

        Here's a way to spot a one-dimensional list: if the number of columns is fixed, it's one-dimensional. ("Continent, Country, Population" is fixed; it's not dependent on your data.)

        If you introduced a "year" column, then it would be two-dimensional data. China's population in 2003 was different than in 2002. And China's population in 2002 was d

        • That's one-dimensional data. "Country" is a single-column primary key of that data. Each country exists in only one continent. Each country has only one population.

          I'm aware of that. Please note that I was responding to a question about what pivot tables were so I answered with the simplest possible example. There are obviously MANY more sophisticated things that can be done.

      • Thanks sjbe.

        So you can use a pivot table to quickly sort and group data. When does it make sense to use a pivot table instead of a simple sql query such as:

        SELECT sum(Pop) FROM Country GROUP BY Continent ;
        or
        SELECT sum(GDP) FROM country GROUP BY Continent ;

    • > Heh, heh. Google's first hit for "pivot table tutorial" is already slashdotted.

      That rocks, everyone. I'm proud of you.
    • Seems to be like a SQL query with a "group by" clause, except that it's been make user friendly, so you need a whole book about it, and it also tries to deal with the fact that it's a spreadsheet and the data has been mangled accidentally by the user.
    • From what I'm reading, and I may not understand correctly, a pivot table is something like the results of this SQL query:

      select
      category,
      sum(values)
      from categorized_values
      group by category
      order by category

      The "pivoting" refers the ability to quickly change the grouped-by and ordered-by columns.

      Now how wrong am I?
      • sayeth the sig "Am I the only slashdot user that thought that Minority Report qualified as a horror movie?

        well if its the plot you you are refering to, I'd have to say it would be fair comment referring to it as horror, much of Philip K Dick's work is scary as hell. Probably not suprising considering he was a paranoid schizophrenic genius with a bad speed habit.

        I don't know though are Orwell's "1984", or Bradbury's "Farenheit 451" Horror? what is a good definition of horror?

      • You're absolutely correct. My problem is when that query you specified takes 15-60 minutes to complete (~300,000 records returned from a 5+ table join, then summarized in VB/FoxPro). The great thing about pivot tables is you can start with fairly raw data and you don't have to use more SQL to collapse it, you just drag fields around. I've been using them a lot in the last couple of days, to check the output of queries against the database to make sure they have the correct joins. Write the data to a CSV fil

    • I'd really like to know what the hell a pivot table is.

      This is /. All you need to know about pivot tables is that they are a feature of a Microsoft tool.

    • Well, crap, I've been using them for years, had I known that I could write a book on such an elemtary topic as pivot tables and pivot charts... I'd have made some money too... Well that pisses me off...
    • If you've ever used SAS, then you're familiar with PivotTables. PROC TABULATE is a very powerful function in SAS that does everything PivotTables do and more.

  • The first rule of PivotTables... (Score:5, Funny)

    by Aardpig ( 622459 ) on Friday November 19, 2004 @06:04PM (#10869593)

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

  • Multi-dimensional databases (Score:5, Interesting)

    by gtrubetskoy ( 734033 ) * on Friday November 19, 2004 @06:04PM (#10869596)

    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.

    • A traditional relational database has two dimensions - rows and columns.

      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 i

  • Whats the most power feature in Excel? (Score:3, Interesting)

    by stecoop ( 759508 ) on Friday November 19, 2004 @06:04PM (#10869599) Journal
    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?

  • What's a pivot table (Score:5, Insightful)

    by Pedrito ( 94783 ) on Friday November 19, 2004 @06:08PM (#10869649)
    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.

  • And then ! (Score:2, Funny)

    by ilikeitraw ( 706793 )
    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.

  • Summary of what a Pivot Table is (Score:5, Informative)

    by Wrexen ( 151642 ) on Friday November 19, 2004 @06:16PM (#10869786) Homepage
    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.

    • Re:Summary of what a Pivot Table is (Score:4, Insightful)

      by UnknowingFool ( 672806 ) on Friday November 19, 2004 @06:35PM (#10870003)
      Here's another example where one of the dimensions is not a number

      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
    • There's a single-column primary key to that data (name), so it's one-dimensional. Your second view (average height by age) can be produced without a matrix report/pivot table. Where you do need pivot tables is where your data are more complex:

      Name Age Height (cm)
      ---------------------
      Jane 22 174
      Jane 21 174
      Jane 20 174
      Jane 19 173
      Jane 18 171
      ...
      Dick 22 212
      Dick 22 212
      ...
      Dick 9 105
      Mary 24 150
      Greg 24 198
      Dave 23 244

      The above data points describe height by age and person. It's two-dimensional data, but you're show

  • Pivot table was the foosball table (Score:3, Funny)

    by FerretFrottage ( 714136 ) on Friday November 19, 2004 @06:20PM (#10869829)
    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.

  • Excel Feature? (Score:2, Informative)

    by Geste ( 527302 )
    Whaaaa? 38 pivot-table posts on /. and nobody has mentioned Lotus Improv yet? Fixed.
  • Are the object web components covered? I use them at work to genereate web based pivot tables and the API is far from fully documented.

  • Ughhh... (Score:4, Insightful)

    by Darth_Burrito ( 227272 ) on Friday November 19, 2004 @06:41PM (#10870077)
    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....

  • PIvot tables? Yawn (Score:4, Informative)

    by pclminion ( 145572 ) on Friday November 19, 2004 @06:51PM (#10870183)
    From what I can tell, these "pivot tables" are just a primitive form of concept analysis (data mining).

    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 [waikato.ac.nz] and Orange [uni-lj.si].

    Weka in particular is extremely easy to use and you don't have to be a researcher to figure it out.

    • 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.

      Good lord, I think I can deduct an hour of slashdot browsing. Extremely useful, thanks!

  • Description of a Pivot Table (Score:3, Informative)

    by chrisatslashdot ( 221127 ) <spamforchris@@@yahoo...com> on Friday November 19, 2004 @06:59PM (#10870289)
    Suppose a database of widget info...cost, model, storage location, color, etc.

    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...

    SELECT color,
    location,
    SUM(inventory)
    FROM table
    GROUP BY color, location


    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.
    Database output:
    location |color | inventory
    warehouse A| red | 100
    warehouse A| blue | 123
    warehouse B| red | 0
    warehouse B| blue | 50

    Pivot Table
    ------------red blue
    warehouse A 100 123
    warehouse B 0 50

  • When viewing the page with Firefox . . . (Score:3, Insightful)

    by acceleriter ( 231439 ) on Friday November 19, 2004 @07:00PM (#10870297)
    . . . it says at the top
    Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 5.01 or later or Netscape Navigator 6.0 or later.

    Would you like some FUD with those tips?

  • Hmm... (Score:2)

    by Pivot ( 4465 )
    I must say that I indeed have this really nice solid wood table that I got a ikea. I use it mostly for food though, not so much numbers..

  • Ok (Score:2)

    by Hard_Code ( 49548 )
    Who else thought the entry was about some form of air hockey, foosball or pinball machine?

  • Pivot table info and resource (Score:4, Informative)

    by kendor ( 525262 ) <kennethfine@hotmail.com> on Friday November 19, 2004 @07:19PM (#10870487)

    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. :)

  • Quantrix does it better (Score:3, Informative)

    by rjrjr ( 28310 ) <rjrjr@poOPENBSDbox.com minus bsd> on Friday November 19, 2004 @07:42PM (#10870672) Homepage
    If you like pivot tables, you'll love Quantrix [quantrix.com]. It's a multi-dimensional spreadsheet that picks up the ball that Lotus Improv dropped.

    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.

  • capabilities of the Microsoft Office products.
    You, traitor...
  • sc can do this too. Actually any spreadsheet package from the 1980s can create a spreadsheet using data from another spreadsheet. It works the same way as equations do, except accross spreadsheets. big deal.

    the only real difference is that PivotTable in excel can be done automatically based on the names of columns, instead of having to go to the effort of writing a 3-5 line awk or perl script.

    btw- I analyze most data using gnuplot. it's an extremely powerful and easy to use package. But graphs analysis is
  • 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 (Score:3, Funny)

    by Animats ( 122034 ) on Saturday November 20, 2004 @02:52AM (#10873014) Homepage
    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.

Slashdot Top Deals

He has not acquired a fortune; the fortune has acquired him. -- Bion

Close