Removing zero value rows in pivot table

[Darren_workforce]

Good day. We receive a file each month and based on choices our customers make, it will add either a 0 or a 1 into the respective category. Once we run the pivot table though, as you can see, it tallies up all of the 1's but it also displays the zeroes and the corresponding sub label (apologies if that's not the proper term) rows associated with them. As you can see, it makes the table very large and cluttered.

Is there a way to remove the sub label rows that have a 0 value all the way across? I didn't want to use conditional formatting to hide the zeroes as it would still allow the headers to show. Just looking to slim down a bit. I attempted using slicers but those only allowed me to filter 1 of the 3 headers and it caused the other 2 header values to be hidden regardless if they were a 0 or 1. Is what I'm asking even possible? I'm not opposed to using vba if that is an option. Thank you in advance.

 

[James006]

Hi,
If I am not mistaken ... place your cursor on any of your Zero value and hit space bar ...

 

[Darren_workforce]

If I am not mistaken ... place your cursor on any of your Zero value and hit space bar ...

Hello James,
Doing that gives the following popup:

I'm hoping to format the table so that it looks like the example below. The zeroes are ok if at least 1 of the 3 columns has data.

 

[James006]

Hi again,

Right click on your PivotTable and select Pivot-Table Options, then use the "For empty cells show" ...

 

[Darren_workforce]

Hi again,

Right click on your PivotTable and select Pivot-Table Options, then use the "For empty cells show" ...

The cells aren't technically empty. When customers choose yes, a 1 appears. When they choose no, a 0 appears. So there's never an empty cell. That is the data that is being captured in the pivot table. I would just like to see if it was possible to remove/hide all rows in the pivot table if all 3 headers have a value of '0'.

 

[James006]

Among the possibilities, you can test the Custom Format ;;;

 

[Darren_workforce]

Among the possibilities, you can test the Custom Format ;;;

To some degree, that helped remove the data being displayed but it's not removing the sub labels (Procedural Question, Note Modification, etc).

 

[James006]

So basically ... you do not want to remove zeros from your Pivot Table ...

What you are after is to Filter Out items which happen to be identified by zero ...

 

[Darren_workforce]

So basically ... you do not want to remove zeros from your Pivot Table ...

What you are after is to Filter Out items which happen to be identified by zero ...

Right...but only if entire rows have 0 values across all categories. I hope the images help get my idea across. For example, since 'Procedural Questions' has 3 0's across the 3 categories ("Sum of..." headers), that row would get removed. Same thing with the rest of the rows that reflect a 0 across all 3 categories.

 

[karinfromsweden]

I have the exact same question, did you find a solution for this @Darren_workforce?