How to Combine Excel Files: Step-by-Step Guide

Merging multiple Excel files into one spreadsheet

Combining Excel files saves you hours of manual work by bringing scattered data into a single, organized spreadsheet. Whether you want to merge Excel files for analysis, reporting, or record keeping, you can do this easily using built-in Excel features, Power Query, or VBA automation. Here’s exactly how to combine multiple Excel files into one, with clear steps for each approach.

Why Combine Excel Files?

If you work with data spread across different Excel files, merging them lets you analyze information at a glance and eliminates the risk of missing updates. For example, you might receive monthly sales files from different teams, or need to consolidate survey results from multiple sources. When you combine Excel files, you reduce manual copying, minimize errors, and create a single source of truth for your project or organization.

Bringing all your data together also speeds up processes like filtering, charting, and building pivot tables. You won’t need to switch between multiple spreadsheets just to get the complete picture. Instead, your combined file becomes the master record, making it easier to update, share, or back up your work.

Use Excel’s Copy and Paste

The fastest way to merge Excel files is by copying and pasting data. This method works best for small datasets or when you need a quick solution without advanced tools.

  • Open both files: Start by opening the Excel files you want to combine.
  • Select your data: Highlight the entire data range in one file (use Ctrl + Shift + End to select to the last cell).
  • Copy and paste: Press Ctrl + C to copy. Go to the destination file, select the cell where you want the data to begin, and press Ctrl + V to paste.
  • Repeat if needed: If you have more files, repeat these steps, pasting each dataset below or beside the previous one—depending on how you want to combine the data.

Make sure your column headers match, and double-check for duplicate header rows or blank lines. If you want to combine multiple Excel files into one sheet, always paste data directly under the last row to keep everything together. This manual method is useful for occasional merges but can be time-consuming for larger or regularly updated datasets.

Merge Excel Files with Power Query

Power Query is Excel’s built-in tool for merging and transforming data efficiently, even when dealing with many files or frequent updates. It can merge Excel files from a folder and even lets you automate refreshes as new files are added.

Step-by-Step Power Query Method

  1. Place all files in one folder: Save every file you want to merge in a single folder. Make sure each file has the same table structure and column headers for best results.
  2. Open a new Excel workbook: Go to the Data tab and select Get Data > From File > From Folder.
  3. Choose your folder: Browse to your folder and confirm. Power Query will list all files inside.
  4. Combine and load: Click Combine, then Combine & Load. Power Query will preview the data and merge all files into a single table in your new workbook.
  5. Refresh for updates: If you add new files later, right-click the query table and select Refresh to update the merged data automatically.

This method is ideal if you want to merge multiple Excel files with consistent formatting or need to regularly update your combined data. Power Query can also clean, transform, and filter data as you merge, saving even more time.

Automate Merging with VBA

If you frequently merge Excel files or work with complex datasets, automating the process with VBA (Visual Basic for Applications) can save hours. VBA scripts let you batch import, organize, and combine multiple Excel files into one master file with a single click.

Simple VBA Script Example

Here’s a basic VBA script to merge all Excel files in a folder into one worksheet:

  1. Press Alt + F11 to open the VBA editor in Excel.
  2. Go to Insert > Module and paste the following code:
Sub CombineFiles()
  Dim wb As Workbook, ws As Worksheet, myPath As String, fName As String, lastRow As Long
  Set ws = ThisWorkbook.Sheets(1)
  myPath = "C:\YourFolderPath\" ' Change this to your folder
  fName = Dir(myPath & "*.xlsx")

  Do While fName <> ""
    Set wb = Workbooks.Open(myPath & fName)
    wb.Sheets(1).UsedRange.Copy ws.Cells(ws.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
    wb.Close False
    fName = Dir
  Loop
End Sub
  1. Replace C:\YourFolderPath\ with your folder’s path.
  2. Run the macro (F5). All data from each file in the folder will be copied into your master sheet.

This script can be adjusted for different file structures or multiple sheets. Always back up your files before running VBA scripts, and test with a small set first to confirm results.