Then, use the result as the pivot table's source data.To see an example, download the Union Query sample files.We’ve created a step by step guide to help you create your own consolidation by combining similarly organized data across multiple sheets and books.The screenshots below will help you see an example of how to use the Excel consolidate function: Step 1: Open all files (workbooks) that contain the data you want to consolidate.In the sample file, the Table Destination is set for the active sheet, in range A1.Then, after you make those small changes, click the button on the worksheet, and a summary pivot table is automatically created.There are many reasons a financial analyst may want to use this function.
Read the details in blog post, Create a Pivot Table from Multiple Files. Download the sample pivot table tutorial file To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in.
Unzip the folder, and keep all the files in the same folder.
When you open the file, enable macros to run the code.
However, it's a bit tedious to set up, especially if you have more than a couple of tables.
You can read more about MS Queries here: Instead of manually setting up a union query, you can use the code in a sample file from Excel MVPs, Kirill Lapin (KL), with amendments by Hector Miguel Orozco Diaz.