In Excel 2010, you can use the free PowerPivot add-in to create a report from multiple Excel workbooks or worksheets. With PowerPivot, it’s easy to connect the tables using the Primary and the Foreign key, such as ‘ProductID’ in a Sales table and a Pricing table.
In some cases though, you might want to combine the data in two Excel files, or worksheets, that have an identical structure. For example, you could have sales data for the different regions, or expense data for multiple years.
Because the tables have identical structures, you can’t use a key to connect them; instead, you would need to create one combined table from all the data. The technique shown in the following video 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. You can see more of Kirill’s work in the Contextures Blog post on Combining Data from Two Excel Files in a Pivot Table.
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, please watch this video.
Download the PowerPivot Add-In
You can download the free PowerPivot add-in from the Microsoft website: PowerPivot Download
__________