8

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?

Improve this question

2 Answers 2

Reset to default
7

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.

Improve this answer
2
  • 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
    Commented Jan 31, 2011 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-B. Crow Raven
    Commented Oct 20, 2016 at 15:44
1

Try using Custom Lists.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .