Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Pivot Tables

  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Afternoon all

    I am totally new to Pivot Tables and am already struggling. I downloaded a simple demo and tuition from the web and got on OK with that but it was very limited in its selections. When I tried to transfer the concepts to my data the results were quite spurious and seemed to give me too many columns etc.

    There are several things that I will need to do but to start of simply to ease into it could somebody please take a look at the data in the attached table and tell me how I can get it to look like the mock up I have done below.

    When I tried with 3 columns it said there was too much data and when I tried it with 2 it actually produced dozens of columns.

    Any pointers would be greatly appreciated. (Please bear in mind that until last night I had never even looked at Pivot tables so what I am asking it to do may be outside of its remit).
    Attached Images Attached Images
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't know how much help this will be, I don't have 2003.

    I made a pivot table in 2007 and saved it as a 2003 workbook.

    Hope it helps.
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Stan's pivot table looks good in earlier versions of Excel too. The only thing lacking is a page field for Study. The attached version includes the page field.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Stans' file is almost there. If you drag the "location" from the "Pivot table field list" to the top of the table where it says "Drop Page fields here" you can select just the individual locations to display.

    You can edit the names of the header lists if desired as well to better match what you want

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='777320' date='28-May-2009 17:00']Stans' file is almost there. If you drag the "location" from the "Pivot table field list" to the top of the table where it says "Drop Page fields here" you can select just the individual locations to display.

    You can edit the names of the header lists if desired as well to better match what you want

    Steve[/quote]

    Thanks everybody for chipping in

    I still an't see though where the data is being put? If I start the Pivot table wizard and then select the layout options I am putting the date into the rows area and then shipments , sales, cost and margin into the data area I get a chart of sorts, as per the example below. In it's favour though it does add up the total shipments to 1073 when there are actually 1072 wherea Stans example adds up to 889 and the revenues are wrong.

    Sorry to be a pain but how do you decide what goes where to get Stans effect but with the correct totals?

    Thanks

    Editted: 18:40 to add snapshot, doh!!
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post
    When creating the pivot table you need to drag Shipments, Sales etc into the main part of the table (called the Data area), not into the Row area.

    You then need to change the Count into Sum. To do this, on the pivot table that has been generated double-click on the heading "Count of Sales " to bring up the PivotTable field dialog. You can use that dialog to change it to Sum of Sales. Repeat that for the other data.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think, I didn't highlight the whole Table, only what you had sorted. Sorry, take a look at this.
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='TonyE' post='777338' date='28-May-2009 19:09']When creating the pivot table you need to drag Shipments, Sales etc into the main part of the table (called the Data area), not into the Row area.

    You then need to change the Count into Sum. To do this, on the pivot table that has been generated double-click on the heading "Count of Sales " to bring up the PivotTable field dialog. You can use that dialog to change it to Sum of Sales. Repeat that for the other data.[/quote]

    Thanks Tony

    That is what I did (or thought I did) see screenshot 1 below, this produces screenshot 2 whereas I would like to produce screenshot 3 which was Stans example.
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='stans' post='777339' date='28-May-2009 19:11']I think, I didn't highlight the whole Table, only what you had sorted. Sorry, take a look at this.[/quote]

    Thanks Stans

    That has made another point clear to me, check the area!!
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Click on the Data button and drag it to where it says Total, then release it.
    The layout of the pivot table should change.

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='777343' date='28-May-2009 19:56']Click on the Data button and drag it to where it says Total, then release it.
    The layout of the pivot table should change.[/quote]
    Thanks Hans

    I can't believe it was that easy, like they say in the UK 'Its easy when you know how'

    Cheers all for the great input
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just for info, you can also right-click the Data box and choose Order->Move to Column (or Move to Row) to change the layout.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='777412' date='29-May-2009 08:39']Just for info, you can also right-click the Data box and choose Order->Move to Column (or Move to Row) to change the layout.[/quote]

    Thanks Rory

    I will be experimenting with every bit of advice over the weekend and that looks like a good tip, thanks

    BTW looking at your user pic you look younger and much better looking :-) a new addition?
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yep - that's my daughter. 4 months old now (seems like so much longer... )
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='777441' date='29-May-2009 14:28']Yep - that's my daughter. 4 months old now (seems like so much longer... )[/quote]

    Congratulations

    Don't worry what people say it is only the first 18 years which are the worse, with 5 daughters, 2 sons, 7 grandsons and 4 grandaughters (and a dog) I should know :-)

    Have a great weekend and remember the factor 50 for her this weekend it is going to be hot , hot, hot
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •