+ Reply to Thread
Results 1 to 8 of 8

Blanks in Pivot / Slicer / Charts

  1. #1
    Registered User
    Join Date
    07-16-2019
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    4

    Blanks in Pivot / Slicer / Charts

    Hej,

    I have a problem. I cant get rid of Blanks in my pivot which then shows in the Charts.... I know how to tick the box in drop down list but when I have done that and play with the Slicer Blank of course comes back again.
    Se below pics ... how can this be fixed?

    1.PNG2.PNGAttachment 6325273.PNG

    BR Mikka

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,985

    Re: Blanks in Pivot / Slicer / Charts

    You could use Power Query to filter out blank months from your source table, load to Data Model, then base your Pivot Table on the Data Model.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    07-16-2019
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    4

    Re: Blanks in Pivot / Slicer / Charts

    Never worked with Power Query but will check it up. Until then - is there any easier way to solve this without change to much?

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,985

    Re: Blanks in Pivot / Slicer / Charts

    That is the easier way.

    Upload a sample workbook, and I'll show you.

  5. #5
    Registered User
    Join Date
    07-16-2019
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    4

    Re: Blanks in Pivot / Slicer / Charts

    Is this OK? I know that if I do not select whole Columns it works but I want to add data so I need to select whole columns. If u know what I mean.
    Attached Files Attached Files

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,985

    Re: Blanks in Pivot / Slicer / Charts

    Your source data is already formatted as a Table, which will dynamically resize as you add data - so just change your Pivot data source from Sheet1!$A:$R to TableGrundData. Problem solved.

  7. #7
    Registered User
    Join Date
    07-16-2019
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    4

    Re: Blanks in Pivot / Slicer / Charts

    Quote Originally Posted by Olly View Post
    Your source data is already formatted as a Table, which will dynamically resize as you add data - so just change your Pivot data source from Sheet1!$A:$R to TableGrundData. Problem solved.
    I f*ing Love u Olly:D Sorry for my incompetence.

  8. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,985

    Re: Blanks in Pivot / Slicer / Charts

    Don't be sorry! Happy to help. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You may also "add reputation" to those who helped you. Thanks.

+ 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