+ Reply to Thread
Results 1 to 8 of 8

Pivot Layout - not correctly using all data?

  1. #1
    Registered User
    Join Date
    10-12-2011
    Location
    Hertfordshire
    MS-Off Ver
    Excel 2016
    Posts
    5

    Pivot Layout - not correctly using all data?

    Hi,

    I am a pivot chart amateur, so bear with me.
    Basically I have a table where one employee will enter data. I then want to use a Pivot to summarise the data, and provide each of our drivers with a summary of their data. I want to be able to generate these summaries, filtering by Driver and Date.

    However, I just cant get the pivot to pick up all the right columns, This is what it keeps doing:
    pivot.jpg

    I have tried rearranging the table, as I am sure the table layout is where the problem is. But still haven't had any luck.

    This is the data entry table:
    Data Chart.jpg

    Also, I would really like to be able to have the pivot expand with the table, so I don't have to keep re selecting the data, as it grows. If that is even a thing!

    Any help would be greatly appreciated!

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,846

    Re: Pivot Layout - not correctly using all data?

    You need to transpose your source data, so that Dates are in one column, Drivers in one column, etc.

    It's really easy to do this dynamically as a pivot source using Power Query (Get & Transform Data) - but your profile says you are using Excel 2003?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    10-12-2011
    Location
    Hertfordshire
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Pivot Layout - not correctly using all data?

    I am using 2016, just updated my profile.
    How do you do the Power Query?

    Thanks!

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,846

    Re: Pivot Layout - not correctly using all data?

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include an example of what your required pivot table layout looks like. Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    10-12-2011
    Location
    Hertfordshire
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Pivot Layout - not correctly using all data?

    I hope this attachment has worked, this is the data.
    For the pivot, I would like to just be able see the data for each of the drivers alongside, together with the column names.
    Attached Files Attached Files

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,846

    Re: Pivot Layout - not correctly using all data?

    It really would be far simpler to just transpose your table for entry - so your input table would look like this:

    Capture.PNG

    Choose 'Format as Table' and use as your pivot source, and it will dynamically resize as you add rows.

  7. #7
    Registered User
    Join Date
    10-12-2011
    Location
    Hertfordshire
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Pivot Layout - not correctly using all data?

    Excellent, Thank you for all your help Olly.

  8. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,846

    Re: Pivot Layout - not correctly using all data?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also 'Add Reputation' to those who helped you. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1