+ Reply to Thread
Results 1 to 6 of 6

Pivot Table Date Filtering

  1. #1
    Registered User
    Join Date
    10-09-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2016
    Posts
    48

    Pivot Table Date Filtering

    Morning kind folks.

    A little help if you can.

    I have a Pivot Table which works as it should. But I tried to make it a bit neater and I just cant get to what I want from it. I hope you have some suggestions:

    The data its pivoting only covers the last 12 months. WHich is perfect. The dates are grouped by Month then Year on the Pivot Table. This works well if there are data points in each month of the last 12. However, if theres a month with nothing in it then the Pivot misses out that row. If I ask it to display all blank rows, then it adds in for the whole two years (2019/20) instead of just the blank months in the last 12. Is there a way around this?

    thanks

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,719

    Re: Pivot Table Date Filtering

    Does this help?

    https://exceljet.net/pivot-table/piv...s-with-no-data
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    10-09-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Pivot Table Date Filtering

    Thanks, sadly no.

    Thats exactly what I have been doing. If there are pieces of data in every month I want to use the the table shows

    2019
    - July
    - Aug
    - Sep
    - Oct
    - Nov
    - Dec
    2020
    - Jan
    - Feb
    - Mar
    - Apr
    - May
    - Jun

    Which is great. If theres a month with nothing, then it changes to for example


    2019
    - July
    - Aug
    - Sep
    - Oct
    - Nov
    - Dec
    2020

    - Apr
    - May
    - Jun

    If I select show items with no data, it shows

    2019
    - Jan
    - Feb
    - Mar
    - Apr
    - May
    - Jun
    - July
    - Aug
    - Sep
    - Oct
    - Nov
    - Dec
    2020
    - Jan
    - Feb
    - Mar
    - Apr
    - May
    - Jun
    - July
    - Aug
    - Sep
    - Oct
    - Nov
    - Dec

    Cant I just get this with zeros in the empty months?
    2019
    - July
    - Aug
    - Sep
    - Oct
    - Nov
    - Dec
    2020
    - Jan
    - Feb
    - Mar
    - Apr
    - May
    - Jun

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,719

    Re: Pivot Table Date Filtering

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Last edited by AliGW; 07-06-2020 at 05:52 AM.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,719

    Re: Pivot Table Date Filtering

    Can't do much until you attach the WB ...

  6. #6
    Registered User
    Join Date
    10-09-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Pivot Table Date Filtering

    Attachment added, a blank version with just the dates on it
    Attached Files Attached Files

+ 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. Replies: 5
    Last Post: 07-01-2019, 08:01 AM
  2. [SOLVED] Pivot table filtering date to date
    By sovietchild in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 07-31-2018, 04:40 AM
  3. Pivot Table Report Filtering by Date
    By Designs in forum Excel General
    Replies: 3
    Last Post: 02-11-2016, 08:23 AM
  4. Filtering Date in Pivot Table data from cube
    By Achuks in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2015, 02:04 AM
  5. Filtering based on date in Pivot Table
    By jswalemail in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-10-2014, 09:44 PM
  6. Filtering Minimum Date Field in Pivot Table
    By investmentbanker in forum Excel General
    Replies: 1
    Last Post: 07-13-2012, 05:38 PM
  7. Pivot table date filtering question?
    By s_zabihi in forum Excel General
    Replies: 10
    Last Post: 12-06-2011, 12:08 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