Pivot table sorting incorrectly

Question

Hi,

I have a small table :

Category ; =F5&"<=Sales<"&G5

Which is then loaded into power pivot data mode and subsequently a pivot table

but the pivot does not sort correctly , it seems to be sorting on the first digit, 0,1,1,2,5.

so I added "" to the start so ; =""&F5&"<=Sales<"&G5 and it sorted correctly , can someone explain why this works as it was just a guess.

Richard.

Answer 1

Hi,

In the PowerPivot window, click on any cell in the Category column and click on Sort By > Lower Limit > OK. Now click on Close and Load. See the Pivot Table.

Answer 2

But if you use Type , the data type is text (2), prior to adding the "" , so already text?

so how is it different after adding .

Richard.

Answer 3

It sets it as text.

Answer 4

so why does adding "" to the start stop it , as everything remains in the same relational position and how else can I prevent it?

Richard.

Answer 5

Hi,

Thank you for reaching out to us today. I’m Kevin and I’m happy to to help you out with your question today.

It’s sorting on the first digit because the cell contents are text, so it’s doing a standard alpha numerical sort in the first characters.

I hope this information is helpful. If you have any further questions or need additional assistance, please reach back out and I will be more than happy to assist you.

Best Regards,
Kevin

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.