How to Combine Multiple Excel Files Into One
· admin
Combining two spreadsheets by hand is manageable. Combining twenty of them is where innocent copy and paste starts developing criminal intent.
If the files contain the same kind of data, Excel can usually do the repetitive work for you. The key is making sure the structure is consistent before you combine anything.
Before you combine the files
Keep an untouched copy of the originals. Then check that the files use the same column headings, that the columns mean the same thing, and that dates and numbers use consistent formats.
If one file calls a column Customer ID and another calls it Account Number, decide whether they genuinely contain the same information before merging them.
The quick manual method
For a couple of small files, you can copy the rows from each workbook into one master sheet. Copy the data rows rather than repeating the header every time, and check the row count afterwards.
This is fine for a small one-off job. It becomes risky when there are lots of files, thousands of rows or regular monthly exports.
Use Power Query for larger jobs
Excel’s Power Query tools can import files from a folder, apply the same transformation to each file and append the results into one table. In Excel, start from the Data tab and use the Get Data options to import from a folder.
Once Excel has identified the files, review the sample and transformations before loading the combined result. If the source files are consistent, this can turn an hour of copying into a repeatable process.
Check the combined spreadsheet
Compare the total number of source rows with the final result. Look for repeated header rows, blank records, unexpected columns, dates stored as text and duplicated records.
If duplicates are a problem, see our guide on how to remove duplicates in Excel without losing your data.
When combining files gets complicated
The difficult jobs are rarely the ones where every workbook is beautifully identical. Real files arrive with renamed columns, extra notes, different date formats, missing fields and somebody’s helpful colour coding from 2019.
That is when the job becomes less about combining files and more about cleaning and standardising data.
Can’t be faffed?
If you have a pile of Excel files that need combining, cleaning, deduplicating or restructuring, Faff It Off.
Send us the files, tell us what you need the finished spreadsheet to look like, and we’ll quote for sorting it.