It is Friday afternoon in budget week. Ten department heads got the same template three weeks ago, and now ten workbooks are back in your inbox: Budget_FY27_Sales_v3_FINAL(2).xlsx and its cousins. Two of them no longer match the template, because marketing inserted a column for a campaign split and operations renamed the input sheet. Before Monday's review you need to merge the Excel files into one master, ideally without copy-pasting thirty rows at a time. Excel has three built-in ways to combine files, and one of them handles this at scale. Below are all three, plus the step most teams eventually take: setting up the collection so there is nothing left to merge.
Merge Excel files with Power Query, the reliable way
Power Query is the right tool the moment you have more than two or three returned files with the same layout. It reads every workbook in a folder, appends them into one table, and can be refreshed when late files trickle in.
- Move all returned workbooks into one folder, and nothing else into it.
- In a new workbook, go to Data > Get Data > From File > From Folder and pick that folder.
- Excel lists the files it found. Choose Combine & Transform Data.
- In the Combine Files dialog, Excel opens one workbook as a sample and asks which sheet or table to take from each file. Pick the input sheet and confirm.
- Power Query appends every file's rows into one table and adds a Source.Name column, so each row remembers which file it came from. Adjust whatever you like in the editor, then choose Close & Load.
When the eleventh file arrives on Monday, drop it into the folder and press Refresh All on the Data tab. That refresh is what makes Power Query worth the setup: the merge becomes a repeatable query instead of a one-off paste job.
The precondition is strict, though. Every file must have the same structure. The sheet or table you selected has to exist under the same name in every workbook, with the same columns in it. The renamed sheet from operations makes the query error on that file, and marketing's extra column shows up as a new column with blanks for everyone else. Power Query at least fails loudly rather than silently, which is the better kind of failure, but you still end up fixing the files by hand.
Two simpler ways to combine Excel files
Copy whole sheets. For a handful of files you want side by side in one workbook rather than stacked into one table: open both workbooks, right-click the sheet tab, choose Move or Copy, pick the destination workbook under "To book", tick Create a copy, and confirm. Repeat per file. Formulas that reference other sheets in the source workbook keep pointing at it as external links, so check that a copied sheet calculates on its own before you rely on it.
Consolidate the numbers. If you need aggregated values rather than row-level data, the Consolidate command combines same-shaped ranges from several sheets or workbooks. Select a target cell, go to Data > Consolidate in the Data Tools group, pick a function such as Sum, add each source range as a reference, and tick Top row or Left column if the ranges should be matched by their labels instead of by position. Consolidate produces values, not a live model; tick Create links to source data if the result should update when a source workbook changes.
Why merging after the fact keeps going wrong
All three methods rest on the same assumption: that ten people each held a full copy of your template for three weeks and changed nothing except the cells you meant them to change. Somewhere, every round, that assumption fails. Someone inserts a helper column. Someone renames a sheet to make it "clearer". Someone replies to an older email and returns last quarter's template. None of this is carelessness. A workbook on someone else's disk is simply out of your control, and a merge is where you discover everything that happened to it.
Merging late also means validating late. A number typed into the wrong unit on day one surfaces at merge time on day twenty, when the person who typed it has moved on and the deadline has not. And the copies themselves carry risk that has nothing to do with merging: each file traveled with everything in it, including whatever a hidden sheet was supposed to keep out of sight.
The alternative: collect the numbers, not the files
Teams that stop dreading budget week usually stop emailing copies. Instead, they share one input form driven by the master workbook and let submissions land in it directly. With granular sharing in airrange you select the input range in your workbook and publish it as a small web app. Department heads open a link in the browser and see exactly the cells you shared. Nobody installs anything, and nobody receives the file, so nobody can break the structure of something they never had.
The merge itself changes character. Submissions arrive in the Microsoft-validated airrange add-in inside Excel, where you review each change and accept it with a click; the add-in then writes the accepted values into the workbook. Every input element defines its target address in the receiving workbook (sheet, column, and row), each either fixed or taken from a variable, so a formula can route a submission to the right sheet based on the department chosen in the app. One thing to plan for: appending each submission to the next free row is not automatic. If you want submissions stacked as rows, drive the target row from a variable your workbook calculates, such as the current row count plus one. Through all of this the master file stays where it is; the add-in works with local files as well as Microsoft 365 ones, in any Excel version that can run add-ins from the Microsoft store.
When responses are many rows rather than a filled-in block, skip the per-cell routing and collect into a table instead: the submit button runs an Add to Table flow, and every submission is appended as a row to an App Records table in your workspace, up to 50,000 rows per table, ready to review, export, or connect back into a workbook. The Excel form builder page shows that setup, and our post on Excel data entry forms covers when a form beats a shared file entirely.
Picking the right merge method
For a one-off with two or three well-behaved files, copy the sheets across and be done. For a recurring round built on identical templates, spend fifteen minutes on a Power Query folder merge and refresh it every cycle. If totals are all you need, Consolidate covers it. And when the merge itself has become the job, when every quarter starts with chasing files and repairing layouts, move the collection out of email: share the input range once, let each number arrive at its target address, and spend Friday afternoon reviewing the plan instead of reassembling it.