filemixer

FileMixer / Guides / Merge Excel files

How to merge Excel files into one

You have twelve monthly exports, or a file from each store, and you need one sheet. Here are three ways to do it, and what goes wrong with each.

Before you start: check the columns

Open two of the files side by side and compare the header rows. Merging is easy when every file has the same columns in the same order. It gets harder when:

  • the columns are in a different order (“Email, Name” in one file, “Name, Email” in another),
  • a column is named differently (“E-mail” and “Email”, or “Email ” with a trailing space),
  • some files have extra columns the others don't.

Rename any columns that mean the same thing so the names match exactly. The rest depends on the method.

Method 1: copy and paste

Fine for two or three small files with identical columns.

  1. Open the first file. This will be the merged file, so save a copy under a new name.
  2. Open the next file, select the data below the header row, and copy it.
  3. In the merged file, click the first empty cell in column A under the last row, and paste.
  4. Repeat for each file. Then press Ctrl+End to jump to the last cell and check the row count.

The catch: if one file has its columns in a different order, its rows land in the wrong columns, and nothing warns you. You also can't tell afterwards which row came from which file unless you add a column for it before pasting.

Method 2: Excel's Get Data from Folder

Excel for Windows (2016 and later) and Microsoft 365 can combine every file in a folder.

  1. Put all the files in one folder, and nothing else.
  2. In Excel, go to Data › Get Data › From File › From Folder, and pick the folder.
  3. Click Combine › Combine & Load. Choose the sheet to use from the sample file and click OK.

Excel matches columns by name and adds a Source.Name column with each file name. When next month's file arrives, drop it in the folder and click Refresh. It is the best option if you repeat this every month and everyone has the Windows version of Excel. Getting it to handle a messy file, such as a different sheet name or a title row above the headers, takes some time in the query editor.

Method 3: FileMixer, in the browser

Works on any computer, including Macs and Chromebooks, with CSV and Excel files mixed together.

  1. Open the merge tool and drop in all the files at once.
  2. Leave Match columns by name on. Columns line up by name even when the order differs, and capitals or stray spaces in the names don't matter.
  3. Check Add a “Source file” column if you need to trace rows back to their file.
  4. For workbooks with several sheets, pick the sheet next to each file, or check Use every sheet of each Excel file.
  5. Check the preview and the row count, then download as Excel or CSV.

The files are read inside your browser and never uploaded, which matters for payroll, patient or customer data.

Open the merge tool

Checking the result

  • Row count. The merged file should have the sum of the rows of all the files, minus their header rows. FileMixer shows the total above the preview.
  • Blank patches. A block of empty cells in one column usually means one file named that column differently. Rename it and merge again.
  • Duplicates. If the exports overlap (a report run on the 30th and again on the 1st), remove the duplicates after merging.

Keeping each file on its own sheet

Sometimes you want one workbook with a tab per file, not one long table. In the merge tool, choose One Excel workbook, each file on its own sheet. Each tab is named after its file.