Pivot Tables Excell 2000/2003

Discussion in 'Windows Desktop Systems' started by sean.ferguson, Jun 18, 2006.

  1. sean.ferguson

    sean.ferguson Moderator Folding Team

    Fife; Scotland

    I am needing some help on how to create Pivot Tables in both Excel 2000 and 2003 (If there are indeed any differences). I am starting a new job tomorrow, and although I wont need to do this immediately i will need to learn. I only have OOo too so dont have a local win32 installation to try it in.

    Is there anywhere that I can see some detailed steps, and maybe some examples? :laugh:
  2. ming

    ming OSNN Advanced

  3. LeeJend

    LeeJend Moderator

    Fort Worth, TX
    To start with Pivot tables are actually a Database Query tool. Like using Access, Paradox or Dbase to write a simple script.

    What Excel is doing is auto generating script code for you from a predefined list of possible queries. Access, Paradox or even the ancient DBase could all do this with their report generators.

    1) Fill in your database (i.e. spreadsheet) with whatever you want (dates, names, numbers, text). Columns are database fields, rows are data for a given entry. Actually I should say fill it in with everything! The power of a database if having a full set of knowledge that can then be queried to produce data subsets you want.

    2) Open a pivot table (i.e. your query report)

    3) Open the pivot wizard (i.e. field list) and select the fields you want to operate on sort/sum/ave/etc. Note this can be a lot harder than just writing a plain query English.

    IF store_age<12 Sum, Ave gross_sales gross_sales for month=july+august+sept
    Takes a few seconds to write and is obvious. It would take me 5-10 minutes to make a pivot table for that.

    If I wanted standard deviation or a complex function I'm screwed because it is not built into the Pivot function.

    Basically a pivot table is Data Query for Dummies. Anybody with even rudimentary programming skills could do better with scripting it themselves.

    The true Database languages will auto generate queries (reports) that you can then cusomize the scripts for. Much more powerfull.

    Things to watch out for:
    1) Learn your function list!
    IF dates are entered as m/d/y you may not be able to do an analysis on say months unless the function Getmonth (date) exists.
    2) Spot check you r resulst by copy the sheet to a new files sorting the spread and summing by hand.
    3) Find out if you can add data fields or rows to the data spread sheet later and how and if the pivot tables will tolerate the change or if you will need to be rebuild them. This always comes up.
    Last edited: Jun 18, 2006