Home > Pivot > Create > Multiple Tables Use Power Query to Combine Tables 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

Pivot Table from Data on 2 Sheets

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.

Data in Two Tables

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.

• In the East data (blue), there is a Manager column that is not found in the other table
• In the West data (black), there is a Tax column that is not found in the other table.

Add the Tables

The first step is to create a query for each table, East and West, to get its data.

Power Query Commands

If you're using commands from the Excel Power Query tab, follow these steps:

• Select a cell in the East table.
• On the Ribbon, click the Power Query tab
• Next, click the From Table command

Get & Transform Commands

If you're NOT using the Power Query tab commands, follow these steps for the Get & Transform commands:

• On the East worksheet, select any cell in the East table.
• On the Ribbon, click the Data tab
• Next, click the From Table/Range button

Power Query Window

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.

• No changes are needed in this table or its data
• Click the Close & Load button, to return to the Excel workbook

New Worksheet Added

A new worksheet is inserted, with the data from the East table.

• At the right side of the Excel window, you can see a list of Workbook Queries
• There is a Refresh button at the right side of each query in the list.

Add the West Table

Next, follow the steps below, to get the West table data.

• On the West worksheet, select any cell in the West table.
• On the Ribbon, click the Data tab, then click the From Table/Range button
  • OR, click the Power Query tab, then click the From Table command

In the Power Query Editor window that opens, you can see the data from the West region.

• No changes are needed in this table or its data
• Click the Close & Load button

Combine the Table Data

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.

Power Query Commands

If you're using Power Query:

• Click the Power Query tab on the Ribbon
• Click the Append command, in the Combine group.

Get & Transform Commands

If you're NOT using Power Query:

• Select any cell in the workbook
• Click the Data tab on the Ribbon
• Click the Get Data arrow, then click Combine Queries, and click Append.

Append Window

The Append window opens, where you can choose which tables to merge.

• In the Append window, select a table from each drop down list of table names
  • In this example, it does not matter which table is selected as the primary table
• After both table names are selected, click OK.

Combined Data

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.

• No changes need to be made to the data
• Click the Close & Load button.

New Worksheet

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.

Create a Pivot 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.

• Select a cell in the combined data table
• Then, on the Insert tab of the Excel Ribbon, click Pivot Table

The PivotTable from Table or Range dialog box opens.

• Choose a location for the pivot table
• Click OK, to create the pivot table

Add Pivot Fields to Layout

An empty pivot table layout appears in the location that you selected

• Select any cell in the blank pivot table layout
• Add check marks to fields in the PivotTable Field List, to add those fields to the pivot table layout

In the screen shot below:

• Region and Item fields were added to the Row labels area
• Units field was added to the Values area, as Sum of Units

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.

Combine 3 Excel Tables

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 File

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.

More Pivot Table Resources

Pivot Table Blog

Unpivot, Power Query

Pivot Table Article Index

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals