How To Create A Pivot Table From Multiple Worksheets In Excel 2013

In the end import the data back to excel as a pivot table.

How to create a pivot table from multiple worksheets in excel 2013. For Excel 2016 its there on the Data tab and for other versions 2010 and 2013 you need to install the add-on. What I am looking to do is just to create a big pivot table containing and consolidating all of this data as if there was just one worksheet I need to have them in multiple worksheets. Once blank Pivot Table is inserted build the second Pivot Table as required by selecting items and dragging them between Columns Rows and Values areas in PivotTable Fields list.

The Data Model was brand new in Excel 2013 and there was no obvious way to create a relationship before you built the pivot table. In the list select PivotTable and PivotChart Wizard click Add and then click OK. We will open a New excel sheet and insert our data.

Heres how to set it up with multiple spreadsheets. Steps to Create a Pivot Table using Data from Multiple Workbooks Important. In Excel 2013 you would convert all three sheets to tables.

For this we need to use the power query so make sure you have the power query in your Excel version. Click on Close and Load To. Click on the Table name box to name the table.

All we need to do is go to File Tab and import that table into Excel. In the Import Data dialog box select Table and New worksheet options. It is good to use a new sheet option in excel.

Normally you would click OK and start working with a PivotTable. Like if I just pasted these datatables in a sequence. You can see that in total from all 4 sheets we have 592 records.