Pivot Table Issue

[Cpreston]

Hi

I have a Pivot table which as a filed for the rows called ProdGroup. It as numbers from 1 to 12 and I have managed to do the totals and subtotals on each group.

However the numbers for the columns are out of number order, so instead of going from 1 to 12 it is putting the 1's first so I have

1
10
11
12
2
3
4
Up to 9

How can I get it so it goes in order from 1 to 12. I have tried grouping but it just wont work, I have tried several sorts but still no luck.

Any advise please.

 

[Andrzejek]

Just a guess here, but it looks to me your 'numbers' are actually text, and that's why the sorting you see.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Hi

Thanks for the reply, I have actually tried changing the formant of the column to number instead of general, even tried text but I am getting the same result each time unfortunately

 

[Andrzejek]

Did you try any of the ways of converting text to numbers presented here?
'changing the formant of the column' doesn't do anything to the value itself :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Hi

The column is set to General and not text as per the video. So the options are not available to convert when I highlight the column in anyway.

Thanks

 

[Cpreston]

Figured it out I believe, The spreadsheet was linked to a SQL View and the field it was using was VarChar. I did a CAST and changed it to INT. The spreadsheet is working as expected now.

Thanks for the replies

 

[Andrzejek]

Great!
I told you Excel was 'seeing' it as text

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson