Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 17th 08, 05:07 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 124
Default 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   Report Post  
Old April 17th 08, 07:09 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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   Report Post  
Old April 17th 08, 07:33 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,069
Default 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   Report Post  
Old September 26th 17, 08:52 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 1
Default Group by month in a Pivot table

This really worked, Hutch. Great idea, how to resolve the issue in Google Docs. Thanks.
  #5   Report Post  
Old September 26th 17, 09:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Group by Month in Pivot-table [email protected] Excel Discussion (Misc queries) 4 February 20th 08 05:48 PM
Pivot table - group dates per week or month digicat Excel Discussion (Misc queries) 1 January 8th 06 08:46 PM
Can't group pivot table items by month in Excel scott_ensley Excel Discussion (Misc queries) 1 February 1st 05 08:41 PM
Pivot Table - no group on web Tracy D. Excel Discussion (Misc queries) 0 December 16th 04 05:57 PM
Pivot Table - Group by Month - Show Items with no data Dan Reynolds Excel Discussion (Misc queries) 0 November 28th 04 01:01 AM


All times are GMT +1. The time now is 02:01 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017