Pivot table horizontal split

[EduPAz]

Hi,

Can someone please help me with this doubt:

I have a pivot table with these characteristics:

- The columns are the months from January to December.
- Each columns (month) has two types of products (A and B)
- I have two rows (R1 and R2)

I would like 2 columns like this:
- For R1, Sales in Januray for Product A. Sales in February for Product A, Sales in March for Product A.... Sales in December for Product A
- The same for R2


Can this be done in a pivot table? I only got an horizotal total (product A and B). I am not able to see a horizontal total split in 2 (product A and Product B).

Thanks!

Andres

 

[EduPAz]

That option gives me a Grand Total (Product A + Product B) for the whole row. It doesn't allow me to split the total (Total for Product A and Total for Product B)

 

[sheetspread]

Excel 2010
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
3	Sum of Sales	Column Labels
4		Jan	Jan	Feb	Feb	Mar	Mar	Apr	May	May	Jun	Jun	Jul	Aug	Aug	Sep	Sep	Oct	Oct	Nov	Grand Total
5	Row Labels	A	B	A	B	A	B	B	A	B	A	B	B	A	B	A	B	A	B	A
6	R1	56		36		41	68			8	72		70		59	60		93		80	643
7	R2		40		87			137	5			33	71	27			2	11	57	84	554
8	Grand Total	56	40	36	87	41	68	137	5	8	72	33	141	27	59	60	2	104	57	164	1197
Sheet3

You do have the total for each product per month (?)


or do you want:

Excel 2010
	F	G	H	I	J	K	L	M	N	O	P	Q	R
10	Sum of Sales	Column Labels
11	Row Labels	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Grand Total
12	A	56	36	41		5	72		27	60	104	164	565
13	B	40	87	68	137	8	33	141	59	2	57		632
14	Grand Total	96	123	109	137	13	105	141	86	62	161	164	1197
Sheet3

where you would leave off the Type field on this or an extra pivot table.

 

[EduPAz]

It should be like this table. How can I get a Grand Total (column Z) for A and B?
I would like to have a Gran total for each product

	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
Sum of Sales	Column Labels
Jan	Jan	Feb	Feb	Mar	Mar	Apr	May	May	Jun	Jun	Jul	Aug	Aug	Sep	Sep	Oct	Oct	Nov	Grand Total
Row Labels	A	B	A	B	A	B	B	A	B	A	B	B	A	B	A	B	A	B	A
R1
R2
Grand Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]56[/TD]
[TD="align: right"][/TD]
[TD="align: right"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"][/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"]93[/TD]
[TD="align: right"][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]643[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]554[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]56[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]164[/TD]
[TD="align: right"]1197[/TD]

</tbody>

Sheet3​

 

[sheetspread]

You can show that in an adjacent pivot table:

Excel 2010
	F	G
10	Row Labels	Sum of Sales
11	A	565
12	B	632
13	Grand Total	1197
Sheet3

 

[EduPAz]

Is not possible to show it in the same pivot table, right?

 

[sheetspread]

I don't think so, but does the extra table show everything you wanted?

 

[EduPAz]

Yes, the extra table will solve the problem.
Thank you very much for your answers. They were very helpful.