How to set up a Pivot Table?

[ejwjohn]

I use Excel 2011 on my MAC and i am having real trouble setting up what i thought would be a simple Pivot Table, i have a Book on Excel 2011 and still it escapes me.... Can you suggest other sources of simple instruction on Pivot Tables please?

I could show an example of the data i am using but not sure how to upload it for this thread?

Thanks

John

 

[RoryA]

What steps have you tried, and what problems are you having specifically?

 

[ejwjohn]

The following is an example of the data in the sheet ( the data is more comprehensive than this )

Sail Boat Name Class Type Elapse Time
1 abc 2 1:30:02
2 xyz 2 1:32:45
3 eee 3 3:45:12
12 bcd 3 1:45:33
23 fff 4 3:23:40

I am trying to create different Pivot table to show by Class type which boat sailed the course fastest and then i can assign points to 1st, 2nd and 3rd etc in each class.

I set up the pivot table filter to be Class Type and the rows shows the boat sail # within the class ( all this works) then i try to add the Elapse time and all i success in doing is getting the elapse time in the column headers and i cannot figure out how to get the elapse time for the specific boat indicate in the values fields?

Hope this helps.

THanks

John

 

[RoryA]

Can you not simply drag the field to the values area in the Builder?

 

[ejwjohn]

NO, it will not allow me to do this, in fact i cannot use that method of populating the table for any field i have to select them within the Builder and then move them between areas ....... is this a MAC OSX thing?????

Thx

John

 

[RoryA]

I mean can you not drag the field to the Values section of the Builder?

 

[ejwjohn]

OK apologise,

Yes i can do that and then i get a count of the elapse times per boat ie 1, all i want in the vlaues field is the actual elapse time value and i cannot work out how to make this part work.

Thx

John

 

[RoryA]

You have to use an aggregation function in the pivot, but if you only have one value per combination of row/column fields in the pivot, then Max, Min, Sum or Average functions should all work instead of Count.

 

[ejwjohn]

Great, thanks, do you mind if i ask two other questions on this now?

In the rows information which is Sail # in my example how do i get the row heading to be Sail#? and in the values field the Column heading should be Elapse time ?

And if i want to add more information inregard to each row ie the name of the Boat how is this done please?

Thx

John

 

[RoryA]

If you right-click a field in the table and choose Field Settings (I think - I'm not in front of a Mac at the moment) you can change the caption there.