How to Merge Excel and CSV Files by Column Headers

Q: How do I merge Excel files whose columns are in a different order?

Match cells by the header name, not by the column letter. A Hours column in column C of one file and column A of another file is still Hours. Stacking by column letter silently puts names under hours.

Q: What is the difference between first-file headers and the union of headers?

First-file headers keep only the columns of the first workbook. Extra columns in later files, such as Note, are dropped. The union keeps every header that appears in any file, and leaves a cell blank when a file did not have that column.

Q: How do I stop a merged sheet from double-counting a row?

Decide the key before you add the hours. If the same employee number, name, department, and hours were exported twice, that row is a duplicate and should be kept once. In the worked example, six rows total 236 hours, and removing the repeated row leaves 196 hours.

Q: How do I split one sheet into a file per department?

Choose the department column. Each distinct value becomes one file, named from that value. In the example, East has 3 rows and 120 hours, West has 2 rows and 76 hours, and 120 plus 76 is 196, the same total as the merged sheet.

Q: Is there a free Windows app that merges and splits Excel and CSV files offline?

Yes. Excel CSV Merge & Split is a free Microsoft Store app. It merges .xlsx and .csv files by column headers, or splits one sheet into a file per column value. It detects UTF-8 and GBK, and it keeps leading zeros in IDs. The free version merges up to 3 files and 500 rows. A one-time $9.99 purchase removes both limits. Files stay on your PC.

The problem: several files, one table

HR and finance files rarely line up. One workbook has Name, EmpNo, Dept, Hours. The next CSV has Dept first and a Note column the others lack. Copying blocks under each other by column letter puts a department name into the hours column.

The useful question is: which cells share a header, which row is a duplicate, and what do the hours add up to after that row is removed? The same finished table can then be split into one file per department.

A worked example

Three files for the same week. Employee numbers are text, so a leading zero has to survive. Hours are ordinary numbers.

East.xlsx — headers in this order

Name	EmpNo	Dept	Hours
Ada	00128	East	40
Ben	00129	East	38

West.xlsx — department is the first column

Dept	EmpNo	Name	Hours
West	00130	Cara	36
West	00132	Eve	40

East-again.csv — Ada’s row was exported twice, plus Dan and a Note

Name	EmpNo	Hours	Dept	Note
Ada	00128	40	East	duplicate export
Dan	00131	42	East

Step 1: Add the files the wrong way, by position

If you paste West.xlsx under East.xlsx without looking at headers, West’s first column (Dept) lands under Name, and the hours land under Dept. Cara’s 36 hours is no longer in the Hours column. Matching by header name avoids that. Dept stays Dept even when it is column A in one file and column D in another.

Step 2: Choose which headers to keep

Two honest choices:

First file only. Keep Name, EmpNo, Dept, Hours. The Note column exists only in the CSV, so it is dropped.
Union of every header. Keep Name, EmpNo, Dept, Hours, and Note. East.xlsx and West.xlsx have no Note, so those cells are blank. The CSV’s note is kept.

Use the union when the extra column matters. Use the first file when you have already agreed a column list and later files must conform to it.

Step 3: Count rows and hours, then drop the duplicate

Six data rows before any cleanup. Blank rows are ignored and are not part of this count.

East: 40 + 38 = 78
West: 36 + 40 = 76
CSV: 40 + 42 = 82
78 + 76 + 82 = 236 hours across 6 rows

Ada, employee 00128, East, 40 hours, is in East.xlsx and again in the CSV. That is one person exported twice, not two weeks. Keep the first row and drop the copy. The employee number stays 00128, not 128.

236 − 40 = 196 hours
6 − 1 = 5 people

Name	EmpNo	Dept	Hours
Ada	00128	East	40
Ben	00129	East	38
Cara	00130	West	36
Eve	00132	West	40
Dan	00131	East	42
Total			196

40 + 38 + 36 + 40 + 42 = 196. If a later week really does contain Ada again, with different hours, that row is not this kind of duplicate. Deduplicate only on the columns that mean “this is the same line”, not on the name alone.

Step 4: Split the five rows by department

Each distinct Dept becomes a file. The file name is the cell value.

File	Rows	Hours
East.xlsx (Ada, Ben, Dan)	3	120
West.xlsx (Cara, Eve)	2	76
Total	5	196

120 + 76 = 196, the same hours you had before the split. A department value that cannot be a file name, such as a slash, is written with an underscore instead. This example does not need that: East and West are already safe names.

Tips for a merge you can audit

Match headers, not column letters. Read the first row as names. Rename a header before the merge if two files use different words for the same fact (“Emp No” and “EmpNo”).
Say which duplicate you mean. Whole row, or selected columns. Deduplicating on name alone will merge two different people who share a given name.
Keep IDs as text. 00128 and 128 are not the same employee number once a leading zero has been stripped.
Preview the row count before you export. Six in, one duplicate, five out. If the preview says something else, stop.
After a split, add the pieces back. The department files must sum to the merged total. 120 + 76 = 196 is that check.
Know the free limit. This example is 3 files and 6 rows. A free merge stops at 3 files and 500 rows, so a fourth file or row 501 needs the full version. Old .xls files, password-protected workbooks, and macros are not part of this job.

Let Excel CSV Merge & Split do the stack

Three small files are a calculator exercise. A year of department CSV exports is not. Excel CSV Merge & Split is a Windows desktop app for this exact pair of jobs:

Merge by headers. Use the first file’s headers, or the union of every header.
Split by a column. One file per value, named from that value. Characters that cannot be in a file name become underscores.
Preview. See the first 100 rows and the total row count before you export.
Blank rows and duplicates. Skip blank rows. Drop duplicates by the whole row or by columns you choose.
CSV encoding. UTF-8, with or without a BOM, and GBK/GB18030. Leading zeros in IDs and phone numbers stay.
Excel or CSV out. Numbers and real dates stay numeric in Excel so you can keep summing them.
Offline. The workbooks never leave the PC. English and Simplified Chinese.

Free download. Up to 3 files and 500 rows. $9.99 one-time removes both limits. This example fits in the free version.

Get Excel CSV Merge & Split on Microsoft Store

FAQ

How do I merge Excel files whose columns are in a different order?

Line the cells up by header name. Dept in column A and Dept in column D are the same column. Pasting by column letter is what moves the hours.

What is the difference between first-file headers and the union of headers?

First-file headers keep Name, EmpNo, Dept, and Hours, and drop Note. The union keeps Note as well, with blank cells in the files that did not have it.

How do I stop a merged sheet from double-counting a row?

Remove the repeated row before you add the hours. 236 − 40 = 196, and 6 rows become 5. Do not treat a later week with different hours as this kind of duplicate.

How do I split one sheet into a file per department?

Group by the Dept column. East has 3 rows and 120 hours, West has 2 rows and 76 hours, and 120 + 76 = 196.

Is there a free Windows app that merges and splits Excel and CSV files offline?

Yes. Excel CSV Merge & Split merges by headers or splits by a column, including GBK CSV, and keeps leading zeros. Free for up to 3 files and 500 rows. $9.99 one-time removes those limits. Files stay on your PC.