Sort two pivot table columns?

[Liverlee]

Hi, can anyone let me know if it's possible to sort two columns in a pivot table? For the first column in the pivot table I created a custom list and sorted the data that way, then in column c I tried to sort the data numerically from smallest to largest but the drop down option was to sort a-z, so i created a helper column to rank column c numbers but even though i have the option to sort smallest to largest when i click on the drop down arrow and refresh the data it doesn't sort in rank order.

Any suggestions?

 

[Alex Blakenburg]

Your pivot table doesn't look right.
Typically a pivot table has all the columns on the right being Numeric. You have Status at the end.
This would indicate you have your 2 value columns in the Row and not the Value area of the pivot table.

If you had the 2 numeric columns in the Value area then when you use More Sort Options on your Task column, you would get the option to sort on New Id or Rank.

Also the Pivot Sorts from left to right. So if you sort Column C it will sort within the Column B groupings. Since most of your Column B items appear to be unique, the sort will appear to have no effect.

 

[BSALV]

??? In a pivottable, you can sort on 1 key, combined keys aren't possible, is it ?

 

[Alex Blakenburg]

??? In a pivottable, you can sort on 1 key, combined keys aren't possible, is it ?

I am not sure am not misinterpreting your comment but ...

In a pivot table you can sort the rows from left to right but each sort is sub-sort of the previous grouping.
The sorts can be on the field itself or under more sort options on the value fields.
Once you get to a column that is unique eg each group is actually a single row, then further sorts don't do anything.

 

[BSALV]

I just tried something out and wasn't able to do it correct.
I don't understand if i sort on column A ascending, that it doesn't give ABCDEF in that order.
So that was my first step, it's already wrong, but as i understand you, you can subsequently sort the 2nd column for example descending ?
blakenburg

blakenburg.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	col A	b	c	d	e				Som van e
2	C	F	F	D	0,471604				col A	b	c	d	Totaal
3	B	E	D	A	0,87127				C	C	C	B	0,340379427
4	E	E	E	D	0,818198				C	E	A	C	0,755281994
5	A	F	B	F	0,943924				C	A	E	A	0,006429316
6	B	A	B	B	0,580679				C	F	F	D	0,471604224
7	F	B	C	D	0,678798				E	C	B	F	0,749979879
8	B	C	F	B	0,175333				E	E	E	D	0,818198435
9	F	B	C	F	0,131334				E	E	B	C	0,358540861
10	C	E	A	C	0,755282				E	E	D	A	0,127470025
11	F	E	D	C	0,573219				E	B	A	E	0,096941924
12	E	E	B	C	0,358541				A	E	D	D	0,874457175
13	A	E	F	A	0,721581				A	E	F	A	0,721581368
14	C	C	C	B	0,340379				A	D	E	F	0,565548268
15	E	E	D	A	0,12747				A	F	B	F	0,943923983
16	A	D	E	F	0,565548				B	C	F	B	0,175332747
17	D	F	B	A	0,401945				B	E	D	A	0,871270321
18	E	B	A	E	0,096942				B	A	B	B	0,580679239
19	A	E	D	D	0,874457				B	D	A	C	0,035165184
20	E	C	B	F	0,74998				B	F	C	A	0,547640265
21	F	F	E	A	0,973177				D	C	D	F	0,212320567
22	D	C	D	F	0,212321				D	F	B	A	0,401944826
23	C	A	E	A	0,006429				F	E	D	C	0,573218542
24	F	F	E	E	0,379244				F	B	C	D	0,678798366
25	B	D	A	C	0,035165				F	B	C	F	0,131333541
26	B	F	C	A	0,54764				F	B	F	D	0,2264505
27	F	B	F	D	0,22645				F	F	E	E	0,379244379
28									F	F	E	A	0,973177088
29									Eindtotaal				12,61691244
30
Blad1

 

[Alex Blakenburg]

Sorting the source data doesn't do anything (ie sorting Column A).
When you first create the pivot table it will sort ascending and give the impression the sorting is set ascending.
When you add data to the pivot table you will find things going out of order because under the hood it is actually set to "Manual" sort.
You actually need to go into the pivot table column and explicitly tell it which type of sort you want.

What it is doing is probably more obvious when you have grouping values set to not repeat (blank rows if there is no change).
Once you get to a column that has a value on every line, any further sorts don't do anything.

Book2
	P	Q	R	S	T
1	col A	b	c	d	Sum of e
2	F	B	C	D	0.678798366
3				F	0.131333541
4			F	D	0.2264505
5		E	D	C	0.573218542
6		F	E	A	0.973177088
7				E	0.379244379
8	E	B	A	E	0.096941924
9		C	B	F	0.749979879
10		E	B	C	0.358540861
11			D	A	0.127470025
12			E	D	0.818198435
13	D	C	D	F	0.212320567
14		F	B	A	0.401944826
15	C	A	E	A	0.006429316
16		C	C	B	0.340379427
17		E	A	C	0.755281994
18		F	F	D	0.471604224
19	B	A	B	B	0.580679239
20		C	F	B	0.175332747
21		D	A	C	0.035165184
22		E	D	A	0.871270321
23		F	C	A	0.547640265
24	A	D	E	F	0.565548268
25		E	D	D	0.874457175
26			F	A	0.721581368
27		F	B	F	0.943923983
28	Grand Total				12.61691244
Sheet1

Showing sorting arrows;