Home > Pivot > Create > Multiple Tables Use Power Query to Combine Tables in Excel In Microsoft Excel, 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, there must be at least one common column, where the column names are 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 Excel tables, and on two different sheets.

Create an Excel Table

Before you combine table data with Power Query, be sure that the source data is in named Excel Tables. The technique will not work if the data is just in a normal worksheet list.

In the short video below, I show the steps for setting up a named table, and there are written instructions on the Creating an Excel Table page.

Common Column in Excel Tables

The two tables are not identical, but there are several common columns, where column headers in one table have an exact match in the other table.

There are also columns without a 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 Tables in Power Query

To combine the tables in Power Query, the first step is to create a query for each table, East and West, to get its data.

You can use the one of the following methods to get started:

• A) Power Query commands

OR

• B) Get & Transform commands on the Excel Ribbon's Data tab.

Both methods for getting started are described below.

A) 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

B) 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 from the second table.

• 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.

You can use the one of the following methods to get started with the 3rd query:

• A) Power Query commands

OR

• B) Get & Transform commands on the Excel Ribbon's Data tab.

Both methods for getting started are described below.

A) 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.

B) 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.

The Append command will:

1. get all the rows of data from one query
2. then, get all the rows of data from another query, and add (append) those rows below the first query's data

Follow these steps in the Append window:

• In the Append window, select a table from each drop down list of table names -- first, the Primary table, and in the next drop-down list, the table to append
  • 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 in Power Query

Next, the Power Query Editor window opens, showing the combined data, from the East and West regions.

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 resulting table that contains 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.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

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