Consolidate multiple sheet pivot table

[Syedali]

Hi,

I have data in excel sheet.
1.Header are same
2.Different city wise excel sheet data

I want use pivot table to summarize all sheet without consolidate this in to single sheet. I want to us Multiple consolidation range.
I did that but am not able visualize like attached pivot sheet.

Kindly help on this...

 

[GraH - Guido]

Hi,

I'm not sure how to accomplish this with the consolidation range approach in an ordinary pivot.
Have a look at the attached file, it is a pivot but I have used Power Query (Get & Transform in 2016) to consolidate the sheets first. All do-able via the UI.
You will need to adapt the "source" step. Via edit query (on the data ribbon, depending on your excel version, but in 2016 it is "Show Queries" to open the query pane at the right side, then a simple right click on the query will do.) you can reach it.
Look for "source" an click on the gear icon, or look in the formula bar. Change the blue part in the correct file path.
= Excel.Workbook(File.Contents("G:\Uploads\Copy of MultipleSheetPivot.xlsx"), null, true)
Click on each of the steps to see the transformations each of the steps does.


Your pivot can look like this.


Hopefully this suites your needs.