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

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,749
I have the following data:

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


When I constructed a pivot it looks like this:
Book1
ABCD
3SumofRevenue
4RegionTotal
5North
6South1
7West3
8GrandTotal4
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
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
ABCDEF
1RegionSalesSumofSales
2NorthRegionSalesTotal
3South1South1
4West3West3
5Down5Down5
6Under7Under7
7GrandTotal16
Sheet1
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,749
Hi,

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

Anybody have any idea?
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,749
*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.
 

Forum statistics

Threads
1,136,990
Messages
5,678,983
Members
419,797
Latest member
ikethegenius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top