pulling raw data from (2) workbooks into 1 pivot table

[Macshe2012]

I have two separate workbooks each with raw data worksheets. how can I pull this raw data from two work sheets into 1 pivot table?

 

[RightMeow]

You should be able to create a link in a third workbook that pulls in data from these two workbooks. Generally, there is some relationship between these two tables that you would then create in PowerPivot to build the pivot table that you are thinking about making. I have Excel 2013, this feature of linking data is available in the "Get External Data" group in the ribbon.

 

[scottsen]

Assuming you want a single table in PowerPivot, you need some way of appending the sheets together before it gets to PowerPivot -- it supports no direct way to append data. So, a 3rd sheet as RightMeow suggests, Power Query or paste together some CSV files...

 

[Fazza]

For a pivot table from two workbooks, specify the full path in the SQL. So something like below. This uses worksheet names but you can use named ranges if not dynamic.

SELECT T1.fields
FROM `C:\first file`.[Sheet1$] T1
UNION ALL
SELECT T2.fields
FROM `C:\second file`.[Sheet1$] T2

This is if you have two tables the same. UNION ALL

If you have two different tables, then join them as required. Such as LEFT OUTER JOIN or whatever.

The manual way to set this up is to start from a new workbook, ALT-D-P, choose the external data source at the first step & follow the wizard. At the last step take the option to edit in MS Query and then via the GUI you can change the SQL.

HTH

PS BTW, AFAIK this has been available since the introduction of pivot tables with Excel 95.