Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 1 down vote accepted

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.

share|improve this answer
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

Try using Custom Lists.

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

What if you refresh the data? I notice that even when I order-lock data in the source sheet, a refresh moves the edited data to the bottom of the PivotTable (regardless of where it was before).

share|improve this answer

You must log in to answer this question.

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