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.
Steps:
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.
Scan
IF store_age<12 Sum, Ave gross_sales gross_sales for month=july+august+sept
endscan
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.
