It is a very common situation: each branch sends its sales report in a separate file, each sales rep keeps their own customer list or the system exports one file per month. In the end you need everything in one place to analyze it, and opening twenty files to copy and paste is slow and error-prone. In this article you will see how to merge several Excel files into one, which method to choose and how to prepare the data so the result is useful.
Two ways to merge
Before you start, be clear about the result you want:
- Merge into a single sheet (stack): all rows from all files, one below the other, in one table. This is what you need to analyze sales from every branch together.
- Merge into one workbook with several sheets: each file becomes a tab in the final workbook. Useful for having everything in one file while keeping the data separate.
Method 1: copy and paste
For two or three small files, copy and paste works. Open each file, select the data without the headers and paste it below the table in the main file.
Risks:
- Pasting the same file twice.
- Forgetting a file.
- Columns in a different order that mix up data.
- Not knowing which file each row came from.
If you use this method, add a “Source” column with the file name before pasting.
Method 2: move or copy sheets
To merge into a workbook with several tabs, right-click the sheet tab, choose Move or Copy, select the destination workbook and tick “Create a copy”. Repeat with each file. It is simple, but tedious if there are many files.
Method 3: Power Query
Power Query, built into Excel since 2016, can combine every file in a folder:
- Save all files in the same folder.
- Go to Data > Get Data > From File > From Folder.
- Select the folder and click Combine & Load.
- Choose the sample sheet or table.
Power Query stacks every file and adds a column with the source name. Best of all, if you add more files to the folder, you just click Refresh.
Drawback: it has a learning curve and is not available in every version of Excel, for example some Mac versions or the web version.
Method 4: an online tool
The quickest way, without installing anything or learning Power Query, is the Merge Excel tool on our Excel tools page:
- Upload all the XLS or XLSX files you want to join.
- Put them in order if the order matters.
- Choose whether you want everything in one sheet or each file on its own sheet.
- Download the merged file.
It is ideal for one-off merges and for people who do not use Excel every day.
How to prepare files before merging
The quality of the result depends on the preparation. Check these points:
Same columns, same order
If one file has “Date, Customer, Amount” and another “Customer, Date, Amount”, stacking them will mix the data. Standardize the order and names of the columns.
Same data format
Dates, amounts and codes must use the same format in every file. If some come as CSV and others as XLSX, convert them to the same format first.
No blank rows or totals
Each file’s total rows will end up in the middle of the merged table and double the amounts when you add them up. Remove them before merging.
A single header
When stacking, keep only the first file’s header row. If the others slip in, they will appear as records.
After merging
Once you have the merged file:
- Check the row count: it should equal the sum of the rows in all files.
- Remove duplicates if the files could overlap, for example two exports with overlapping dates. Use our duplicate remover.
- Sort by the column you use most.
- Convert to a table (Ctrl+T) to make filters and PivotTables easier.
Common use cases
- Monthly reports: joining twelve monthly files for an annual analysis.
- Sales by branch: consolidating data from several stores.
- Contact lists: bringing together several reps’ databases for a CRM.
- Surveys: combining answers collected in different files.
Frequently asked questions
Is there a limit on how many files I can merge?
The practical limit is set by Excel: one sheet holds 1,048,576 rows. If the total exceeds that, merge into several sheets or use a data analysis tool.
Are formulas kept when merging?
When stacking data, it is best to merge values. Formulas that refer to other cells can break when they change position.
How do I know which file each row came from?
Add a source column before merging, or use a method that adds it automatically, such as Power Query.
Can I merge CSV files?
Yes. Convert each CSV to Excel with the right encoding first, or merge them directly if they all share the same columns and delimiter. Afterwards, check that accents and dates display correctly.
Conclusion
Merging Excel files is a frequent task that can be done in minutes with the right method. Copy and paste works for two files, Power Query is ideal for repeating processes and an online merge tool is the fastest option from your browser. Prepare your files properly, verify the result and you will have all your data in one place. Try it on our free Excel tools page.
Try our free tools
Compare, convert, merge and clean your spreadsheets right in your browser, with nothing to install.
