Hide (blank) row headings/subtotals in pivot tables

New Contributor

Hi Team,

 

I am looking for a solution to blank heading/subtotal rows in a pivot table with a multi level hierarchy. A label filter is an easy enough solution providing the (blank) label value is at the lowest level of the hierarchy, however if the (blank) label value is in one of the higher "grouping" levels the label filter will remove all the rows in the (blank) group.

 

In the attached chart of accounts there are 5 grouping levels, with 1 being the highest grouping level (Income Statement) and 5 being the individual posting account level. Where there is no grouping required for any intermediate level (level 2 to level 4) a subtotal for "(blank)" is created in the pivot table.

 

Is there a way to remove the heading/total for the (blank) categories from the pivot table, or is there a way to structure the chart of accounts to achieve this outcome, or a VBA solution, or are there any other alternatives that will get the result I am after? I am open to suggestions.

 

Thanks for your time.

 

Regards,

Alistair

2 Replies
Click on the filter dropdown in A1. Select the field "Reportlevel2" in the dropdown of the filterpane and then uncheck "(Blank)". Press OK. Repeat for field "Reportlevel3".

Thanks JKP, however if a filter is used to exclude "(blank)" for "ReportLevel2" then all the child records of "(blank)" are filtered out of the report. All the records need to remain in the report, it is just the heading and subtotal that I want removed.

 

If you open the file and do what you have suggested, the total of the income statement changes. The total of the income statement needs to remain the same.

 

Cheers.