+ Reply to Thread
Results 1 to 5 of 5

Removing Blank from Pivot and Slicer

  1. #1
    Registered User
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    87

    Question Removing Blank from Pivot and Slicer

    Hi everyone,

    I have a pivot table that sources its data from a table in another sheet. The sourcing table includes multiple lines, including blank ones for the user to populate when needed (the size of the table is fixed, hence the need for empty lines). The last column ("TOTAL") of the table is a formula that applies to all lines, even the empty ones.

    When creating a pivot table from that source, I end up with a "Blank" category in my slicer. I see that if I remove the formulas of the last column for the empty lines (the "0" in red in the attached doc), it solves the issue. However, I need to keep this formula throughout the table because I need to have a "Total" column in my pivot table (and I dont want to need the user to add the formula on each new line he populates).

    It's probably clearer when looking at the attached file but please let me know if I'm not clear

    Thanks a lot
    Attached Files Attached Files
    Last edited by MagnusNovak; 12-14-2020 at 08:01 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,281

    Re: Removing Blank from Pivot and Slicer

    Why do you need blank rows when the user only has to right click the last row and select "insert, row" or alternatively, tab from the lower right cell in the table to auto add a row as data is entered.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    87

    Re: Removing Blank from Pivot and Slicer

    Because the source table will be in a worksheet that is locked so they can't increase or reduce its size.

    But even if the sheet wasn't locked, it could frequently occur that the user creates more rows than he needs, leaving some empty, which would result in the same issue.

    Thanks

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,800

    Re: Removing Blank from Pivot and Slicer

    You would have to change the source data for the pivot table to use a dynamic named range - on the assumption that there is at least one column that will always be populated for every row, and that the user won't leave any blank rows in between data.
    Rory
    I drink, and I know things

  5. #5
    Registered User
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    87

    Re: Removing Blank from Pivot and Slicer

    Thanks rorya, I decided to go the dirty way (i.e. the "Total" column (which is the culprit of the "blank" in the slicer) is not part of the pivot table anymore but instead it is a custom formula (outside of the pivot table) that calculates the total if it refers to an appropriate line of the pivot table)

+ 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. Disconnect Slicer, Change Pivot Source, Reonnect Slicer
    By ensi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-27-2020, 04:26 AM
  2. Replies: 1
    Last Post: 09-11-2019, 11:54 AM
  3. Removing Blank Cells in the Pivot Table. (Blank) - Out of Range Cells
    By satishmen in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-09-2019, 06:30 AM
  4. Loop through slicer, while also selecting single slicer value on separate slicer
    By as_sass in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2018, 01:41 PM
  5. Replies: 1
    Last Post: 04-23-2018, 09:43 AM
  6. Removing "(Blank)" from pivot table slicers
    By tnovak in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-18-2014, 08:11 AM
  7. removing the word (blank) from pivot table cells
    By techman41973@yahoo.com in forum Excel General
    Replies: 2
    Last Post: 07-02-2005, 10:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1