+ Reply to Thread
Results 1 to 9 of 9

Daily, Monthly, Yearly Pivot Tables

  1. #1
    Registered User
    Join Date
    09-04-2011
    Location
    Cheshire, England
    MS-Off Ver
    2010
    Posts
    11

    Daily, Monthly, Yearly Pivot Tables

    I have made a 'Data' worksheet containing many records with info on Date, Customer, Total.

    I have used 3 Pivot tables on seperate worksheets to make: a sum of the Daily Totals, sum of the Monthly Totals, and a sum of the Yearly totals.

    Because I'm new to Pivot Tables, and I dont know how to set it up to do it itself....
    On the 'Data' worksheet I have had to Make 3 columns for each date so that each pivot table shows what I want. This is annoying for everyone who is entering new data.
    It currently looks like this:
    Column A Column B Column C Column D Column E
    12.01.00 January'00 2000 P.Bloggs £35.00
    34.05.02 May'02 2002 T.Thompson £35.00
    12.01.00 January'00 2000 C.Cook £45.00
    12.01.00 January'00 2000 N.McGregor £35.00

    Is there a better way of doing this??

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Daily, Monthly, Yearly Pivot Tables

    Hi danifani,

    Find the attached data with a pivot table example. I've created a bunch of random data and then a Pivot Table of the data.

    I'm hoping your version 2003 can show the grouping by dates and filter by customer. If not, I'd suggest upgrading to 2010 Excel where Pivot Tables have more features.

    Let me know if you can group and expand this example and filter.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    09-04-2011
    Location
    Cheshire, England
    MS-Off Ver
    2010
    Posts
    11

    Re: Daily, Monthly, Yearly Pivot Tables

    Wow thanks! Thats so simple, yet amazing!!!

    But.... how did you manage to get the 'months' and 'years' in the Row Labels if they aren't columns on your original table??

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Daily, Monthly, Yearly Pivot Tables

    You use dates as Rows and find the "Group By" feature.
    http://www.contextures.com/xlPivot07.html

    There is so much stuff hiding in Excel, I'm still finding things I never knew.
    Last edited by MarvinP; 09-18-2011 at 03:39 PM.

  5. #5
    Registered User
    Join Date
    09-04-2011
    Location
    Cheshire, England
    MS-Off Ver
    2010
    Posts
    11

    Re: Daily, Monthly, Yearly Pivot Tables

    I've just discovered it was done by 'grouping', from playing around with your table.

    Thank you VERY much!!!

  6. #6
    Registered User
    Join Date
    09-04-2011
    Location
    Cheshire, England
    MS-Off Ver
    2010
    Posts
    11

    Re: Daily, Monthly, Yearly Pivot Tables

    Hi, sorry to be a pain.... but I cant do it now!

    It was working fine with some sample data - but now I'm trying again and it wont group!

    I have been through looking for any blanks in the table, as well as checked the specific columns are formatted as dates/currency... but when I try group it doesn't work. It does not give me the option to 'group field' and if I right click the 'Date' field there is nothing about grouping.

    Im stumped... once again! Haha

  7. #7
    Registered User
    Join Date
    10-30-2003
    Location
    Dunstable, England
    MS-Off Ver
    2010
    Posts
    69

    Re: Daily, Monthly, Yearly Pivot Tables

    Attached is a small pivot data, your information and I little more (dates and years).

    I have used the slicer which is available in 2010 (you state you are using Office 2010).

    Slicers are found under the same Tab as the Pivot Table (Insert).

    Click on the date and it filters to that date, or month, or year - or all. To clear them just click on the filter button at the top of the slicer box to clear it.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-04-2011
    Location
    Cheshire, England
    MS-Off Ver
    2010
    Posts
    11

    Re: Daily, Monthly, Yearly Pivot Tables

    Thank you!! That's perfect

  9. #9
    Registered User
    Join Date
    10-30-2003
    Location
    Dunstable, England
    MS-Off Ver
    2010
    Posts
    69

    Re: Daily, Monthly, Yearly Pivot Tables

    You welcome.

    Make sure you mark your post as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1