Forum Discussion
Pivot Table - Hiding Rows Based on Column Value
Hello! Seeking guidance on how I can hide rows in a pivot table if the value in a certain column is zero.
Overview: I have a data dump from an accounting database with > 100k sales transactions. Each transaction is categorized by one of five products sold (product A, B, etc.). I am making a table to show the buyer by rows then columns with the sales volume for each of the five products, as follows:
Product A Product B Product C etc,
Customer 1 $1000 $3000 $500
Customer 2 $0 $50000 $10000
Customer 3 $10000 $200 $0
In this example, I would like to hide Customer 3 since they have a zero in Product C. Any thoughts on how to set-up the table to accomplish this?
Thank you,
mucrick
A dirty solution would be to select the cell immediately to the rightmost header of the Pivot Table (PT), and then put a filter on that cell. This will insert filter buttons on each of the column headers inside the PT. Then you can filter out any value, e.g. blanks, zero's, amounts below, above or within boundaries. Whatever. The attached worksheet has a simplified example where the filter was put on L13. Just coloured it as an indicator. Now you can press the filter for product E and e.g. uncheck (Blanks). Hope this works for you.
Hello Riny_van_Eekelen , I was trying to figure this out for an hour and your solution worked perfectly! Unfortunately it doesn't apply to the associated pivot chart, do you know if its possible or not? Thanks in advance!
Paul_Hiroshi Can't really tell. Never tried that. Better to open a new conversation with your specific problem and add some more specific information.
