Removing Blank from Pivot and Slicer

[MagnusNovak]

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

[protonLeah]

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.

[MagnusNovak]

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

[rorya]

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.

[MagnusNovak]

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)