• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to sort a pivot, but not by the first column

G

gtothek

Member
I'm trying to sort a pivot table, but I can only sort it by the first column. I'm trying to sort it by the third column in the pivot. Am I doing something wrong? I'd like to sort by Age in descending order, so the oldest is at the top. When I sort it, nothing happens. I want to keep the age in that column. THanks for you help!

upload_2014-10-3_10-21-37.png
 
How exactly are you setting the sort? The default sort button may be picking a value field or the first column. You should be able to open the filter, go down to label sort, and sort descending on Age group.

If still running into issues, could you upload a sample, showing this part of your PT so we can try to diagnose?
 
This is a little counter-intuitive, but I believe what you need to do is sort the first column, but by the values in another column.

So in your Pivot Field List, if you click the little options arrow next to whichever field you have in the first column, you should see 'Sort A to Z', 'Sort Z to A', and 'More Sort Options.' If you click on 'More Sort Options', there should be an option to sort the first column by one of your values columns. So if you select Sort by Age, it should do what you're wanting.

Let me know if that works, or if you need additional clarification. I can post screenshots if necessary.
 
Schmolio, can you post screenshots? I tried clicking the option arrow in both the field list and in the pivot, but it doesn't give me additional sort options under "more sort options".
 
Ah, I see where schmolio007 is going! In the PT, it's trying to sort the Ages within the next group up, the Claim Number. In your example, there's a 1-to-1 correlation, so you can't see this happening. If you move the Age field to the left/before Claim Number, you should see the correct sort.
 
Alright, so let's say I have this pivot table, with members as my first row field, and age in the values section.

screen1.png

In the Pivot Fields on the right hand side of the screen, click on the arrow next to the field that you have in the rows section (you want to click on the arrow in the list of fields at the top, not in the row or values section at the bottom).

screen2.png

If you click on the arrow, you should get the dialogue I was talking about, with Sort A to Z, etc. Click on 'More Sort Options.'

That should bring up this form, which you can use to sort your first column by one of the fields you have in your values section. So you are sorting your first column by one of your values columns. Of course, it will vary slightly depending on your pivot table setup, but this should give you the result you're looking for.

screen4.png
 
Ok I see now, I have to put the age filed into "Values". Is there a way to do mutiple sorts in a pivot? Would all fields have to be in values column?
 
For some reason, the head in the values field stays as "total" even when i change the field settings custom name...any clue why?
 
I believe you can do multiple sorts, and I don't think all fields necessarily have to be in the values column. It really just depends on your pivot table setup. So I couldn't really give you a definitive answer on that one.

As for the values field not setting the custom name, correctly, I'm not sure. Is there possibly another field or column that already has the name you're trying to set? If not that, I'm not sure why it wouldn't be working.
 
You must log in or register to reply here.
Back
Top