How to merge files and tables in Excel using Power Query

If you spend Mondays copying data from email attachments into a "Master" sheet, you're not working in Excel—you're doing manual labor. Power Query fixes that. Master these three core data consolidation workflows to turn hours of copy-pasting into a single Refresh click.

The case for appending, merging, and consolidating data

Stop the copy-paste madness once and for all

Cleaning data is a classic Power Query use case, but the real power comes when you stop thinking about individual tables and start thinking at the system level. Most Excel users spend a huge chunk of their week copy-pasting data or chasing down the latest CSV.

Power Query helps eliminate this manual labor through three consolidation workflows:

1. Appending tables:This is a vertical stack. You use this when you have multiple tables with the same headers—like monthly scores—and want to pile them into one long master list.

2. Relational merging:This is a horizontal join. You use this to pull related information from one table into another based on a shared data point, such as a person's name.

3. Folder consolidation:This is the ultimate automation tool. You point Excel at a folder on your drive, and it automatically grabs all the files inside, cleans them, and stacks them into a single table.

By mastering these, you turn Excel from a static calculator into a dynamic data hub.

Workflow 1: Appending tables from a single workbook

Stacking multiple sheets into one master list

Power Query's Append feature lets you unify several local tables into a single continuous dataset.

The scenario:You have a workbook with 12 separate tabs—one for each month of the year—and you need to combine them to create an annual summary report.

Download a free copy of the workbook used in Workflow 1. When you click the link, you'll find the download button in the top-right corner of your screen.

Before you start, ensure your workbook is prepared:

1. Create a blank worksheet (such as one called Summary) for the output.

2. Format each range as an Excel Table ( Ctrl+T).

3. Name each table (JanSales, FebSales, and so on).

4. Ensure headers match across tables.

Once you've checked your setup, use this method to stack them:

1. Open the Datatab and click Get Data.

2. Hover over From Other Sourcesand click Blank Query.

3. In the Power Query Editor formula bar, type = Excel.CurrentWorkbook()and press Enter. This loads all tables and named ranges.

4. Click the filter arrowon the Namecolumn, then select Text Filters > Ends With.

5. Type Salesinto the first valuefield, then click OK. This filters only your Sales tables.

6. Click the Expandicon (two arrows) in the Contentcolumn, uncheck Use original column name as prefix, and click OK.

You can remove the Namecolumn (by right-clicking the column header and clicking Remove) or keep it, depending on whether you want to track which table each row came from.

Now that all the data is visible, you're ready to set the data types and load the results:

1. Click the ABC123icon in the Dateheader and select Date.

2. Click the bottom half of the split Close & Loadbutton and click Close & Load To.

3. Select Tableand Existing Worksheet, then click cell A1on your blank Summarysheet and click OK.

4. Format the Amountcolumn as Accountingusing the drop-down menu in the Hometab.

You now have a single, unified master table on your Summary sheet.

The best part? It's hands-off. Add a new table, then click Refresh Allin the Datatab, and it's included automatically.

Set these workflows to update automatically so you never have to refresh them manually. Right-click your output table, select Query > Properties, and check the box for Refresh data when opening the file. You can also toggle Refresh every X minutesto keep your data live while you work.

Workflow 2: Connecting tables using a common column

Using relational merging to combine related but different datasets

Relational merging in Power Query allows you to pull specific data points from one table into another by finding a common link.

The scenario:You have one table ( AgeData) containing employee names, ages, and locations, and another table ( DeptData) containing those same names paired with their department and job level. These tables are on separate worksheets in the same workbook, and you want to combine them into one.

Download a free copy of the workbook used in Workflow 2. When you click the link, you'll find the download button in the top-right corner of your screen, and when you open the file, you'll see each table on its own worksheet tab.

First, you need to load both tables into Power Query by creating Connections:

1. Select a cell in your first table ( AgeData) and click Data > From Table/Range.

2. In the Power Query Editor, click the bottom half of the split Close & Loadbutton and click Close & Load To.

3. Check Only Create Connectionand click OK.

4. Repeat these steps for your second table ( DeptData), then click Queries & Connectionsin the Datatab to see both these connections in the pane on the right.

Now that both tables are loaded, you're ready to perform the merge:

1. In the Datatab, click Get Data > Combine Queries > Merge.

2. In the Mergedialog, select one table in the top drop-down and the other in the bottom drop-down.

3. Click the matching bridge column (in this case, Employee Name) in bothtable previews to highlight them.

4. Ensure the Join Kindis set to Left Outer, then click OK. This keeps all rows from the first table and adds matching data from the second.

At this point, the Power Query Editor opens. You'll see all the columns from your first table displayed normally, but the data from the second table is condensed into a single new column where every cell simply says "Table."

Now that you can see the condensed data, you're ready to unpack it:

1. Click the Expandicon in the header of the new Tablecolumn.

2. Uncheck any duplicated columns (such as Employee Name), and uncheck Use original column name as prefixto keep your column headers clean. Then, click OK.

3. Finally, click the top half of the split Close & Loadbutton to drop the merged results into a new worksheet.

You've now successfully joined two separate lists without writing a single formula.

Because this is a dynamic connection, you don't need to manually repeat the merge process. As always, if the source data changes, simply click Data > Refresh All(provided the source tables and column structure haven't changed).

Workflow 3: Merging multiple files from a folder

Consolidating external documents automatically

The From Folder connector pulls data from every file in a directory. It's ideal for recurring reports like weekly exports or monthly statements.

The scenario:Your coworkers drop weekly Excel files into a shared folder. Every workbook contains a worksheet tab named SalesData, and you need to combine them all into one summary sheet every Monday.

Download free copies of the Sales_Week_1 and Sales_Week_2 workbooks used in Workflow 3. When you click the links, you'll find the download button in the top-right corner of your screen. Then move both files to a new, dedicated folder named Weekly Reportson your computer.

Before connecting, ensure your incoming files and folders are standardized:

1. Each file must contain a worksheet with the same name (for example, SalesData).

2. Column headers should be consistent for clean consolidation.

3. All files must be saved in the same dedicated folder.

Now, you need to point Excel to your data source, using the Power Query Editor as a security gate to make sure only the correct files get through:

1. In the Datatab of a new Excel workbook, click Get Data > From File > From Folder.

2. Browse to the folder containing the reports, then click Open.

3. In the preview window, click Transform Data. You might be tempted to click Combinestraightaway, but clicking Transform Datagives you the chance to make sure your report grabs only the files you need.

4. You'll now see a list of every file in the folder. Click the filter arrowsin the Extensionor Namecolumns to uncheck anything that isn't part of the dataset.

Now you can tell Power Query how to open and stack those files:

1. Click the Combine Filesbutton in the header of the Contentcolumn.

2. In the Combine Filesdialog, select the SalesDataworksheet from the list. This tells Power Query to extract that specific worksheet from each file. Click OK.

3. In the Queriespane, select Transform Sample File. Any cleaning you do here—such as applying the correct formats to the Dateand Amountcolumns—is applied to every file in the folder.

4. Click back on the main query at the bottom of the list (usually named after your folder) to see the final stacked result. If needed, you can remove the Sourcecolumn, but I prefer to keep it visible so I can see where each row came from.

5. Click Close & Loadto load the final table into your workbook.

You've now built an automated data pipeline.

Next week, instead of opening a new file to copy-paste, just save it into that folder and click Data > Refresh All.

---

Power Query removes the need for manual consolidation. Just drop files in the folder and click Refresh All. If you only need to combine simple ranges without transformations, you can use VSTACK and HSTACK in Excel for Microsoft 365 and Excel for the web.