Merge Excel and CSV Files: Why Columns Shift and How to Avoid It

Contents
You combine Excel reports from several departments into one file. The same item is clearly there, but its values end up in the wrong column. The program isn't doing anything wrong. The merge is based on column order (which position the column is in), not on column names. This post covers why this happens and what to check before you merge.
Summary ① When you "merge into one sheet," data is appended by column order, not by column name. ② If you use "keep each file as a sheet," each file's original column order is preserved, so this problem doesn't happen at all. ③ If sheet names collide or contain characters that aren't allowed, numbers are added automatically or the characters are changed to spaces.
Why columns get misaligned in the merged file
For example, suppose Team A's first row (the header) is in the order "Name, Department, Amount," and Team B's file is in the order "Department, Name, Amount." If you merge the two files with "append all rows to one sheet," Team B's "Department" values stack up in the "Name" column position from Team A.
This happens because the merge tool doesn't read the header text and match items with the same name. It treats each row like an array and joins the first cell to the first cell, the second cell to the second cell, and so on. The same thing happens when you copy and paste sheets directly in Excel. Paste works by cell position. It doesn't compare header names.
If you choose "keep each file as a sheet," this problem doesn't happen at all. Each file stays on its own sheet with its original column order. The tradeoff is that the data isn't combined into one table. It's split across sheets.
If you choose "merge into one sheet" for a file that contains multiple sheets, every sheet in that file is appended along with the rest. There is no way to select just one sheet. If a file has sheets you don't need, delete them before you merge or split them into a separate file.
What to check before you merge
You can check without a tool. Open each file you plan to merge and compare the column order in the first row (the header row) side by side. If the order is different, move columns in one file to match the other before you merge. That way values won't shift.
To merge sheets with Excel's own features while keeping them as sheets, right-click the sheet tab, select "Move or Copy," and choose the target workbook. This method doesn't touch column order, so it's safe. The downside is that you have to repeat the process for every file.
Check it with our free tool, Excel/CSV Merger. Upload several files at once, choose a merge mode, and you're done. Whether you keep sheets or merge into one sheet, there's no repetitive work.
Free · No sign-up · Runs entirely in your browser (no server upload)

Common issues
| Situation | What actually happens |
|---|---|
| Multiple files with the same sheet name | A number (_2, _3) is added to the end automatically to tell them apart |
Characters such as \ / ? * [ ] : in a sheet name | These are characters Excel doesn't allow in sheet names, so they're replaced with spaces automatically |
| A file name longer than 31 characters | It's truncated to fit Excel's 31-character sheet name limit |
| You choose "merge into one sheet" for a file with multiple sheets | Every sheet in that file is merged in (you can't select only some) |
| Merge result screen | It shows only the sheet count and file size. It doesn't preview cell contents |
The bottom line
Here's the key point. Columns are merged by position, not by name. So the only way to prevent shifting is to match the header order before you merge. If that's hard to do or you want to preserve the original layout, "keep each file as a sheet" is safer. Sheet name conflicts and special characters are handled automatically, so you don't need to worry about them.
The merge result screen shows only the sheet count and file size. It doesn't preview cell contents. So you'll need to open the downloaded file once to check that the columns line up.
Free · No sign-up · Runs entirely in your browser (no server upload)
Frequently asked questions
Can I tell how many sheets the merged file will have before I merge?
With "keep each file as a sheet," you can estimate it by adding the number of files you added and the number of sheets in each file. The exact count appears as the "Result sheets" number on the result screen after merging.
Can I mix CSV and Excel (xlsx) files in one merge?
Yes. If the extension is .xlsx, .xls, or .csv, you can add them together in any order and merge them into one Excel file. The column order issue described above still applies, no matter the file format.
Does changing the file order in the list change the result?
With "keep each file as a sheet," the list order sets the order in which the sheets are listed. With "merge into one sheet," rows stack from top to bottom in list order, so data from the file you put first ends up at the top.
The sheet name changed automatically. Does that change the original data too?
No. Only the sheet name is cleaned up according to the rules. The data inside the sheet stays the same.
This article was adapted from our Korean original. Read the Korean original