How to create a single pivot table from multiple sheets in the workbook

[KNMSRK]

How to create a single pivot table from multiple sheets in the workbook

 

[AlanSidman]

This link will give many options

https://www.google.com/search?q=cre...57j69i60l2.19566j0j7&sourceid=chrome&ie=UTF-8

 

[KNMSRK]

Hi Alan,
Please find attached the file, I have tried to create a pivot for the data in two sheets, however I cannot move the headers into rows or columns
Ex: I want the "subject" and "Name" to be placed in rows and marks in column
I have created a pivot for the data in a single sheet, and need a new pivot for the data in multiple sheet in the same format

 

[AlanSidman]

Attached is your file with a proposed solution using Power Query. I uploaded each table to PQ. Appended one table to the other and then pivotted the data.

Data Range

	A​	B​	C​	D​	E​
1​	Name​	Social​	Science​	Maths​	English​
2​	Neel​	210​	150​	105​	255​
3​	Sravs​	180​	120​	135​	285​
4​	Sree​	150​	240​	165​	135​

 

[KNMSRK]

ThnQ for the quick reply Alan

Did you debug your code first?http://www.cpearson.com/excel/DebuggingVBA.aspx - I could not do this as we are not supposed to download or open any links at office... My query is completely for office purpose.

About the power Query..... I have gone through the video, its really good and it is useful... Unfortunately we do not have MS office 2016 installed in office laptop.....

Please suggest any other solution....
Thank you!!

 

[AlanSidman]

Power Query is available as a free add in download starting with Excel 2010. If you are using an earlier version-- otherwise google it and download. Talk to your IT department as this is a valid add-in to excel.