• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Pivot Tables Excell 2000/2003


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

Members online

No members online now.

Latest posts

Latest profile posts

Hello, is there anybody in there? Just nod if you can hear me ...
What a long strange trip it's been. =)

Forum statistics

Latest member