Analysing sales data -pivot table novice

This is my first post on here, so hello to everybody and go easy on me!

I have a worksheet containing aprox 3,500 rows of sales data covering a four year period. I want to use pivot tables to analyse this data. My experience of Excel is pretty basic- I am learning as I go a long at the moment and have never used pivot tables before. I am doing this as a favour for my Dad (data is from his Business) but also as I am interested in improving my Excel skills.

My initial questions relate to the formating of my data. I have a feeling that the way it is setup currently doesn't lend it's self well to pivot table analysis:

http://www.excelforum.com/attachment...1&d=1215250833

So rather than a seperate row for each product sold, it is split per transaction. I.e if customer x puts an order in for 5 products, those 5 products are currently shown on the same row, but split into a seperate collumn (under collumn headings: product 1, product 2 etc). These headings stretch from Collumn P - Collumn AC (highest order = 14 products)

Will this style cause me problems when creating pivot tables?
Should each product sold be shown on a seperate row?
If so, what's the easiest way achieve this?
There are 31 products - should the product i.d. be numeric?
Any other advice on how best to format the data?

Thanks in advance for your help.

Hi
If it is a one-off exercise you can manipulate the data fairly easily to convert it to one row per product. If it is going to be done regularly maybe you should fix the underlying data format.

For a one-off exercise..
use >data >text to columns to split the column with multiple products into multiple columns each with one product. Add headings to these new columns eg Group1, Group2, Group3 etc
delete all the spare rows above your headings. close the file

open a new file
use >data >import external data >new database query.
select the original excel spreadsheet as the data source.
select (by clicking on) all the columns except group 2 and group 3 etc.
click on the SQL button to display the sql and edit it by adding to the end
"where Group1 is not null
union all"

then copy and paste this text twice, delete the last union all, change the references to Group1 to Group2 and Group3 in the pasted text.
Click on the door icon in the top menu/button bar.

If you right-click in the data in the answer sheet you can edit the query and see the sql.

It takes longer to say this than to do it ;-)
An example is attached which probably will only work if unzipped in C:\ as that is where it was created.

regards