Best tool for merging spreadsheets
Merging spreadsheets means turning several files into one. In practice it is two different jobs that people describe with the same word. Stacking appends rows: twelve monthly exports with identical headers become one long table. Joining matches on a key: a list of email addresses gains a phone-number column pulled from a second file. Picking the wrong one is why a merge either loses rows or multiplies them, so decide which you have before you open a tool.
What to look for
- Matches the job you actually have — stacking rows from same-shaped files is a different operation from joining two files on a shared key like an email address.
- Survives mismatched headers. Real exports arrive with columns in a different order, an extra field added last quarter, or "Email" in one file and "email_address" in another.
- Repeats without rework if the merge is monthly. A refreshable query beats a manual copy-paste you have to redo and re-check every time.
- Leaves you able to dedupe afterwards. Merging two contact lists almost always creates duplicate people, and the merge itself will not tell you.
- Keeps personal data off third-party servers. Contact lists are personal data under the GDPR, so a tool that processes the file on your own device avoids the transfer question entirely.
The tools, compared
The right answer for stacking many same-shaped files. Data > Get Data > From File > From Folder, point it at the folder, then choose Combine & Load or Combine & Transform Data. Microsoft documents that every file needs the same schema — consistent headers, data types, and column count — but notes the columns do not have to be in the same order, because matching is done by column name. Two cautions from the same page: put the files in a dedicated folder, since everything in it and its subfolders gets included, and avoid sources holding more than one data object. The query refreshes, so next month's file drops into the folder and reloads. The cost is a genuine learning curve for anyone who has never opened the Power Query Editor.
Visit siteIMPORTRANGE(spreadsheet_url, range_string) pulls a range out of another Google spreadsheet and keeps it live, which suits a dashboard that reads from sheets other people maintain. Google documents the constraints: the destination sheet must be granted access with an Allow Access prompt, each request is capped at 10MB of received data, it cannot reference volatile functions like NOW or RAND, and heavy use across many imports can trigger throttling. It is a live link rather than a merge — good for a rolling view, weaker as a one-off consolidation. And both files have to live in Google Drive first, which means uploading anything that started as a local CSV.
Visit siteA command-line stack that is hard to beat on speed for large files: csvstack takes multiple CSVs and writes their rows to one output. The -g flag adds a grouping value per input file and -n names that column, so you can label which file each row came from; --filenames uses the filename instead. The docs carry one sharp warning — redirecting output into one of the input files makes it grow indefinitely. Rated medium because it assumes a terminal and a Python install. If that describes you, it is the fastest option here; if it does not, the setup outweighs the task.
Visit siteMicrosoft's own overview of combining sheets lists three routes below Power Query: Consolidate for summarising values, VSTACK to stack ranges dynamically with a formula, and plain copy and paste. VSTACK is the useful one — it appends ranges live, so the combined table updates when a source sheet does. Rated low for merging separate files, because all of these work sheet-to-sheet inside a workbook rather than folder-to-table, and copy-paste in particular checks nothing: a paste one row too high overwrites real data silently and leaves no record of what happened.
Visit siteHonest scope: Sigmera does not stack whole files today — there is no folder-merge tool. What it does is the column-level merge and the clean-up the moment after a merge, which is where merged files actually go wrong. Combine columns joins two fields into one with a separator you choose; the duplicate remover finds the records that arrived twice because the same person was in two of the source files; the phone and email cleaners standardise fields that two systems formatted differently. All of it runs in the browser tab with nothing uploaded, so a combined contact list stays on your machine. Rated medium deliberately: for the merge step itself, Power Query or csvkit is the better tool.
Visit siteWhy Sigmera
A merge is only half a job. The moment two contact lists become one, the same person exists twice, one file wrote phone numbers as +14155550100 and the other as (415) 555-0100, and a column of email addresses has picked up trailing spaces from whichever system exported it. None of the merge tools above fix that — Power Query stacks the rows exactly as it finds them. Sigmera is built for the pass that comes next: remove duplicates on the matching column, standardise phone numbers to E.164, lowercase and trim the email column, then convert the result to Excel if that is what the recipient wants. Because every tool runs client-side in your browser, the combined file — usually the most complete list of real people you own — never gets uploaded to a server, which keeps the whole clean-up GDPR-safe by design. Previewing the result is free and unlimited; the download counter only ticks when you export the finished file.
Sources
Frequently asked questions
- How do I merge multiple Excel files into one?
- Put every file in one dedicated folder, then in Excel choose Data > Get Data > From File > From Folder and pick Combine & Load. Power Query appends the rows into a single table. Microsoft requires the files to share a schema — same headers, data types, and column count — though the column order can differ, because matching happens by column name.
- What is the difference between stacking spreadsheets and joining them?
- Stacking appends rows: two files with the same columns become one longer file, and the row count adds up. Joining matches on a key such as an email address and adds columns: the row count stays roughly the same but each row gains fields from the second file. If your row count jumped unexpectedly after a "merge", you stacked when you meant to join.
- How do I merge spreadsheets when the columns are in a different order?
- Power Query handles it — Microsoft documents that matching is done by column name, so the order does not have to match. What it cannot absorb is a name difference: "Email" and "email_address" are treated as two separate columns and you end up with both, half-empty. Rename headers to agree before you combine, and the merge lines up.
- How do I merge CSV files without Excel?
- On a machine with Python, csvkit's csvstack command stacks any number of CSVs into one output and can tag each row with its source file. Without a terminal, the practical route is to combine the files in a spreadsheet or a browser tool, then run the clean-up on the result — CSV is plain text, so nothing about it requires Excel specifically.
- Why does my merged spreadsheet have duplicates?
- Because the same record existed in more than one source file, and stacking preserves both copies without comment. A customer who bought in January and again in March appears in both monthly exports. Deduplicate after the merge, on the field that identifies a person — usually email — rather than on the whole row, since two rows for one person are rarely byte-identical.
- Can I merge and clean spreadsheets without uploading them anywhere?
- The clean-up, yes. Sigmera's tools run entirely in your browser, so deduplicating, standardising, and converting a combined file happens on your own device with nothing sent to a server. For the merge step itself, Power Query and csvkit are also local — the option that does involve an upload is Google Sheets, since IMPORTRANGE requires both files to be in Google Drive.