How to remove automatic sorting from the pivot table in MS Excel(via VBA and Excel)

Question

How can we remove the automatic sorting from the pivot table ?
I have some raw data and when I convert it into pivot table, it automatically changes the order of some fields. How to prevent this ?

PFA

As you can see it changed the order of time[row field] automatically. I want the order to be maintained as it was.
How can we achieve this ?

We know it can be achieved by inserting new helpers column but is there any alternative ?

We want to modify only the raw source data without affecting other data. Since this method add and delete columns, it will definitely result in data loss, which we want to avoid.

![](https://learn-attachment.microsoft.com/api/attachments/80744535-8269-4e49-b1f0-b6b9bf60ff17?platform=QnA

Answer 1

I wonder what the data source looks like.

Why a sample file is important for troubleshooting - - - - - - - and how to do it.

Answer 2

Hi Jim,

The image contains 2 tables.
First one is the source data
Second one is the pivot table created out of it.

If you see the second table, the order of the "time" field values have been changed automatically.

We want to avoid this without adding/removing columns in the source data.

Answer 3

If you provide a sample then I get to see how your pivottable is set up and I don't have to type a lot of data and guess what steps you took to make your pivottable.

Answer 4

name	location	time	data
abc	worldWide	week -59	100
abc	worldWide	week -58	200
abc	worldWide	week -57	300
abc	worldWide	week -56	400
xyz	worldWide	week -59	600
xyz	worldWide	week -58	800
xyz	worldWide	week -57	120

So, here's the source data.
We are creating a pivot table out of it in the same order as mentioned but the field values of time field are sorting. we want to avoid this.

Answer 5

There are two sorting options. Right-click in the PivotTable and choose PivotTable Options > Layout > Sort