+ Reply to Thread
Results 1 to 23 of 23

Question: How to sort values in a Tabular Pivot table?

  1. #1
    Registered User
    Join Date
    03-31-2017
    Location
    Indiana
    MS-Off Ver
    2013
    Posts
    29

    Question Question: How to sort values in a Tabular Pivot table?

    [Solved] - See post #16

    Solution: To sort by value in a tabular pivot table, highlight the first "ROW" field that varies in the group you want to sort by in value and right click to sort by more options. Sort by the sum of the Value field that was originally intended to be sorted ( ASC / DESC ). Tabular pivots with a static leading "ROW" field can be sorted directly by right clicking the corresponding values you want to sort by if there is only 1 additional "ROW" field that varies. Upon the 3rd "ROW" field of varied values the solution in #16 has to be applied


    See basic example below,

    I made the pivot table in Tabular Form with Repeat All Item Labels
    - I removed the subtotals
    - How do I sort the sum of VALUE 1?

    ( Images deleted )

    Workbook attached
    Attached Files Attached Files
    Last edited by #DIV/0!; 08-16-2017 at 10:44 AM.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Question: How to sort values in a Tabular Pivot table?

    PT is sorted by the first column. You can try to switch to manual sort then drag rows as you want

    How to sort PT
    Last edited by sandy666; 08-15-2017 at 01:11 PM.

  3. #3
    Registered User
    Join Date
    03-31-2017
    Location
    Indiana
    MS-Off Ver
    2013
    Posts
    29

    Re: Question: How to sort values in a Tabular Pivot table?

    Is there a way to do it automatically though?
    Edit: It won't let me manually drag and gives me the error prompt: " We can't make this change for the selected cell because it will affect a PivotTable. Use the field list to change the report. If you are trying to insert or delete cells, move the PivotTable and try again.
    - I tried moving the pivot table and I still get the error



    I tried adding another column on the left just valued at "=1" ( see below ) to try and sort but it still won't work ( Red highlight )
    - I went to each of the "ROW" metrics and set them to manual as well ( Orange highlight )
    Attached Files Attached Files
    Last edited by #DIV/0!; 08-15-2017 at 01:36 PM.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Question: How to sort values in a Tabular Pivot table?

    Attach a sample workbook (not a picture!). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

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

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Question: How to sort values in a Tabular Pivot table?

    btw. did you read information from the link?

  6. #6
    Registered User
    Join Date
    03-31-2017
    Location
    Indiana
    MS-Off Ver
    2013
    Posts
    29

    Re: Question: How to sort values in a Tabular Pivot table?

    I have read the link - it appears to show how to sort a traditional pivot table but not one in tabular form

    I attached the workbooks and removed the images - there is a before and after tab but to get the results I had to copy pivot table as values
    Last edited by #DIV/0!; 08-15-2017 at 01:30 PM.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Question: How to sort values in a Tabular Pivot table?

    see attached file
    Attached Files Attached Files

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Question: How to sort values in a Tabular Pivot table?

    or here (manual sorted)
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-31-2017
    Location
    Indiana
    MS-Off Ver
    2013
    Posts
    29

    Re: Question: How to sort values in a Tabular Pivot table?

    Quote Originally Posted by sandy666 View Post
    see attached file
    Is there no way to do it without moving VALUE1 into "Rows"

    I desensitized a workbook and attached it,
    - The workbook attached here I am able to sort values in column "F" ( SPH ) high to low or low to high even though the "ROW" value in column C is not sorted. Column B ( Tenure REF ) is exclusive to "Inactive" in this view

    Why is it in this workbook I can sort values in the "VALUE" pivot field? ( I don't know how I unlocked the ability to do so in this pivot even though it is a tabular rows repeating )
    Attached Files Attached Files

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Question: How to sort values in a Tabular Pivot table?

    see second attached file

  11. #11
    Registered User
    Join Date
    03-31-2017
    Location
    Indiana
    MS-Off Ver
    2013
    Posts
    29

    Re: Question: How to sort values in a Tabular Pivot table?

    I played around with the second workbook - it seems to automatically sort based on "ROW 2" value?

    I was trying to make an example workbook but it's just a coincidence that Row 2 and Value 1 are both ascending ( Alphabetical and numerical )

    Is there a way to have Value 1 truly sorted?
    Last edited by #DIV/0!; 08-15-2017 at 02:10 PM.

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Question: How to sort values in a Tabular Pivot table?

    you can sort by SPH but note SPH moves to the end or to the beginning, depend of Smalles to Largest or Largest to Smallest but it doesn't sort SPH
    and SPH is Calculated Field so you can't move (copy) it to the first column (position) of ROWS

    maybe it will be possible with VBA but I'm far away from VBA
    Last edited by sandy666; 08-15-2017 at 02:23 PM.

  13. #13
    Registered User
    Join Date
    03-31-2017
    Location
    Indiana
    MS-Off Ver
    2013
    Posts
    29

    Re: Question: How to sort values in a Tabular Pivot table?

    Quote Originally Posted by sandy666 View Post
    you can sort by SPH but note SPH moves to the end or to the beginning, depend of Smalles to Largest or Largest to Smallest but it doesn't sort SPH
    and SPH is Calculated Field so you can't move it to the first column (position) of ROWS
    In the workbook when I right click a value in SPH ( Like cell F8 ) I can click Sort -> Largest to Smallest and all the rows adjust
    - I can also click a value in column G ( Like cell G8 ) sort like above and I can see that it truly is sorting the view by SPD

    The problem is I don't know why it works here but not in the example workbook ( Or any other workbook I make in Tabular )
    - I've tried turning all the values in the example workbook into calculated fields and it didn't work
    - I've tried adding slicers to the example workbook to see if that did anything, it didn't work

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Question: How to sort values in a Tabular Pivot table?

    Right, you can sort by Values in SPH

    In Desen001 i can sort by SPH S2L or L2S and it is in tab form
    so ?

    i think i didn't catch your idea

  15. #15
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Question: How to sort values in a Tabular Pivot table?

    Quote Originally Posted by #DIV/0! View Post
    The problem is I don't know why it works here but not in the example workbook ( Or any other workbook I make in Tabular )
    is there calculated fields or you tryin' to sort data from source?

  16. #16
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Question: How to sort values in a Tabular Pivot table?

    Hi,

    In your example workbook, sort the ROW1 field descending by Value1. To do so, right click a ROW1 field, Sort, More sort options, then select descending and Value1.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  17. #17
    Registered User
    Join Date
    03-31-2017
    Location
    Indiana
    MS-Off Ver
    2013
    Posts
    29

    Re: Question: How to sort values in a Tabular Pivot table?

    Sorry, I had to get back to a work task that took me all night - apologies for dropping off mid conversation.


    Quote Originally Posted by sandy666 View Post
    Right, you can sort by Values in SPH

    In Desen001 i can sort by SPH S2L or L2S and it is in tab form
    so ?

    i think i didn't catch your idea
    Desen001 workbook is perfect, it reacts how I believe it should. It's a tabular pivot table that lets me sort by values and it isn't giving me any troubles.
    - The problem is I don't know why it works here but no were else. If I make a new workbook ( The example book1 ) or try to make tabular pivots in my other data sources I can't get any values ( Calculated or source ) to sort



    Quote Originally Posted by xlnitwit View Post
    Hi,

    In your example workbook, sort the ROW1 field descending by Value1. To do so, right click a ROW1 field, Sort, More sort options, then select descending and Value1.
    Thank you - I believe this works!

  18. #18
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Question: How to sort values in a Tabular Pivot table?

    post above was edited so answer here doesn't make sense
    Last edited by sandy666; 08-16-2017 at 10:32 AM.

  19. #19
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Question: How to sort values in a Tabular Pivot table?

    Quote Originally Posted by #DIV/0! View Post
    Thank you - I believe this works!
    FYI, sorting the first row field will not work since it only sorts the items within that field. As there is only one item in your example, no sorting occurs. You must sort the next field in order to make the sort actually change anything. Note also that this only works because you have the same item in field 1 for all the rows. It is not possible to sort an entire table by a value field if it would break the row field groupings.

  20. #20
    Registered User
    Join Date
    03-31-2017
    Location
    Indiana
    MS-Off Ver
    2013
    Posts
    29

    Re: Question: How to sort values in a Tabular Pivot table?

    Quote Originally Posted by sandy666 View Post
    even from the same source?
    I think I figured it out - you can right click a tabular pivot table and manually sort by fields if the leading "ROW" value is static and maybe.. only 2?
    - In the "Descen001" workbook I had 2 row values, the first one was static "Inactive" the second one was a person's name. Since the first of the 2 fields was static it let me sort.
    - In the "Book1"" example I had 3 row values, the first one was static "1" but the second and third ones were not. I think there might be some issue with how the pivot trees off because I see the little "Expand" buttons show up ( Grey [-] boxes ) on the 2nd column but not the third.

    Long story short, under some conditions if the "ROW" values are limited I can sort in tabular by just right clicking.
    Otherwise I have to go to the first varying "ROW" column where the corresponding "Values" need to be sorted and do the More sort option that xlnitwit suggested

  21. #21
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Question: How to sort values in a Tabular Pivot table?

    you can expand rows and columns but not values (+/-)

  22. #22
    Registered User
    Join Date
    03-31-2017
    Location
    Indiana
    MS-Off Ver
    2013
    Posts
    29

    Re: Question: How to sort values in a Tabular Pivot table?

    Quote Originally Posted by xlnitwit View Post
    FYI, sorting the first row field will not work since it only sorts the items within that field. As there is only one item in your example, no sorting occurs. You must sort the next field in order to make the sort actually change anything. Note also that this only works because you have the same item in field 1 for all the rows. It is not possible to sort an entire table by a value field if it would break the row field groupings.
    Right - it is clearer now thank you

    The reason the "Descen001" workbook works initially ( and I can just right click and sort directly on the values ) is because the leading field is static ( It always = "Inactive" ) and since it only has 1 more "ROW" field value next to it then it works

    In the example workbook ( Book1 ), the first field was static but it didn't have just 1 more "ROW" field, it had 2. Because of this I had to right click on the first "ROW" field with varying values and link to the value I wanted to sort ( your direction ) to get it to work.

  23. #23
    Registered User
    Join Date
    08-02-2019
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1

    Lightbulb Re: Question: How to sort values in a Tabular Pivot table?

    The way I solved this issue was creating a Macro the re-builds the Pivot table every time. You can easily sort the data with two columns.

    In my case, one represented a project ID and the second represented last activity recorded for that project, but once all the other fields were inserted into the pivot table I would lose my ability to sort this information.

    My solution was to record a Macro with the following steps.

    Take all the ROWS fields except my project ID and inserted first the columns that I wanted to drive the sorting, in my case, last activity recorded for the project.
    After that I sorted the data from oldest to youngest activity recorded.
    Then, I re-inserted all the other ROWS fields into the pivot table and my sorting remained unchanged.
    I repeated the process to other fields that users might want to sort the data by and created a button to each of these sorting option.

    Hope it helps,

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Pivot Table Issue: Tabular ROWS values appearing blank now
    By #DIV/0! in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-14-2017, 12:15 PM
  2. vba to show pivot table in tabular form
    By yossip in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-22-2017, 05:34 AM
  3. Trying to Get Pivot Table To Be Columnar/Tabular Just like a Table
    By larrytxeast in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-06-2016, 08:40 AM
  4. [SOLVED] Using a pivot table to sort on values only
    By julesmctavish in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-20-2016, 02:02 AM
  5. Need Macro to Set Up Pivot Table in Tabular Format
    By bmahfood in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-04-2016, 07:09 PM
  6. Pivot Table Tabular Form Formatting Help
    By JohnHuey in forum Excel General
    Replies: 1
    Last Post: 12-12-2012, 04:58 AM
  7. Replies: 6
    Last Post: 05-30-2012, 09:54 AM

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