Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need help sorting pivot table
I'm not very experienced with Excel, in particular with Pivot tables, and
would appreciate some help. I have an inherited spreadsheet with a number of pivot tables in it. The source data for the spreadsheet gets "refilled" from an Access database, but the rows in the pivot table don't generally get sorted correctly. The data is in the form of "month year" - e.g. Jan 05, Feb 05, Mar 05, etc. I tracked down the source of the data in the spreadsheet, it comes from a column on another sheet (column V), labeled Month. It's formula seems to be: =INDEX(Months,MONTH(F1),1)&" " &RIGHT(YEAR(F1),2) The pivot table frequently appears: Jul 05 Jan 05 Feb 05 Mar 05 Apr 05 May 05 Jun 05 Aug 05 Sep 05 Dec 05 So, it's not in date order, nor alpha order. The data does appear in the source worksheet in date order. What would be a practical means of correcting this so that the pivot table comes up in proper date order? Or, what additional information should I post so that you can help me with this? Thank you in advance for any suggestions. -- Randy Harris (tech at promail dot com) |
#2
|
|||
|
|||
Instead of using the Month field in the pivot table, use the Date field
in the pivot table. Then, group the date by year and month. There are instructions he http://www.contextures.com/xlPivot07.html Randy Harris wrote: I'm not very experienced with Excel, in particular with Pivot tables, and would appreciate some help. I have an inherited spreadsheet with a number of pivot tables in it. The source data for the spreadsheet gets "refilled" from an Access database, but the rows in the pivot table don't generally get sorted correctly. The data is in the form of "month year" - e.g. Jan 05, Feb 05, Mar 05, etc. I tracked down the source of the data in the spreadsheet, it comes from a column on another sheet (column V), labeled Month. It's formula seems to be: =INDEX(Months,MONTH(F1),1)&" " &RIGHT(YEAR(F1),2) The pivot table frequently appears: Jul 05 Jan 05 Feb 05 Mar 05 Apr 05 May 05 Jun 05 Aug 05 Sep 05 Dec 05 So, it's not in date order, nor alpha order. The data does appear in the source worksheet in date order. What would be a practical means of correcting this so that the pivot table comes up in proper date order? Or, what additional information should I post so that you can help me with this? Thank you in advance for any suggestions. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
I've been trying that. It won't let me group the data - "Because the field
is grouped, you cannot add a calculated item to it.". I've repeatedly ungrouped, but still get that message. -- Randy Harris (tech at promail dot com) "Debra Dalgleish" wrote in message ... Instead of using the Month field in the pivot table, use the Date field in the pivot table. Then, group the date by year and month. There are instructions he http://www.contextures.com/xlPivot07.html Randy Harris wrote: I'm not very experienced with Excel, in particular with Pivot tables, and would appreciate some help. I have an inherited spreadsheet with a number of pivot tables in it. The source data for the spreadsheet gets "refilled" from an Access database, but the rows in the pivot table don't generally get sorted correctly. The data is in the form of "month year" - e.g. Jan 05, Feb 05, Mar 05, etc. I tracked down the source of the data in the spreadsheet, it comes from a column on another sheet (column V), labeled Month. It's formula seems to be: =INDEX(Months,MONTH(F1),1)&" " &RIGHT(YEAR(F1),2) The pivot table frequently appears: Jul 05 Jan 05 Feb 05 Mar 05 Apr 05 May 05 Jun 05 Aug 05 Sep 05 Dec 05 So, it's not in date order, nor alpha order. The data does appear in the source worksheet in date order. What would be a practical means of correcting this so that the pivot table comes up in proper date order? Or, what additional information should I post so that you can help me with this? Thank you in advance for any suggestions. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
Then, change the Month formula to:
=DATE(YEAR(F1),MONTH(F1),1) In the pivot table, format the Month field (FormatCells, Number tab) to the custom format of mmm yy Randy Harris wrote: I've been trying that. It won't let me group the data - "Because the field is grouped, you cannot add a calculated item to it.". I've repeatedly ungrouped, but still get that message. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
|
|||
|
|||
That's a huge improvement. Is there a way to "globally" set the format for
the row label (to mmm yy) for that pivot table? I can set it, but then up above, when I switch to another year the formatting reverts back to the default (4/1/2004). "Debra Dalgleish" wrote in message ... Then, change the Month formula to: =DATE(YEAR(F1),MONTH(F1),1) In the pivot table, format the Month field (FormatCells, Number tab) to the custom format of mmm yy Randy Harris wrote: I've been trying that. It won't let me group the data - "Because the field is grouped, you cannot add a calculated item to it.". I've repeatedly ungrouped, but still get that message. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#6
|
|||
|
|||
I think I figured out why it wouldn't "globally" set that format. I went
back to the source data and put the format back to standard date (I had changed it to mmm yy trying to get this to work). Now I can set the format for the row heading. Thanks very much for your help. Randy Harris "Randy Harris" wrote in message ... That's a huge improvement. Is there a way to "globally" set the format for the row label (to mmm yy) for that pivot table? I can set it, but then up above, when I switch to another year the formatting reverts back to the default (4/1/2004). "Debra Dalgleish" wrote in message ... Then, change the Month formula to: =DATE(YEAR(F1),MONTH(F1),1) In the pivot table, format the Month field (FormatCells, Number tab) to the custom format of mmm yy Randy Harris wrote: I've been trying that. It won't let me group the data - "Because the field is grouped, you cannot add a calculated item to it.". I've repeatedly ungrouped, but still get that message. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
|
|||
|
|||
You're welcome. Thanks for describing how you solved the problem.
Randy Harris wrote: I think I figured out why it wouldn't "globally" set that format. I went back to the source data and put the format back to standard date (I had changed it to mmm yy trying to get this to work). Now I can set the format for the row heading. Thanks very much for your help. Randy Harris "Randy Harris" wrote in message ... That's a huge improvement. Is there a way to "globally" set the format for the row label (to mmm yy) for that pivot table? I can set it, but then up above, when I switch to another year the formatting reverts back to the default (4/1/2004). "Debra Dalgleish" wrote in message ... Then, change the Month formula to: =DATE(YEAR(F1),MONTH(F1),1) In the pivot table, format the Month field (FormatCells, Number tab) to the custom format of mmm yy Randy Harris wrote: I've been trying that. It won't let me group the data - "Because the field is grouped, you cannot add a calculated item to it.". I've repeatedly ungrouped, but still get that message. -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
Problems with Pivot Table Field Sorting in Excel 2002 | Excel Discussion (Misc queries) | |||
pivot table | Excel Worksheet Functions | |||
Problems with sorting a pivot table | Excel Discussion (Misc queries) |