How to sort pivot tables by multiple columns?

Question

is it possible to sort a pivot table by multiple columns - conceptually a t-sql order by column1, column2?

I have a pivot table with 4 columns. the first column is the row label. columns 2,3,4 are column label columns. I would like the primary sort to be on column 4, then sorted by column 3 such that column 3 sorting preserves column 4 order.

as a very contrived example, consider the following where car is the row label, and male population, female population are column labels, and total is a values element.

i can easily sort by any of the columns, but cannot figure out how to include a 2d column in a sort and retain the total column's order. in this example, bmw comes before Porsche because female population of 20 is greater than female population of 10.

car	male population	female population	total
Mercedes	60	40	100
bmw	70	20	90
porsche	80	10	90

I don't want to program anything. the table is a PIVOT TABLE - not a range of normal excel cells.

Answer 1

Hi,

before:

after (sort by ascending order)

first by column D, next by column C

Answer 2

As per this screen shot, I am able to get sorting as desired by you.

Answer 3

thank you for looking into this. I believe that I did what you did and still have an improper sort. perhaps my example was too simple. I tried to keep the discussion simple by using simple data.

here is the data with which i am struggling. you will note that in the Top 2 Box % column, the sort order fails. see how .85741.... comes after .928571....? that is incorrect. the major column sort order has been corrupted by the second.

is it possible to sort first by Top 2 Box % and then by % Excellent such that the first sorting column is sorted correctly?

Row Labels	% Good	% Excellent	Top 2 Box %
Music	0.071428571	0.071428571	0.142857143
Scent	0.214285714	0.714285714	0.928571429
Layout	0.142857143	0.785714286	0.928571429
Amenities (towels, water, ear buds)	0.142857143	0.785714286	0.928571429
Size		0.857142857	0.857142857
Lighting	0.071428571	0.928571429	1
Flooring	0.071428571	0.928571429	1
Cleanliness		1	1
Top 2 Box %	0.089285714	0.758928571	0.848214286

Answer 4

Hello Tony,

Welcome to Microsoft Community and thank you for posting your query.

Since you need help with Pivot table in Excel, for better suggestion you may also post your query in Excel IT pro using the forum link below.

http://social.technet.microsoft.com/Forums/en/excel/threads

Thank you.

Answer 5

Hi,

Upload the workbook to OneDrive and share the link of the workbook here.