PivotTable with multiple row and column headings

[alx7000]

Hi,

I am reasonably experienced at excel but have never really been a heavy user of PivotTables. I have searched for solutions to my current problem to no avail so here goes:

I have a data set that has multiple row headings and multiple column headings. I have attached a sample data set, the actual data set I am using is thousands of rows long.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1			Entity 1	Entity 2	Entity 3	Entity 4	Entity 5	Entity 1	Entity 2	Entity 3	Entity 4	Entity 5
2			2009	2009	2009	2009	2009	2010	2010	2010	2010	2010
3			Group A	Group A	Group A	Group B	Group B	Group A	Group A	Group A	Group B	Group B
4			Set X	Set X	Set Y	Set Y	Set Y	Set X	Set X	Set Y	Set Y	Set Y
5	Revenue	Revenue 1	29	48	51	58	94	10	7	60	76	24
6	Revenue	Revenue 2	82	34	75	56	81	59	33	42	68	39
7	Revenue	Revenue 3	98	85	71	91	17	99	91	19	13	86
8	Revenue	Revenue 4	32	49	89	64	78	13	78	64	79	48
9	Revenue	Revenue 5	29	35	51	99	93	79	88	41	24	80
10	Expenses	Expenses 1	13	26	53	84	11	22	14	68	78	37
11	Expenses	Expenses 2	99	2	83	55	8	26	52	71	3	96
12	Expenses	Expenses 3	19	13	75	43	4	54	74	72	4	95
13	Expenses	Expenses 4	48	20	64	37	49	61	21	56	29	40
14	Expenses	Expenses 5	66	45	93	46	67	41	35	58	8	81
All Data

Excel 2010

Is there a way that I can pull a PivotTable straight from the data in this format? Currently, I am first using a macro to pull out a line for each value that I then use to make a PivotTable. For example, the first 10 items in the sample set above are pulled out as follows:

Excel Workbook
	A	B	C	D	E	F	G
1	Entity	Year	Group	Set	Item 1	Item 2	Amount
2	Entity 1	2009	Group A	Set X	Revenue	Revenue 1	29
3	Entity 2	2009	Group A	Set X	Revenue	Revenue 1	48
4	Entity 3	2009	Group A	Set Y	Revenue	Revenue 1	51
5	Entity 4	2009	Group B	Set Y	Revenue	Revenue 1	58
6	Entity 5	2009	Group B	Set Y	Revenue	Revenue 1	94
7	Entity 1	2010	Group A	Set X	Revenue	Revenue 1	10
8	Entity 2	2010	Group A	Set X	Revenue	Revenue 1	7
9	Entity 3	2010	Group A	Set Y	Revenue	Revenue 1	60
10	Entity 4	2010	Group B	Set Y	Revenue	Revenue 1	76
11	Entity 5	2010	Group B	Set Y	Revenue	Revenue 1	24
Data for pivot

Excel 2010

From this data I can make a PivotTable that allows allows me to cut the data in many different ways easily. Can this be accomplished without the need to extract via a macro first?

Hopefully that all makes sense, any questions please let me know.

Thanks for your help,
alx7000

 

[Marcelo Branco]

Hi,

I *think* you have to first arrange your data as you did - by the way, well done!

Just in case, take a look at
http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

M.