Manually copying and pasting data from dozens of separate Excel workbooks into a single master spreadsheet is a massive time sink and a common source of human error. If you find yourself consolidating weekly reports or monthly logs, Power Query is the most powerful productivity tool at your disposal. This guide will show you how to automate the merging process so you never have to copy-paste again.

Step 1: Organize Your Source Files

Before opening Excel, place all the files you wish to combine into a single, dedicated folder on your computer or cloud drive. For the most seamless experience, ensure that all files (whether .xlsx or .csv) share the same column headers and general structure.

Step 2: Connect Excel to the Folder

Open a new, blank Excel workbook which will serve as your master file. Navigate to the Data tab on the top ribbon. Click on Get Data, select From File, and then choose From Folder. Browse to the folder you prepared in Step 1 and click Open.

Step 3: Initiate the Combination Process

Excel will open a preview window listing the metadata of the files found in that folder (Name, Extension, Date Created, etc.). Instead of clicking 'Load', click the Combine dropdown button at the bottom and select Combine & Transform Data. This is the secret to cleaning your data before it enters your sheet.

Step 4: Select the Parameter Object

A 'Combine Files' dialog box will appear. Select the First File as your 'Sample File.' Under the folder icon, click on the specific Sheet or Table name you want to extract data from. Power Query will use this as a template to find and extract the same data from every other file in the folder. Click OK.

Step 5: Clean the Data in Power Query Editor

The Power Query Editor window will now open, showing your combined data. Here you can perform essential cleanup tasks: Remove Columns you don't need, Filter out null values, and most importantly, click the icon next to each column header to Change Data Types (e.g., ensuring currency is set to 'Currency' and dates are set to 'Date').

Step 6: Load the Consolidated Data

Once the data looks exactly how you want it, click the Close & Load button in the top-left corner. Excel will generate a new table in your master workbook containing every row of data from all the files in your source folder.

Step 7: Automate for Future Use

The true power of this method is its automation. Whenever you receive a new file (e.g., 'February_Report.xlsx'), simply drop it into the source folder. Open your master Excel file, go to the Data tab, and click Refresh All. Excel will automatically run the Power Query steps again, find the new file, and append its data to your master table in seconds.

💡 Pro Tip: Keep your software updated to avoid these issues in the future.