You can use Power Query (or Get & Transform Data) to combine the data from 2 or more tables, if those tables have some matching column headings with identical names. Then, create a pivot table from the merged data
NOTE: In older versions of Excel, use the Multiple Consolidation feature to merge two or more tables
To create a pivot table from table data on two or more worksheets, use Excel's Power Query (also called Get & Transform Data). With a few simple steps, you can combine all the data, as long as those tables have one or more column headings with identical names.
Watch this video see the steps, and the written instructions are below the video.
To combine the data from two related tables with this technique, at least one column heading must be an exact match in the two tables.
In this example, the Excel workbook has sales data from two regions - East and West. The data is in two separate tables, and on different sheets.
The two tables are not identical, but most column headers have an exact match in the other table.
The first step is to create a query for each table, East and West, to get its data.
If you're using commands from the Excel Power Query tab, follow these steps:
If you're NOT using the Power Query tab commands, follow these steps for the Get & Transform commands:
After clicking the button, on either the Power Query tab or the Data tab, the Power Query Editor window opens.
In that window, shown in the screen shot below, you can see the data from the East region.
A new worksheet is inserted, with the data from the East table.
Next, follow the steps below, to get the West table data.
In the Power Query Editor window that opens, you can see the data from the West region.
Next, you will create a third query. This query will combine the data from the two new tables, with separate East and West data, into one merged table.
If you're using Power Query:
If you're NOT using Power Query:
The Append window opens, where you can choose which tables to merge.
Next, the Power Query Editor window opens, showing the combined data.
For columns that are unique to one of the tables, rows from the other table will show null. You can see an example of that in the Tax column and Manager column in the screenshot below.
A new worksheet appears, with the combined data from the merged tables.
Note: Data that showed "null" in the Power Query Editor are blank cells in the worksheet table.
Now that the two tables are appended, you can use the combined data to create a pivot table, that shows the East and West sales records.
The PivotTable from Table or Range dialog box opens.
An empty pivot table layout appears in the location that you selected
In the screen shot below:
This pivot table gives you a summary of the datasets in the two original tables.
You can see the East and West data listed separately, or you could remove the Region field, to see combined totals for each sales item.
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.
Get the sample Combine Tables With Power Query file. The zipped Excel file is in xlsx format, and does not contain any macros. There are two tables, with East and West data. The workbook does not contain any connections - follow the steps on this page, to create the queries that combine the table data.
Last updated: March 5, 2022 11:32 AM