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?


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.

  • this solution works well. Numbers have to be inclided in the pivot table and then hidden. Numbers also have to be the same per for each sort group, to avoid splitting groups into subgroups. – Meringros Jan 31 '11 at 15:09
  • I already have a value I want to sort by, no need to add another data point. How can I sort by that programmatically (C#/Excel Interop)? – B. Clay Shannon Oct 20 '16 at 15:44

Try using Custom Lists.

  • Ribbon Button
  • Popular
  • Edit Custom Lists button
  • Type comma-separated values, or import from the cells containing your desired order.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.