Now we want to summarise or consolidate the data that we just combined. Data Consolidation Sample You can use the above query formula as an alternative to Pivot Table to summarise data.
Also Query can be used an alternative to filter function.
The consolidate function in Excel allows an analyst to combine information from multiple workbooks into one place.
The Excel consolidate function lets you select data from its various locations and creates a table to summarizes the information for you.
Then, after you make those small changes, click the button on the worksheet, and a summary pivot table is automatically created.
If you need to combine data in multiple files, here are a couple of options, using macros provided by Excel expert, Kirill Lapin.
For example, if your sheet names are "East" and "West", change this line of code: In the code, you can also change the location where the pivot table will be added.
In this tutorial no Google Sheets script or plugin used to consolidate data from multiple Sheets.
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.
Before you use the sample code, replace the sample sheet names with the sheet names in your workbook.
With this solution, you'll end up with a normal pivot table, with none of the limitations.
However, it's a bit tedious to set up, especially if you have more than a couple of tables.