Pivot Table Hide Row with no data?

Bebert

New Member
Joined
Oct 28, 2002
Messages
4
Hi there!
I face a problem with my Pivot Table. I would like to hide automatically the rows where there is no data in the TOTAL column (the last one). Is it possible?
Thanks in advance for your help!
Bebert
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

clare_voiant

Board Regular
Joined
Mar 20, 2002
Messages
76
you may find that in the drop down arrow for that column that there is a checkbox for "blank", uncheck this box.....
 

Bebert

New Member
Joined
Oct 28, 2002
Messages
4
Thanks for your replies, let me try to explain better:

The original table from where I have created the Pivot Table looks like this:

Cat Rplan Brate DLXK DLXQ DLXT
VOLA HWEB1 330
SPEC HWEB2 280 300
SPEC HWEB3 420 420 420 420
HTL3 HWEB3 250
VOLA HWEB4 400 400 400 400
VOLA HWEB4 230 250
HWEB HWEB5 200
HWEB HWEB6 170 200
HWEB HWEB7 140

On my Pivot Table, I would like to hide the lines where there is no figure in the Total column. Cat, RPlan and Data columns can be filtered, but not the Total column...

Cat RPlan Data Total
JRST 445
CORN
PRES 2005
KING
VOLA LFAW DLXK 165
DLXQ 165
DLXT 165

I'm not really sure that this is clear, but in case somebody understands...
Thank you!
Bebert
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

You need to reorganize your data (into 1st Normal Form -- 1NF) as shown below...
Book1
ABCDEFGHIJKLMN
12
13CatRplanBrateCategoryValueSumofValue
14SPECHWEB2280DLXQ300CatRplanCategoryTotal
15SPECHWEB3420DLXK420HWEBHWEB6DLXK200
16SPECHWEB3420DLXQ420SPECHWEB2DLXQ300
17SPECHWEB3420DLXT420HWEB3DLXK420
18VOLAHWEB4400DLXK400DLXQ420
19VOLAHWEB4400DLXQ400DLXT420
20VOLAHWEB4400DLXT400VOLAHWEB4DLXK400
21VOLAHWEB4230DLXT250DLXQ400
22HWEBHWEB6170DLXK200DLXT650
23
Sheet1

This message was edited by Mark W. on 2002-10-29 12:33
 

Bebert

New Member
Joined
Oct 28, 2002
Messages
4
Thank you Mark!
I see now what would be the solution, but I won't be able to use it since my 1NF has 20 different categories (DLXQ,DLXK,...) and more than 5000 lines for the time being. If I understand you properly, I would have to use different lines for each category and would then have 100'000 rows (less the ones with no value).
Thank you anyway for taking the time!
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
On 2002-10-30 03:22, Bebert wrote:
...If I understand you properly, I would have to use different lines for each category and would then have 100'000 rows (less the ones with no value)...

Perhaps it's time to think about moving your data to an external (from Excel) database application.
 

Forum statistics

Threads
1,143,613
Messages
5,719,710
Members
422,242
Latest member
hishamkhatri

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