microsoft excelmicrosoft-excel-2007pivot table
I have a worksheet with data that is sorted in certain order (data is coming from external source, so there are four "ORDER BY" columns that create the desired sort order but are hidden on worksheet). However when pivot-table is created, it changes the sort order by sorting on each column selected for the pivot table. The four "Order By" columns are not used in the pivot-table.
Is there a way to preserve the original sort order in the Pivot-Table?
@Soandos, I think you can add three new columns (with 20th, 50th and 70th percentile as headings) to your raw data to capture the 20th, 50th and 70th percentile calculation. Once this is achieved, manually refresh your pivot table to capture these new fields in your Pivot Table Field List.
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.
Best Answer
Assign serial numbers to the data in another hidden column and use that to sort the pivot table data. If needed the serial numbers can be included in the pivot table and then hidden.
Serial numbers can be inserted either using a formula that adds one to the cell above or by typing 1 in the first cell and 2 below it and selecting both cells and then using the lower right hand selection point to drag the range down.