How to combine monthly Excel files without copying and pasting
Combine monthly Excel files by defining a canonical table, mapping each source column by meaning, adding period and source history, and reconciling every file before append. A repeatable import should reject unexpected headings or units rather than placing values in the nearest-looking column.
What changes the answer
- Specify one name, meaning, type, and unit for every output column.
- Map headings to canonical fields and list anything missing, new, or ambiguous.
- Store reporting_period, source_file, source_sheet, and source_row before append.
- Check each file first, then each month and branch, before trusting the grand total.
From the field · Finance administrator
Append 12 branch files whose headings change during the year
One branch renames Net sales to Revenue after returns, another adds Currency, and a third places a subtotal after every week. The annual report must combine transaction rows without treating titles or subtotals as business records.
Columns used in the example
transaction_id · transaction_date · branch · net_sales · currency · reporting_period · source_file
Follow it with your own file
- Identify the actual table boundary and remove only confirmed title, note, and subtotal rows.
- Map source fields to the standard column layout and document tax and return treatment.
- make consistent types and units in candidate columns while preserving raw values.
- Append only validated rows and create an exception file for unmapped sources.
Check: For every workbook, source transaction count and net-sales total must equal its accepted rows plus documented exceptions. The annual total should then equal the sum of the 12 accepted monthly controls.
Warning signs in the file
- Monthly workbooks have the same purpose but different heading names or positions.
- The master workbook contains copied subtotals and blank separator rows.
- Nobody can identify the source file for a suspicious combined row.
What needs resolving first
Manual copying makes it easy to miss a workbook, paste over a formula, include subtotal rows, or shift values when one branch adds a column. The combined file may still look orderly even though its total no longer equals its sources.
Build the result step by step
- Define the standard column layout: Specify one name, meaning, type, and unit for every output column.
- Map each source: Map headings to canonical fields and list anything missing, new, or ambiguous.
- Add lineage: Store reporting_period, source_file, source_sheet, and source_row before append.
- Reconcile in layers: Check each file first, then each month and branch, before trusting the grand total.
Treat unexpected column changes as evidence, not inconvenience
A changed heading may be harmless wording or a changed business definition. The workflow should expose it so the owner can decide once and update the mapping intentionally. source history turns the combined table from a dead end into an auditable dataset.
Where this usually goes wrong
Do not infer currency from the branch name or copy an exchange rate from the previous month. If amount units or rate dates are missing, keep local amounts separate and request the missing rule.
Choose the approach that matches the evidence and repetition
| Approach | When it works | What to verify |
|---|---|---|
| Copy and paste | A few consistent sheets once | Missed files, paste offsets, and subtotal rows |
| Power Query append | Stable folders and schemas | Column mappings, refresh errors, and types |
| Reviewed workflow | Drifting files and shared ownership | Exceptions are resolved before reuse |
Make the output easy to check
- standard column layout approved
- Every source mapped
- Lineage columns present
- Per-file controls reconciled
- Unknown headings rejected
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Combine monthly Excel workbooks into one table without copying and pasting. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Data profiling · Reviewable cleaning · Reusable workflows · Version history
One analysis-ready table that can be refreshed with the next monthly files and still explains exactly where every row and amount came from.
Questions readers usually ask next
Can Excel combine files with different column names?
Yes, after each source heading is mapped to a canonical field by meaning. Unmapped or ambiguous fields should be reviewed rather than dropped.
How do I know a monthly file was missed?
Maintain an expected-file or expected-period control and compare it with the imported source_file list before calculating the report.