• 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


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

Sorting pivot tables on a split column


New Member

The data for a pivot table is:

Product Category Value

A Cat1 17

A Cat2 13

B Cat1 60

B Cat2 40

C Cat1 85

C Cat2 25

If I now pivot the data so that the Product is on the left hand side (rows), Category forms the column headings and Value is the data - like

Sum of Value Category

Product Cat1 Cat2 Grand Total

A 17 13 30

B 60 40 100

C 85 25 110

Grand Total 162 78 240

I want to swap these values to %ages of rows (easily done) but then sort the table by the percentage value of the Cat1 field into descending order. i.e.

Sum of Value Category

Product Cat1 Cat2 Grand Total

C 77.27% 22.73% 100.00%

B 60.00% 40.00% 100.00%

A 56.67% 43.33% 100.00%

Grand Total 67.50% 32.50% 100.00%

My attempts so far amount to flattening the pivot table to text and then use it as the data source for a new pivot table which then allows me to insert a calculated field for the percentage of the Cat1 values and then sort on this field.

I'm sure that there must be a way of doing this, without all my other extra work.

Thanks in advance, Mike