SOLVED

filter out 0 in pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-2406151%22%20slang%3D%22en-US%22%3Efilter%20out%200%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2406151%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20experts%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20filter%20out%20Zero%20(0)%20in%20a%20pivot%20table%20and%20I%20do%20not%20see%20how%20I%20can%20do%20that.%26nbsp%3B%20The%20issue%20is%20that%20th%20column%20in%20question%20is%20a%20%22difference%20from%20previous%22%20and%20it%20seems%20that%20the%20filtering%20option%20is%20no%20longer%20an%20option%20for%20a%20column%20calculated%20this%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGrateful%20for%20you%20assistance.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%20sample%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Untitled.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285480i479422577E1DD8B4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Untitled.jpg%22%20alt%3D%22Untitled.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2406151%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2406761%22%20slang%3D%22en-US%22%3ERe%3A%20filter%20out%200%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2406761%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3BIndeed%2C%20the%20built-in%20Filter%20option%20applies%20the%20%22difference%20from%22%20to%20the%20previous%20date%20displayed.%20Perhaps%20a%20more%20raw%20hack%20will%20work%20for%20you.%20Select%20cell%20K3%20and%20switch%20on%20the%20auto%20filter.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-06-02%20at%2006.52.16.png%22%20style%3D%22width%3A%20102px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285519iAA6888F810B09A2F%2Fimage-dimensions%2F102x134%3Fv%3Dv2%22%20width%3D%22102%22%20height%3D%22134%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-06-02%20at%2006.52.16.png%22%20alt%3D%22Screenshot%202021-06-02%20at%2006.52.16.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20will%20insert%20filter%20buttons%20in%20row%203%20of%20the%20pivot%20table.%20Now%20you%20can%20filter%20out%20the%20zero's%20from%20the%20Change%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2407936%22%20slang%3D%22en-US%22%3ERe%3A%20filter%20out%200%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2407936%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Riny%2C%20thank%20you%20for%20the%20response%20but%20I%20have%20to%20say%20I%20do%20not%20see%20the%20option%20as%20described%20above.%20I%20click%20on%20the%20header%20called%20%22Change%22%2C%20which%20is%20cell%20I3%20and%20I%20right%20click%20and%20the%20below%20pic%20is%20what%20I%20see.%20I%20am%20not%20sure%20what%20I%20am%20doing%20wrong.%20thank%20you%20for%20the%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%26nbsp%3B%20I%20am%20not%20sure%20why%20the%20pic%20below%20appears%20as%20an%20attachment%20instead%20of%20a%20viewable%20pic%20like%20you%20posted%20in%20your%20post.%26nbsp%3B%20Maybe%20there%20is%20a%20hack%20for%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent 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 (Frequent 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.