Use Power Query to combine data from different sheets in a workbook, or from different Excel files, if those tables have some column headings with identical names. Then, you can create a pivot table from the combined data
In older versions of Excel, you could use the Multiple Consolidation feature, to create a pivot table from data on different sheets.
Use Power Query to combine data from different sheets in a workbook, or from different Excel files, if those tables have some column headings with identical names.
With the combined data, you will be able to create a normal pivot table, showing the data from both original tables.
Next, you will use Power Query to combine the data from the tables.
A new sheet appears, with the combined data.
Next, you can use the combined data to create a pivot table
To see the steps for combining tables with Power Query, and creating a pivot table from the combined data, watch this short video tutorial.
To see the steps for combining 3 tables with Power Query, watch this short video tutorial by Mike "ExcelIsFun" Girvin. If you have more than 3 tables, use the same technique to add the extra tables.
Download the sample Combine Tables With Power Query file
Power Query Training: Expand your Excel skills -- learn Power Query at your own pace, in this online course from experts Ken Puls and Miguel Escobar. The course starts with the Power Query basics, and gradually builds up to advanced techniques. There are 15 modules, broken into 55 videos (over 12 hours of content). See all the course details and sign up now.
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter.
Last updated: January 12, 2018 10:28 AM
Contextures RSS Feed