Using Order in Excel Pivot Tables

M

Matt Cromer

Is there an easier way to use the Order function in Pivot Tables. Order,
Move Up/Move Down/Beginning...seems very awkward. The fields that I have
cannot be sorted ascending the traditional way, so I have to order them
manually. Are there any hot keys, or other ways to order??

Thanks!

Matt
 
D

Debra Dalgleish

Select a cell in the row or column area
Point to its border, and drag it to a different position in the list

Or, you could create a custom list, and base the sort on that:

In a cell on a blank worksheet, type your list, then select it
Choose Tools>Options
Select the Custom Lists tab
Click the Import button, click OK

When you create a new pivot table, items should be in the order of the
custom list.

To sort the items in an existing pivot table, select the field
button, and choose Data>Sort
Click the Options button, and choose your custom list.
 
M

Matt Cromer

Awesome, the click, point border, and drag was exactly what I was looking
for!!!

Thanks
Matt
 
D

Debra Dalgleish

You're welcome! Thanks for letting me know.

Matt said:
Awesome, the click, point border, and drag was exactly what I was looking
for!!!

Thanks
Matt

:
Click to expand...
 
R

RMF

I am having a similar issue. changing the order via dragging works, but
perhaps there is a easier way for me to order my pivot. I have a data sheet
with dates that are entered in the following format:

dd/hhmm, e.g. 12/0700

Is there a way of sorting this automatically in a pivot? If I would create a
list I would have to enter from 01/0001 until 31/2359 by entering 01/0001,
01/0002, 01/0003 and so on which is of course time consuming.

Is there a solution for this one?

Thnks!
 
P

Peo Sjoblom

You can create a custom list and sort by that list

To create a custom list, type in the list in a range somewhere in a
worksheet, select it and
do tools>options>custom lists, click import, in the pivot table select the
field button, do
data>sort, select options and your list, click OK

Btw if you type in 12/0700 in a cell it will be seen as text by excel so you
can't do any calculations with these values except counting them

--
Regards,

Peo Sjoblom

Portland, Oregon
 
D

Debra Dalgleish

You'd only be able to put a small portion of the items into a custom
list. If they're text items, they should sort automatically when you add
them to the pivot table. If they're formatted dates, you could add a
column to the source table, and covert them to text, e.g.:

=TEXT(A2,"dd/hhmm")

However, if you use all day/hour/minute combinations, you'd exceed the
number of items allowed for a pivot field. There's information on the
limits here:

Limits of PivotTables in Excel (Excel 97 - 2000)
http://support.microsoft.com/default.aspx?id=211517

Description of the limits of PivotTable reports in Excel (2002-2003)
http://support.microsoft.com/default.aspx?id=820742
 
Top