+ Reply to Thread
Results 1 to 7 of 7

Why my Pivot Table Filter shows individual dates

  1. #1
    Registered User
    Join Date
    11-06-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    29

    Why my Pivot Table Filter shows individual dates

    Hi Guys,

    I have a pivot table (based on a 50k rows of data), i put one column (date)
    in the Filter section of the pivot, but when i click the filter it shows individual
    dates unlike in regular excel file which groups the date all dates by months,
    year...this is my problem if i select the filter to choose only the dates i want
    to show, i had to individually pick them one by one.

    is there a way to make it look like a regular filter?


    Thanks,

    chico

  2. #2
    Forum Contributor
    Join Date
    05-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    291

    Re: Why my Pivot Table Filter shows individual dates

    Hi Chico,

    after clicking on filter go to date filter option there you will find desired options

    Thanks
    A

  3. #3
    Registered User
    Join Date
    11-06-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Why my Pivot Table Filter shows individual dates

    Hi amy,

    i did that, only shows individual date.

    Capture10.JPG

    Thanks,
    chico

  4. #4
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    211

    Re: Why my Pivot Table Filter shows individual dates

    Anyone ever find an answer for this? I have been wondering this for years!
    It is so helpful, like in excel, when you can open that drop down box and see a filter for each year, rather than having to click on each one.

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

    Re: Why my Pivot Table Filter shows individual dates

    Hi,

    If you right click on any of the dates and then GROUP them, then the dropdown will show those groups. If you haven't grouped the dates it will show them like you show above.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    211

    Re: Why my Pivot Table Filter shows individual dates

    Thank you so much for the reply.
    I am not looking to group the data in my pivot table, only the drop down menu within the pivot table. I have attached my example. Thank you!
    Attached Images Attached Images

  7. #7
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Why my Pivot Table Filter shows individual dates

    Are you able to add another column to your Data?
    If so, add a column after Invoice Date, named "Year", and insert "=TEXT(A2,"YYYY)" and copy down (A2 is your date cell?)
    In your pivot table add the "Year" to the Report Filter and use this to show the Year(s) you want to view.
    (If you can attach a copy of your file it would help)

    peterrc

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Auto update pivot table filter dates
    By kaitco in forum Excel General
    Replies: 5
    Last Post: 11-19-2010, 01:40 PM
  2. Replies: 2
    Last Post: 10-25-2010, 11:31 AM
  3. Pivot Table - Group Individual Dates Into Months
    By Kolin in forum Excel General
    Replies: 0
    Last Post: 07-15-2010, 11:02 AM
  4. Replies: 0
    Last Post: 05-12-2010, 05:01 PM
  5. Replies: 2
    Last Post: 01-22-2010, 04:00 PM

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