Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using Order in Excel Pivot Tables
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 |
#2
|
|||
|
|||
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 ToolsOptions 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 DataSort Click the Options button, and choose your custom list. Matt Cromer wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
Awesome, the click, point border, and drag was exactly what I was looking
for!!! Thanks Matt "Debra Dalgleish" wrote: 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 ToolsOptions 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 DataSort Click the Options button, and choose your custom list. Matt Cromer wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
You're welcome! Thanks for letting me know.
Matt Cromer wrote: Awesome, the click, point border, and drag was exactly what I was looking for!!! Thanks Matt "Debra Dalgleish" wrote: 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 ToolsOptions 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 DataSort Click the Options button, and choose your custom list. Matt Cromer wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Order in Excel Pivot Tables
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! "Matt Cromer" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Order in Excel Pivot Tables
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 toolsoptionscustom lists, click import, in the pivot table select the field button, do datasort, 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 "RMF" wrote in message ... 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! "Matt Cromer" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Order in Excel Pivot Tables
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 he 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 RMF wrote: 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! "Matt Cromer" wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables & not printing blank rows (revisited) | Excel Discussion (Misc queries) | |||
How do I set the order of calculation of Data Tables in Excel? | Excel Worksheet Functions | |||
Can you reformat order of items in Pivot Tables after refresh? | Excel Discussion (Misc queries) | |||
Pivot Tables, can I use an external list to select data items? | Excel Worksheet Functions | |||
convert excel list to pivot table | Excel Discussion (Misc queries) |