Results 1 to 3 of 3

Thread: How to get monthly group in Pivot Table

  1. #1
    Join Date
    Oct 2011
    Posts
    46

    How to get monthly group in 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. #2
    Join Date
    Apr 2010
    Posts
    80

    Re: How to get monthly group in Pivot Table

    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.

  3. #3
    Join Date
    Apr 2010
    Posts
    85

    Re: How to get monthly group in Pivot Table

    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.

Similar Threads

  1. How to unpivot the pivot table in excel
    By Raju Chacha in forum MS Office Support
    Replies: 2
    Last Post: 08-01-2012, 02:43 PM
  2. How to copy a pivot table into a 2 dimensional table ?
    By Udyami in forum Windows Software
    Replies: 4
    Last Post: 27-11-2010, 11:05 PM
  3. To convert a pivot table to a flattened table in MS Excel
    By zeemga in forum Windows Software
    Replies: 3
    Last Post: 27-11-2010, 06:18 AM
  4. How to create a Pivot Table report
    By Computer_Freak in forum Tips & Tweaks
    Replies: 1
    Last Post: 14-03-2009, 06:23 PM
  5. Pivot Table in Excel 2007
    By NIcaBoy in forum Windows Software
    Replies: 2
    Last Post: 02-03-2009, 02:46 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,639,048,898.93434 seconds with 17 queries