SOLVED

filter out 0 in pivot table

Steel Contributor

Hello experts,

 

I need to filter out Zero (0) in a pivot table and I do not see how I can do that.  The issue is that th column in question is a "difference from previous" and it seems that the filtering option is no longer an option for a column calculated this way.

 

Grateful for you assistance.  

Please see attached sample file. 

 

 

Untitled.jpg

4 Replies

@Tony2021 Indeed, the built-in Filter option applies the "difference from" to the previous date displayed. Perhaps a more raw hack will work for you. Select cell K3 and switch on the auto filter.

Screenshot 2021-06-02 at 06.52.16.png

This will insert filter buttons in row 3 of the pivot table. Now you can filter out the zero's from the Change column.

@Riny_van_Eekelen 

Hi Riny, thank you for the response but I have to say I do not see the option as described above. I click on the header called "Change", which is cell I3 and I right click and the below pic is what I see. I am not sure what I am doing wrong. thank you for the help.

 

Note:  I am not sure why the pic below appears as an attachment instead of a viewable pic like you posted in your post.  Maybe there is a hack for that?

 

best response confirmed by Tony2021 (Steel Contributor)
Solution

@Tony2021 See attached.

 

I got it now. I thought in your response above when you referred to K3 I thought it was a type since K3 is outside of the pivot but it seems this is a a hack. I see now. Very nice indeed.

thank you.
1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution