Pivot Table, Value criteria set to sum, hide zero's

flashgordie

Board Regular
Joined
Jan 9, 2008
Messages
63
I have a data source that deals with items going to customer on deposit. If they take one widget, there is a sale of 1, when they return the widget, there is a sale of -1. resulting in a Zero.

I have attached a picture. Over hundreds of customers and thousands of transactions, we can be dealing with a very long list showing both customers that have completed all of their returns, and those that still have widget to bring back. Withing the pivot table we would like to hide, eliminate, or filter our the zero's. As in my example I would like to hide Jane Sid and Ed. There are a couple of suggestions on the net, but many of them deal with filtering out the zero in the header of the pivot table, however as you can see, my data source has no zeros. Any ideas? TIA!

Data SetPivot Table
NameQtySum of Qty
Bob1NameTotal
Bob1Bob1
Jane1Jane0
Jane1Ken2
Bob-1Sid0
Ken1Ed0
Ed1Grand Total3
Ken1
Sid1
Ed1
Sid1
Bob-1
Bob-1
Bob1
Jane-1
Jane-1
Bob1
Ken1
Ed-1
Ken-1
Sid-1
Ed-1
Sid-1
 

Attachments

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,595
Office Version
2019
Platform
Windows
Click the filter box on 'Name' then go to Value Filters > Does Not Equal > then set the boxes to:- Sum of Qty Does Not Equal 0
 

Lebene

New Member
Joined
Sep 20, 2019
Messages
25
Hi,

to do this, right-click your pivot table in the name row and then select 'filter', then select 'value filters' then, 'does not equal' put in 0 for the value. This works.

edit: I'm a mighty slow! Above have already said it now.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,571
Messages
5,469,467
Members
406,654
Latest member
MR_EXCEL_16

This Week's Hot Topics

Top