Merging Workbooks: One Sheet Each, or One Table for All

Thirty-one characters, and what happens past them

Excel refuses to open a workbook containing a sheet name longer than thirty-one characters. That limit is old and absolute, and it bites exactly when merging, because two files often carry a tab of the same name and the obvious fix — adding a suffix — pushes a long name over the edge. So the suffix is made to fit rather than appended: "Relevé mensuel des ventes par région et par produit" is fifty-one characters, becomes "Relevé mensuel des ventes par r" when truncated, and on a collision becomes "Relevé mensuel des ventes par_2" — still exactly thirty-one. The alternative, appending blindly, produces a file that will not open at all, which is a worse outcome than a shortened tab name.

Merge first, clean second

The order matters and the reason is arithmetic. Deduplicating each file separately cannot see a row that appears in two of them, which is the whole point of merging monthly exports where the last week of one file repeats the first week of the next. Merge into one sheet, then remove duplicate rows, then remove empty rows and columns — in that order the tool sees everything, and the count it reports is a count of the real problem rather than of one file's share of it.

One check is worth doing before anything else: add up the row counts of the sources and compare with the merged sheet. If the total is short, a file did not parse — and a silently skipped source is the only failure of this operation that will not announce itself.

Frequently asked questions

What if my files have different columns?

The row merge stacks them anyway and pads every row out to the widest one, so nothing is lost — but the columns will not line up, because the merge matches by position and not by header name. If your files genuinely differ, reorder the columns to match before merging, or merge them and then move the misplaced column by hand. There is no way for the tool to know that column three in one file is column four in another.

Are the formulas kept?

Appending sheets keeps the sheets as they are, formulas included — but a formula that referred to another tab will now be pointing at whatever tab has that name in the merged file, which may not be the one it meant. Merging rows reads values, so the result carries numbers rather than formulas. If the sources contain formulas that matter, convert them to values before merging and you will know exactly what travelled.

Can I go the other way and split a big workbook?

Yes — splitting writes one file per sheet, which is the natural companion to the sheet merge and the usual way to send one department its own tab without sending it everyone else's. The two are not quite inverses: split then merge gives you the sheets back, while merge then split gives you the sheets of a merged file, which for a row merge is a single sheet.