Group by month in a Pivot table

[Charles]

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]

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.

 

[Tom Hutchins]

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