How to "Do not show items with no data" in pivot t

[PATSYS]

I have the following data:

Region - Revenue
South - 1
North - (Blank)
West - 3


When I constructed a pivot it looks like this:

Book1
	A	B	C	D
3	SumofRevenue
4	Region	Total
5	North
6	South	1
7	West	3
8	GrandTotal	4
Sheet1

I have already made sure that the check box "Show items with no data" is unchecked for the Region field.

So why is pivot still showing items with no data? How do I make it hide items with no data?

Thanks

 

[sunnyland]

Hello,

I never noticed before but it doesn't seems to work when you only have one field[column or row]
See sample below by adding the field revenue in your case it should then work:

Book3
	A	B	C	D	E	F
1	Region	Sales		SumofSales
2	North			Region	Sales	Total
3	South	1		South		1
4	West	3		West		3
5	Down	5		Down		5
6	Under	7		Under		7
7				GrandTotal		16
Sheet1

 

[PATSYS]

Hi,

Even if you have multiple columns, cells with 0 are still showing up.

Anybody have any idea?

 

[PATSYS]

*Bump

Well just for everybody's info, I sort of found a workaround though far form being ideal solution.

What I did what drag the DATA field into the page field, then hide the zero and blank items in the page field.

However one will have problems if you have several data fields and furthermore, if there are 1000 lines in your data with zero values, then you will have to hide 1000 items in your page field.