PowerPivot from Identical Excel Files

You can use the PowerPivot add-in for Excel 2010 to create a report from multiple Excel workbooks or worksheets, by joining the tables using the Primary and the Foreign key, such as ‘ProductID’ in a Sales table and a Pricing table.

In this example though, we want to combine the data in two Excel files that have 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.

At least that’s possible in theory — on my computer it imported about 1.2 million, then gave up, after whining about memory resources.

Thanks to Excel MVP, Kirill Lapin, for sharing this very helpful tip with us. You can see more of Kirill’s work in last week’s posts on Combining Data from Two Excel Files in a Pivot Table.

Create a Connection in the Workbook

The key to this technique is to start by creating a workbook connection, before you launch PowerPivot.

1. On the Excel Ribbon’s Data tab, click Connections.
2. In the Workbook Connections window, click Add
3. At the bottom of the Existing Connections window, click Browse for More.
4. Navigate to the folder where your files are located.
5. Select one of the files that you want to import — EastSales.xlsx in this example — and click Open.
6. Select a table to import, and click OK.
7. The new connection appears in the Workbook Connections window.

Combine the Data in PowerPivot

1. Close the Workbook Connections window, and on the Ribbon, click the PowerPivot tab.
2. Click PowerPivot Window, to launch the PowerPivot add-in.

Note: If you’re using Windows XP, the PowerPivot window has a menu bar. If you’re using Vista or Windows 7, you’ll see a Ribbon instead.

1. On the Table menu, click Existing Connections, or, on the Ribbon, click Design, then Existing Connections.
2. At the bottom of the Existing Connections window, under Workbook Connections, click on the connection that you added, and click Open.
3. In the Table Import Wizard, click Next, then select the table, and click Finish
4. After the data is successfully imported, click Close.

Change the SQL Statement

Now that the first table has been imported, you can change its properties, to combine it with data from the second table.

1. On the Table menu, click Table Properties, or on the Ribbon, click the Design tab, then click Table Properties.
2. At the right, from the Switch To drop down list, select Query Editor.
3. Edit the SQL statement, to create a union query, combining the two tables. In this example, the SQL statement is:

SELECT [EastSales$].* FROM [EastSales$] UNION ALL SELECT * FROM ‘C:\_TESTWestSales.xlsx’.[WestSales$]

After you change the SQL statement, click the Validate button, to verify that the statement is correct, then click Save.

Note: The SQL query string can also be edited in the Excel workbook connection window, by selecting the connection, and clicking Properties. However, there’s no Validate feature there.

Create the Pivot Table

Next, you can create a pivot table from the combined data.

1. On the Toolbar, click the Create a PivotTable button, or on the Ribbon, click the Home tab, then click PivotTable.
2. Select a location for the pivot table, and click OK.
3. Add fields to the pivot table layout, to see a summary of the 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.

Detailed Instruction and Sample Files

To see detailed instructions for this technique, with more screen shots, visit the PowerPivot from Identical Structure Excel Files page on the Contextures website.

That page also has a link for downloading the East and West sales data that I used in this example.

Watch the PowerPivot Video

To see the steps for combining data from multiple tables in PowerPivot, you can watch this PowerPivot from Identical Excel Files video tutorial.

Download the PowerPivot Add-In

You can download the free PowerPivot add-in from the Microsoft website: PowerPivot Download (Note: This link is no longer available)
__________