The fastest way depends on how many files you have and whether they're identical in structure

If you have two or three Excel files with the same columns, copy and paste the data rows into a single file — that takes five minutes. If you have dozens of files or they have different layouts, use Excel's built-in consolidation tools or a free tool like Python or Power Query to automate the work. The method you choose depends on file count, whether the files match in structure, and whether you want to repeat the process regularly.

This guide covers the manual approaches first (for small jobs), then the automated methods (for larger ones). All of these work in Excel on Windows and Mac, and in Google Sheets for the simplest tasks.

Key Takeaways

  • Copy and paste works fastest for two to five files with identical column layouts — select all data rows in each file and paste them into a master file.
  • Excel's Consolidate feature (Data menu) combines data from multiple files automatically if they have matching row and column labels.
  • Power Query, built into Excel 2016 and later, can merge dozens of files from a single folder without opening each one individually.
  • Google Sheets can import data from multiple spreadsheets using the IMPORTRANGE function, though it works best for files you own and share within your account.
  • Python and command-line tools are free options for merging hundreds of files, but require learning basic coding or following a step-by-step tutorial.

Copy and paste for small jobs with matching structures

Open the first Excel file and select all the data rows (not the header). Click the first cell in the data, then hold Shift and click the last cell to select the range. Press Ctrl+C (or Cmd+C on Mac) to copy.

Open a new blank Excel file. Click the first cell below the header row and press Ctrl+V to paste. Repeat this for each additional file — open it, copy the data rows, switch to your master file, and paste below the previous data. When all files are merged, save the master file with a new name so you don't overwrite the originals.

This method works only if every file has the same columns in the same order. If one file has an extra column or the columns are in a different sequence, the data will paste in the wrong columns and you'll have to fix it manually.

Use Excel's Consolidate feature for files with labeled rows and columns

The Consolidate tool is built into Excel and works when your files have the same structure — for example, monthly sales reports where each file has product names in column A and months across the top row. Open a new blank file where you want the merged data to appear.

Click the Data menu, then find Consolidate (the exact location varies by Excel version — in newer versions it's under Data > Consolidate). A dialog box opens. Click the folder icon next to "Reference" and navigate to the first file you want to merge. Select the data range (including headers), then click Add. Repeat for each additional file. Choose whether you want Excel to sum, average, or count the values, then click OK.

Consolidate is most useful when you're combining numbers from multiple files — for instance, merging quarterly revenue reports into an annual total. It's slower than copy-paste for simple row-by-row merging, but it handles calculations automatically.

Power Query for merging dozens of files from one folder

Power Query is a free tool built into Excel 2016 and later (and available as a free add-in for Excel 2010 and 2013). It can merge all Excel files in a folder without opening each one individually. This is the fastest method for large jobs.

Place all the files you want to merge into a single folder. Open Excel and click the Data menu, then select Get Data > From File > From Folder. Navigate to the folder containing your files and click OK. A preview window appears showing all the files in that folder. Click the Combine button, then select the sheet name you want to merge from each file. Power Query loads all the data into a single table.

From here, you can clean up the data (remove columns you don't need, rename headers) and then click Close & Load to send the merged data to a new Excel sheet. Power Query remembers these steps, so if you add new files to the folder and repeat the process, it will merge them the same way automatically.

Google Sheets for files you already store in Google Drive

If your files are already in Google Sheets or you're willing to upload them, you can merge them using the IMPORTRANGE function. Open a new Google Sheet and click a cell where you want the first file's data to appear. Type the formula =IMPORTRANGE("URL", "Sheet1!A:Z"), replacing URL with the sharing link of the first file and Sheet1 with the sheet name. Press Enter.

Google Sheets will ask for permission to access the file. Click Allow. The data from that file appears in your sheet. Repeat the formula in a different area of the sheet for each additional file, adjusting the row numbers so the data doesn't overlap.

This method works best for a handful of files. For dozens of files, it becomes slow and difficult to manage. Also, all files must be shared with your Google account — you can't merge files from other people's accounts unless they've given you access.

Python and command-line tools for hundreds of files

If you have a large number of files to merge regularly, a simple Python script can do the work in seconds. You don't need to be a programmer — you can copy a ready-made script from a tutorial, change a few file names, and run it.

The most common approach uses the pandas library, which is free. Install Python and pandas (both free), then create a text file with a few lines of code that tells Python to read all Excel files in a folder and combine them. Run the script and it outputs a single merged file.

Learning to do this takes an hour or two if you follow a step-by-step tutorial. The payoff is that you can merge hundreds of files in seconds, and you can automate the process to run on a schedule. If you only need to merge files once or twice, this approach is overkill — stick with Power Query or copy-paste instead.

What to do if the files have different column layouts

If your files don't have matching columns — for example, one file has "Customer Name" and another has "First Name" and "Last Name" — you'll need to standardize them first. Open each file and rename or reorganize the columns so they match across all files. This is manual work, but it only takes a few minutes for most jobs.

Alternatively, if you're using Power Query, you can use the Merge Queries feature to combine files based on a common column (like Customer ID) rather than just stacking rows on top of each other. This is useful when you want to add information from one file to another — for instance, combining a sales file with a customer details file based on customer ID.

Frequently Asked Questions

Can I merge Excel files without opening them?

Yes, with Power Query or Python. Power Query lets you select a folder and merge all files in it without opening each one. Python scripts can do the same thing and are free, but require a bit of setup. Copy-paste and Consolidate both require you to open each file.

What if the files are on different sheets within the same workbook?

Use the Consolidate tool or copy-paste each sheet's data into a master sheet. If you want to combine data from multiple sheets based on a common column, use Power Query's Merge Queries feature instead of stacking rows.

Will merging files remove duplicates automatically?

No. After merging, you'll need to remove duplicates manually. In Excel, select all the data, click the Data menu, find Remove Duplicates, and choose which columns to check. Google Sheets has a similar feature under Data > Remove duplicates.

Can I merge files that are in different formats, like .xlsx and .csv?

Yes. Power Query and Python both handle multiple file formats. Copy-paste works too — just open the .csv file in Excel first, then copy the data as you normally would. Consolidate works with .xlsx files only.

How do I automate this process so it runs every week?

Power Query can refresh automatically if the files are in a shared folder and you set up a scheduled refresh. Python scripts can be scheduled using Windows Task Scheduler (Windows) or cron (Mac and Linux). For simple recurring jobs, Power Query is easier; for complex ones, Python gives you more control.