Pivot Table with (blank) fields

Question

Dear all,

I have a question in regards to pivot table. I'm trying to create a pivot table to summarize the qty based on different orders. The tricky part is that the products have different variances based on color, size, and material. Some of the products have different color and sizes, some only different sizes, some only different materials, etc. Below is an example of the chart.

Order	Product	Color	Size	Material	Qty
1122	A	Black	S		2
1122	B	Red	S		1
1122	C			Cotton	1
1122	D		L		2
1122	E	Black			2
1122	F	Red			1
2233	G	Gold	M		2
2233	H	Red	M		1
2233	I			Nylon	1
2233	J		L		2

When I created pivot table, it becomes messy. If the product only has different material and no color or size difference, then I get (blank) fields for color and size. It shows product, (blank), (blank), material. So the table is big and messy. Is there a way that I can get pivot table to disregard (blank) fields and just move to the next field?  

Order	1122
Row Labels	Sum of Qty
A	2
Black	2
S	2
(blank)	2
B	1
Red	1
S	1
(blank)	1
C	1
(blank)	1
(blank)	1
Cotton	1
D	2
(blank)	2
L	2
(blank)	2
E	2
Black	2
(blank)	2
(blank)	2
F	1
Red	1
(blank)	1
(blank)	1
Grand Total	9

Thanks for your help.

Answer 1

Hi Muhammad,

Could you please advise what is the formula? Also, wondering where can I uncheck true?

Thanks.

Answer 2

Hi,

try this , create a helper column named Status then enter below formula G2 ,then copy down, move this new field to filter and uncheck True 

Hope it helps 

Regards

Answer 3

Hi Ashish,

The thing is some products still need this field.

Thanks.

Answer 4

Hi,

In the row labels filter drop down, remove the Blanks.

Answer 5

This might be easier to understand my current problem: