PowerPivot from Identical Structure Excel Files
PowerPivot 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 PowerPivot page on the Microsoft website.
You can use PowerPivot 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.Read more...