How to get monthly group in Pivot Table

[IkshuL]

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!!

[Adrut]

Right click on any of those dates in the column area (after you finish the pivot table). 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.

[Dingbang]

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 Pivot-table 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.