Cannot sort Excel Pivot Table by two or more columns

Question

I want to sort my Pivot Table by two columns. I've searched the web for some time, and it seems Excel doesn't allow this, so I need a workaround.

My data

Desired Outcome

I want to summarise this by customer, sorted by number of orders, then total value of the orders - so this is a mockup of the table I'm expecting to see:

Actual results using pivot tables

Here's what my pivot table actually looks like. If I sort by count of orders, the value column is in the wrong order. If I sort by value, the count is in the wrong order. There is no concept of sorting equal values by a secondary column for pivot tables.

Question

How do I sort Excel pivot tables by multiple columns?

---

Note: I know you can sort pivot tables by multiple columns if you have two sets of row labels, like I've shown here. But in this case, for various reasons, I only want one set of row labels.

Answer 1

Use a calculated field

The best way I've found to achieve this is to add a calculated field to the table that produces the correct sort order, and then to hide that column.

The sort order field needs to work by scaling each sort column up or down so that no column's bit of the output sort value overlaps with any other.

So I scale OrderCount up by a big number, and total Value down by a big number:

= (OrderCount*1000) + (Value/1000) 

For Jemaine's figures above, this will produce a value of 3000.18. Bret would be 3000.07, Murray/Brian 1000.09 etc - the correct order.

If I wanted to add a third sorting condition I'd have to scale that down by an even bigger number:

= (OrderCount*1000) + (Value/1000) + (3rdCondition/1000000)

Adding the Calculated field

To actually implement this in the pivot table, I need to add a column to my data table so that the calculated field can count orders. I've simply added an OrderCount column and set the value to 1 for every row:

Now we can add the calculated field. Select this option from the ribbon and enter the formula:

My pivot table now looks like this, sorted correctly.

Tidying Up

This SortOrder column might confuse other users, so I can just hide the whole column.

However, I often prefer not to hide columns (personal preference) so to make this column unobtrusive I can rename the column to (ie a blank space), and format the number column for the SortOrder values to a custom format of "";"";"". If Excel autoadjusts column widths this makes the column barely noticeable.