Pivot Table - Hide Rows with Zero Value

maeyks

Board Regular
Joined
Aug 20, 2010
Messages
122
Dear All,

I am using Excel's pivot table function. How will I NOT show the row values that have zero or no values at all?

Thanks!


maeyks
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

CWatts

Well-known Member
Joined
Jan 22, 2010
Messages
701
You can right click one of your fields in the row section (generally the lowest-tiered) and you can add a value filter to only show values that are >0.
 

maeyks

Board Regular
Joined
Aug 20, 2010
Messages
122
You can right click one of your fields in the row section (generally the lowest-tiered) and you can add a value filter to only show values that are >0.

CWatts

That was what I needed! Thank you very much!! is there such a thing as "pivot within a pivot"?


maeyks
 

CWatts

Well-known Member
Joined
Jan 22, 2010
Messages
701
I've never heard of one if there is. :)

While the terminology may be unknown, what would this "pivot withinin a pivot" do? There may still be a way to make it happen.
 

maeyks

Board Regular
Joined
Aug 20, 2010
Messages
122

ADVERTISEMENT

I've never heard of one if there is. :)

While the terminology may be unknown, what would this "pivot withinin a pivot" do? There may still be a way to make it happen.

well, it may be hard to explain...but here goes..
i have with me a table with the following data:
column 1: date of request
column 2: request control number
column 3: delivery date
column 4: supplier's code
column 5: supplier's name
column 6: inventory code
column 7: inventory name

the inventory code/inventory name may be used repeatedly depending on the date when it was requested.

first, i want to isolate via pivot the data based on column 2, which is the request control number, and

second, further isolate the inventory code, based on the request control number..

was i able to explain it clearly?

maeyks
 

maeyks

Board Regular
Joined
Aug 20, 2010
Messages
122
I've never heard of one if there is. :)

While the terminology may be unknown, what would this "pivot withinin a pivot" do? There may still be a way to make it happen.

can i actually create a customized number series in excel? which will serve as a control number?
 

maeyks

Board Regular
Joined
Aug 20, 2010
Messages
122
I've never heard of one if there is. :)

While the terminology may be unknown, what would this "pivot withinin a pivot" do? There may still be a way to make it happen.

can i actually create a customized number series in excel? which will serve as a control number?


I have another question, what if i have the following data:
Request number Item Code Item Quanity
001 1234 Water 100
001 1122 Flour 200
001 5432 Sugar 6
001 1235 Salt 2
001 1334 Yeast 5
001 1432 Margarine 22
002 1234 Water 200
002 1122 Flour 400
002 5432 Sugar 12
002 1235 Salt 4
002 1334 Yeast 10
002 1432 Margarine 44
003 1234 Water 500
003 1122 Flour 1000
003 5432 Sugar 30
003 1235 Salt 10
003 1334 Yeast 25
003 1432 Margarine 110


This is on the first tab (A). Then i will create a second tab (b). Wherein on tab B, if i will select the request number 003 and placed item code number 1334, it will return the value 25 and not 5 or 10

Or, if i will select the request number 002 and placed item code 1235, it will return the value 4 and not 10 or 2.

Can this be answered by vlookup? this is the example what i call, "vlookup, within a vlookup." hope you can help me on this.

many thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,108,753
Messages
5,524,664
Members
409,597
Latest member
Dannydev

This Week's Hot Topics

Top