How to stop auto-sort when making pivot tables

[alouie457]

Hi,

When making a pivot table, Excel seems to automatically auto-sort my data. How do I stop this from happening?

For example,
I have data in quarters as in 1q2009,2q2009,3q2009,4q2009,1q2010,2q2010,3q2010,4q2010.

But when I input the data in pivot tables, it comes out in 1q2009,1q2010,2q2009,2q2010. etc

Thank you for help.

[jeffreybrown]

Excel does not see 1q2009 as a number to sort other than the 1, 2, 3, etc., on the front.

Do you actually have dates in your data because a pivot table can group on quarters?

[alouie457]

Thanks for the reply.

No, the data does not have dates.

[martindwilson]

try renaming them to
2009q1 2009q2 2009q3 2009q4 2010q1 2010q2 2010q3 2010q4

[jeffreybrown]

Well looks like Martin beat me to it, but since it took me awhile to come up with it I will still post.

With 1q2009 in A1, in B1 try...

=TEXT(LOOKUP(LEFT(A1,1),{"1","2","3","4"},{"1","4","7","10"})&"/"&SUBSTITUTE(MID(A1,2,1),"q",1)&"/"&MID(A1,3,4),"d/m/yyyy")+0

Then format as date. This will put all the text you had into 1/1/20009, 1/4/2009, 1/7/2009, and 1/10/2009 which is the first day of the quarter.

[alouie457]

Thanks for the help. I appreciate it.