Map Extract
Free tool

Merge several Excel files into one

Choose two or more spreadsheets and combine them. Stack all their rows into one sheet with the columns lined up by header name, optionally with columns saying where each row came from, or put every file into one workbook as separate sheets.

Runs in your browser. Nothing you enter is uploaded.

Two lead lists with different column orders

One file has Business, Town and Email; the other has town, business and phone, in a different order and in lower case. Stack rows with the source lines them up by header, ignoring case, and adds Source file and Source sheet in front.

The merged sheet has every column from either file. Rows from the first file have an empty phone, and the row from the second has an empty Email, because those columns only existed in one file. The figures would show 3 merged rows, 6 columns, 2 sources and 2 columns that are not in every file.

Input
frome-leads.xlsx (sheet Leads): Business, Town, Email
bath-leads.xlsx (sheet Leads): town, business, phone
Result
Source file,Source sheet,Business,Town,Email,phone
frome-leads.xlsx,Leads,Kiln & Co Pottery,Frome,hello@kilnandco.example,
frome-leads.xlsx,Leads,Oak Lane Joinery,Frome,,
bath-leads.xlsx,Leads,Marlow Print Works,Bath,,01632 960411

Merging spreadsheets

  1. Choose the merge type

    Stack rows (matching columns), Stack rows with the source or Each file as its own sheet.

  2. Add the files

    Choose Excel or OpenDocument spreadsheets; you can add more at any time. Start again clears the list.

  3. Choose the sheets

    First sheet takes only the first sheet of each file; Every sheet takes all of them.

  4. Check the summary

    A table lists each file and sheet with its rows and columns. For stacking, you see merged rows, columns, sources, columns not in every file, and the first ten merged rows.

  5. Download

    Download merged Excel saves the workbook; CSV saves the stacked rows as a CSV.

After merging, de-duplicate

Lists from different sources overlap. The same business will appear once from a map search, once from a directory and once from your CRM export, often spelled a little differently each time. Merge first so everything is in one place, then remove exact duplicates on a column such as email or website, and use a fuzzy matcher on names for the near-misses. Keeping the source columns tells you which list each surviving row came from.

Columns are matched on their header text, so Email and E-mail are two different columns. Rename headers to agree before merging if you can; it saves cleaning afterwards.

  • Formulas are merged as their values.
  • Formatting, charts and pictures are not copied.
  • With each file as its own sheet, sheet names combine the file and sheet names, shortened to Excel's 31-character limit.

Questions people ask

How do I combine multiple Excel files into one sheet?

Choose all the files, select Stack rows (matching columns) and download the merged workbook. Rows from every file are placed in one sheet, with columns lined up by their header names even when the order differs. Add the source option to keep a column showing which file each row came from.

What if my files have different columns?

Every column found in any file is included in the merged sheet. Rows from files that lack a column simply have an empty cell there. The summary shows how many columns are not present in every file, which is a quick check that headers were named consistently.

Can I keep each file as a separate sheet instead?

Yes. Choose Each file as its own sheet and the download is one workbook with a sheet for each file and sheet you added. Sheet names are made from the file and sheet names, shortened to fit Excel's limit, with numbers added if two would clash.

Does merging remove duplicate rows?

No. Merging keeps every row so nothing is lost. Run the merged file through a deduplicator afterwards, choosing the columns that identify a duplicate, such as email or name plus postcode, and keep the source columns so you know which list each kept row came from.

Where your data goes

Each workbook is opened and combined in your browser with an open-source spreadsheet library. No file or row leaves your device.

Checked against the tool on

Top