MrExcel Publishing
Your One Stop for Excel Tips & Solutions

pivot table drop down bars are only partially alphabetically sorted?


Posted by Gavin on January 17, 2001 6:46 AM

Hi

A strange one this?

I have a file with 1955 lines, each one order entry information.

I've created a variety of pivot tables fromthis source data.

The strange part is that the drop down bars are 90% sorted alphabetically. I've had a good look buti cant see any reason for this.

Does anyone have any ideas?

Thanks

Gavin


Posted by Mark W. on January 17, 2001 6:52 AM

Do you have mixed data types in that field?

Posted by Gavin on January 17, 2001 7:21 AM

I dont think so, the whole column is just surnames in general format?

Posted by Mark W. on January 17, 2001 7:41 AM

So what do you mean by 90% sorted. Provide an example.

Posted by Bruce on January 17, 2001 8:44 AM

Gavin,

I frequently add additional data to my Pivot tables. When I do, it does not automatically sort the data correctly. I have to go into "field", "advanced", and then select the sort order to fix this. My default sort order is "manual".


Posted by Loren on January 17, 2001 9:09 AM

pivot table sort is SO easy

Click in the column to be sorted and click the sort icon on your toolbar.

Posted by Dave Hawley on January 17, 2001 9:01 PM

Re: pivot table sort is SO easy


Hi

You mean most are sorted by about 10% of them are appearing at the end of your list?

1. By drop down arrows, do you mean page fields ? If so double click the Page Field heading and go to Advanced then click "Ascending" or "Descending" and then select your Page field heading from the "Using Field box".


2. If still no good then you will no doubt have some spaces in your data list you don't know about. go to you data source list and in a spare column put =TRIM(A2) and copy this down, then pastespecial> Values over the top of itself to remove formulas and then paste the new names in A2. That's if column A are you names. Repeat step 1.


Hope this helps
Dave


OzGrid Business Applications