Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group by month in a Pivot table
I have a sales report for the last two years that has three columns,
1)Customer (there are several entries per month for each customer) 2) Invoice Date 3) Amount. I want to create a Pivot table that in column A lists each customer (only once) and has 24 other columns Jan 2006 €“ Dec 2007 that sums the total sales for each customer. When I set the pivot table up I get a column for each date and have no idea how to group it by month. Thanks in advance for any advice you can give!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group by month in a Pivot table
Rightclick on any of those dates in the column area (after you finish the
pivottable). Select Group and show detail Choose Months (and Years???) If you have any text or empty cells in that date field, excel won't let you group your data. Charles wrote: I have a sales report for the last two years that has three columns, 1)Customer (there are several entries per month for each customer) 2) Invoice Date 3) Amount. I want to create a Pivot table that in column A lists each customer (only once) and has 24 other columns Jan 2006 €“ Dec 2007 that sums the total sales for each customer. When I set the pivot table up I get a column for each date and have no idea how to group it by month. Thanks in advance for any advice you can give!! -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group by month in a Pivot table
One way...
Add a helper column to your source data. For example, if the Invoice Date is in column B, the first unused column is D, and the first row of data is row 2: - Add MONTH as a heading in D1. - In D2, enter this formula: =Year(B2)&"-"&Month(B2) - Copy D2 down through all rows of data - Create the pivot table including the MONTH data in column D - Make MONTH your column field. The formula above will give year & month in the format yyyy-mm (ie, 2008-03). This format will automatically sort in the correct order. If you want a format like "Mar 2008", then use this formula instead in D2: =TEXT(B2,"mmm yyyy") When you create the pivot table with MONTH as your column field, they will be sorted alphabetically. Right-click on the MONTH field heading in the pivot table and select Field Settings. On the Pivottable Field dialog that appears, click Advanced. On the next dialog, set AutoSort options to Manual. Click OK a couple of times to close the dialog. Now you can drag each month to its correct position. Hope this helps, Hutch "Charles" wrote: I have a sales report for the last two years that has three columns, 1)Customer (there are several entries per month for each customer) 2) Invoice Date 3) Amount. I want to create a Pivot table that in column A lists each customer (only once) and has 24 other columns Jan 2006 €“ Dec 2007 that sums the total sales for each customer. When I set the pivot table up I get a column for each date and have no idea how to group it by month. Thanks in advance for any advice you can give!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group by month in a Pivot table
This really worked, Hutch. Great idea, how to resolve the issue in Google Docs. Thanks.
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group by month in a Pivot table
On Tuesday, September 26, 2017 at 9:52:24 PM UTC+2, Peter Z. wrote:
This really worked, Hutch. Great idea, how to resolve the issue in Google Docs. Thanks. I would just add the following syntax, so the months can be ordered from left to right ascending in order: =year(A3)&"-"&if(month(A3)<10;"0";"")&MONTH(A3) P. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group by Month in Pivot-table | Excel Discussion (Misc queries) | |||
Pivot table - group dates per week or month | Excel Discussion (Misc queries) | |||
Can't group pivot table items by month in Excel | Excel Discussion (Misc queries) | |||
Pivot Table - no group on web | Excel Discussion (Misc queries) | |||
Pivot Table - Group by Month - Show Items with no data | Excel Discussion (Misc queries) |