Power Pivot is a powerful data analysis add-in that will let you work with millions of records within the familiar Excel environment. In this tutorial, use it to create a pivot table from multiple files
Power Pivot for Excel 2010 is a powerful data analysis add-in that will let you work with millions of records within the familiar Excel environment. To download the free add-in, go to the Power Pivot page on the Microsoft website.
You can use Power Pivot to create a pivot table from multiple Excel workbooks or worksheets, by using the Primary and Foreign keys to join the tables. For example, there could be a 'ProductID' field in an Orders table and a PriceList table.
Occasionally you might want to create a pivot table from files where you can't use keys to join the tables. Maybe you have workbooks for different years' sales data, or budget data from several departments.
In this example, we'll combine data from two Excel files. The files have different data, but an identical structure -- sales data for the East and West regions. In this case, we can't use a key to connect the tables; instead, we want to create one combined table from all the data. The following technique allows you to import more than a million records from Excel, despite the fact that one worksheet can only contain up to 1,048,576 rows.
Thanks to Excel MVP, Kirill Lapin, for sharing this very helpful tip with us. You can see more of Kirill's work in the Contextures blog posts on Combining Data from Two Excel Files in a Pivot Table.
The key to combining data from identical files is to start by creating a workbook connection, before you launch Power Pivot.
Note: I'm using Windows XP, so the Power Pivot window has a menu bar in the screen shots below. If you're using Vista or Windows 7, you'll see a Ribbon instead.
Now that the first table has been imported, you can change its properties, to combine it with data from the second table.
SELECT [EastSales$].* FROM [EastSales$] UNION ALL SELECT * FROM `C:\_TEST\WestSales.xlsx`.[WestSales$]
Note: The SQL query string can also be edited in the Excel workbook connection window, but there's no Validate feature there:
Now that the data from the two files are combined, you can create a pivot table from the combined data.
Here's the pivot table that was created from the combined data, with columns for the East and West regions. The Report Layout is Tabular, and Number format is used, with thousands separator and zero decimals.
Download the Power Pivot from Identical Workbooks sample files
To see the steps for combining data from multiple tables in Power Pivot, please watch this Power Pivot from Identical Excel Files video tutorial.
Get the Power Pivot from Identical Workbooks sample files
Last updated: July 10, 2021 8:08 PM