+ Reply to Thread
Results 1 to 5 of 5

Sorting Across Column Labels in a Pivot

  1. #1
    Registered User
    Join Date
    04-19-2011
    Location
    Leeds
    MS-Off Ver
    Excel 365
    Posts
    48

    Sorting Across Column Labels in a Pivot

    Hi all,

    I have created a pivot table that has item numbers down the left as Row Labels and store format across the top as Column Labels (A, B, C) with the values being sales.
    I can sort the data by item number and by Grand Total (all formats combined) but I can't sort by individual format (ie Store Format A highest to lowest).
    I'm absolutely sure I have been able to do this previously (maybe I'm just giddy for the weekend).
    Any help would be greatly appreciated.

    Thanks, Feastie.
    There's always a way.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Sorting Across Column Labels in a Pivot

    If I select the cell within the body of the PT and then sort it sorts the specific column.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    04-19-2011
    Location
    Leeds
    MS-Off Ver
    Excel 365
    Posts
    48

    Re: Sorting Across Column Labels in a Pivot

    Pivot Prob.xlsxI think I may have sorted it. I currently have, next to item number, a second column that shows the product Caption.
    In options I have switched to Classic View to get this look. (example attached)
    Is there a way to keep multiple rows headers and still have the ability to sort?

    Additional "Can I sort overall rather than just by the second row label".
    Last edited by Feastie; 04-27-2012 at 06:16 AM. Reason: Additional Info

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Sorting Across Column Labels in a Pivot

    The sorting will happen within the inner row item. You can more clearly see this if you reverse the Caption and ItemNumber fields.
    The ItemNumber data will be sorted with Sis and within Dad and within Mum.

    You could add a new field to your data which is a concatenation of ItemNumber and Caption. Use that as the row label and sort by Store.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-19-2011
    Location
    Leeds
    MS-Off Ver
    Excel 365
    Posts
    48

    Re: Sorting Across Column Labels in a Pivot

    In reality there are several row labels and I need to be able to pass this on to other users so, having the complexity of a concatenated field, may give them the jitters.
    If there is no way you can over ride the 'inner rows' I may have to resort to two pivots, one that just has the item number and the other with either a concatenate as you suggest or have the column labels as a report filter thereby allowing them to sort on each column individually.
    Thanks for your help Andy, I now understand how this works a little more clearly.

+ 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