Pivot Table question - again - signed it this time

Paul-Johnson · Mar 6th, 2002, 09:58 AM

Is there us a way to sort the columns in a pivot table ?

Below is a simple example (my real data is much more complex)

Cat <100
Mouse <100
Dog 100-500
Rabbit 501-1000
Hamster 501-1000
Elephant > 1000
Giraffe > 1000

When I create the pivot table

The rows are the animals listed, the columns are the values listed, and the data is the count of each value.

Unfortunately, the columns come out in the following order:
> 1000
100-500
501-1000
< 100

Is there a way to change the order in which they appear, other than changing the source data ? In reality there are 30,000 + items.

Your help is appreciated.

PJ

Mark W. · Mar 6th, 2002, 10:15 AM

Yes, just drag the COLUMN field items and place them in the desired order.

Paul-Johnson · Mar 6th, 2002, 10:27 AM

That does not work, as they are all in the under the same column field

Mark W. · Mar 6th, 2002, 10:31 AM

Click on each Item in your PivotTable, grab hold of its column boundary, and drag it to the desired position.

Mar 6th, 2002, 11:08 AM

Maybe I am being dumb. But, when i try select the column of data and try to move it via the handlebars, i get an error message saying that operation cannot be performed.

Mark W. · Mar 6th, 2002, 11:28 AM

Ahh, now I understand your problem. There shouldn't be a fill handle after you make the proper selection. Evidently, the Enable Selection mode has been disabled. Right click on your PivotTable and choose the Select | Enable Selection popup menu command. The icon should appear depressed (but, not in need of Prozac

, Sorry...). Once done you can click on a column field item and the entire column (label and data values) will be selected. Drag these cells by their border to the desired position.

BTW, there is another approach... creating a custom sort order list... but, this list would only reside on your PC and not "travel" with the workbook which could lead to a configuration issue later. That's why I didn't mention it previously.

[ This Message was edited by: Mark W. on 2002-03-06 10:36 ]

Paul-Johnson · Mar 6th, 2002, 11:33 AM

Got it --- My enable button was depressed

My problem was i was grabbing the handlebar instead of the boundary (if that makes sense).

Thanks Again !!!!

I am interested in the custom sort - I will research it. Thanks Again !
_________________
Paul

[ This Message was edited by: Paul-Johnson on 2002-03-06 10:36 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 09:58 AM	#1
Paul-Johnson New Member Join Date: Feb 2002 Posts: 47	Is there us a way to sort the columns in a pivot table ? Below is a simple example (my real data is much more complex) Cat <100 Mouse <100 Dog 100-500 Rabbit 501-1000 Hamster 501-1000 Elephant > 1000 Giraffe > 1000 When I create the pivot table The rows are the animals listed, the columns are the values listed, and the data is the count of each value. Unfortunately, the columns come out in the following order: > 1000 100-500 501-1000 < 100 Is there a way to change the order in which they appear, other than changing the source data ? In reality there are 30,000 + items. Your help is appreciated. PJ

Mar 6th, 2002, 10:15 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Yes, just drag the COLUMN field items and place them in the desired order.

Mar 6th, 2002, 10:27 AM	#3
Paul-Johnson New Member Join Date: Feb 2002 Posts: 47	That does not work, as they are all in the under the same column field

Mar 6th, 2002, 10:31 AM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Click on each Item in your PivotTable, grab hold of its column boundary, and drag it to the desired position.

Mar 6th, 2002, 11:08 AM	#5
Guest Posts: n/a	Maybe I am being dumb. But, when i try select the column of data and try to move it via the handlebars, i get an error message saying that operation cannot be performed.

Mar 6th, 2002, 11:28 AM	#6
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Ahh, now I understand your problem. There shouldn't be a fill handle after you make the proper selection. Evidently, the Enable Selection mode has been disabled. Right click on your PivotTable and choose the Select \| Enable Selection popup menu command. The icon should appear depressed (but, not in need of Prozac , Sorry...). Once done you can click on a column field item and the entire column (label and data values) will be selected. Drag these cells by their border to the desired position. BTW, there is another approach... creating a custom sort order list... but, this list would only reside on your PC and not "travel" with the workbook which could lead to a configuration issue later. That's why I didn't mention it previously. [ This Message was edited by: Mark W. on 2002-03-06 10:36 ]

Mar 6th, 2002, 11:33 AM	#7
Paul-Johnson New Member Join Date: Feb 2002 Posts: 47	Got it --- My enable button was depressed My problem was i was grabbing the handlebar instead of the boundary (if that makes sense). Thanks Again !!!! I am interested in the custom sort - I will research it. Thanks Again ! _________________ Paul [ This Message was edited by: Paul-Johnson on 2002-03-06 10:36 ]