How to Use Power Query to Merge Data from Multiple Sheets in Excel

When you are working with large datasets in Microsoft Excel, you often find yourself with data spread across multiple worksheets. Perhaps you receive a separate worksheet for every month of the year, or individual sales reports from different regional offices. Consolidating this data manually by copying and pasting is incredibly tedious, highly prone to errors, and must be repeated every time the data changes.

The most efficient and reliable way to combine this information is by using Power Query. Power Query is a powerful data preparation tool built directly into Excel that allows you to automate the process of importing, cleaning, and merging data. Once set up, you can refresh the merged data with a single click.

Why Use Power Query Over Manual Copying?

Relying on the standard copy-and-paste method is risky when dealing with business data. If a colleague adds a new row to one of the source sheets, your master sheet will immediately become outdated. Furthermore, if the columns in the source sheets are not in the exact same order, a simple paste could misalign critical information.

Power Query solves these problems by creating a dynamic link to your source data. It intelligently matches columns by their headers, meaning the order of columns no longer matters. Most importantly, it creates an automated workflow. When your source sheets update, your consolidated table updates automatically.

How to Merge Data from Multiple Sheets Using Power Query

To follow this tutorial, you need an Excel workbook containing two or more worksheets that share the same data structure (the same column headers).

Step 1: Format Your Data as Excel Tables

Power Query works best when your source data is formatted as an official Excel Table, rather than just a range of cells.

1. Open your first worksheet containing data.
2. Click on any cell within your data range.
3. Press Ctrl + T on your keyboard to open the Create Table dialog box.
4. Ensure the “My table has headers” box is ticked, then click OK.
5. In the Table Design tab at the top of the screen, give your table a descriptive name (for example, “Sales_Jan”).
6. Repeat this process for every worksheet you want to merge.

Step 2: Import the Tables into Power Query

Now that your data is structured, you need to load it into the Power Query Editor.

1. Navigate to the Data tab on the Excel ribbon.
2. Click on Get Data, select From Other Sources, and choose Blank Query.
3. In the formula bar at the top of the Power Query Editor, type =Excel.CurrentWorkbook() exactly as written, and press Enter. This command tells Power Query to find all the tables in your current workbook.

Step 3: Filter and Expand the Data

You should now see a list of all the tables in your workbook.

1. If there are any tables listed that you do not want to include in the merge, click the drop-down arrow next to the “Name” column and uncheck them.
2. Click the expand icon (two arrows pointing away from each other) located in the header of the “Content” column.
3. Uncheck the box that says “Use original column name as prefix”.
4. Click OK.

Power Query will now stack the data from all your selected tables on top of each other, aligning them perfectly by their column headers.

Step 4: Load the Merged Data Back to Excel

Once you are happy with how the merged data looks in the preview window, you need to load it back into your spreadsheet.

1. In the top left corner of the Power Query Editor, click the bottom half of the Close & Load button.
2. Select Close & Load To….
3. Choose Table and select New worksheet.
4. Click OK.

Excel will create a brand-new worksheet containing your fully consolidated dataset.

How to Update Your Merged Data

The true power of this workflow becomes apparent when your source data changes. If you add new rows to any of your original tables, or if you create a completely new table in a new worksheet (and name it appropriately), you do not need to repeat the entire process.

Simply navigate to your merged table, right-click anywhere inside the data, and select Refresh. Power Query will instantly re-run the entire merge process and update your master table with the latest information.