• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

KNMSRK

New Member
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
 

Attachments

AlanSidman

Active Member
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​
 

Attachments

KNMSRK

New Member
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

Active Member
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.
 
Last edited:
Top