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

Continue reading PowerPivot from Identical Excel Files