How to combine multiple Excel sheets with different columns
Combine inconsistent Excel sheets by detecting table boundaries first, classifying each sheet’s role, mapping columns by meaning rather than position, normalizing types and units, adding source history, and reconciling row and amount totals. Do not append every used cell range or assume similarly named columns contain the same concept.
What changes the answer
- A worksheet can contain titles, notes, subtotals, and several tables.
- Map synonyms only after checking values, units, and grain.
- Add workbook, sheet, table, and original-row lineage before combining.
- Reconcile counts and totals by source after append or join.
From the field · Regional operations analyst
Combine monthly branch workbooks after their columns drift
Six branches send monthly workbooks. One says net_sales, another revenue_after_returns, and a third puts currency in the heading. Some sheets contain notes above the table. Appending used ranges would mix incompatible values and headings into the dataset.
Columns used in the example
branch · period · transaction_id · net_sales · currency · source_file · source_row
Follow it with your own file
- Detect each actual table and exclude title rows, notes, and subtotals.
- Create a reviewed source-to-canonical column mapping based on definitions, not column position.
- make consistent type and unit only after currency and tax treatment are explicit.
- Append source_file and source_row so every combined record remains traceable.
Check: Reconcile row counts and net-sales totals for every source separately before checking the combined total. Reject a future workbook when required fields disappear or a new unmapped heading appears.
Build the result step by step
- Inventory workbook structure: List visible and hidden sheets, candidate table ranges, headers, formulas, merged cells, blank separators, and roles such as transactions, lookup, summary, and notes.
- Confirm table grain and mapping: State what one row represents. Map columns using names, types, units, values, and relationships—not name similarity alone.
- Standardize before combining: make consistent currencies, dates, categories, and units within each source table. Add lineage columns first.
- Combine and reconcile: Append tables with the same grain and join related entities. Validate unmatched keys, row multiplication, source counts, and totals.
Detect tables before columns
A sheet is a visual canvas, not a database table. Blank rows, merged titles, subtotal bands, notes, and side-by-side tables break assumptions based on used range. Detection should examine coherent headers, density, type patterns, formulas, and boundaries.
Semantic matching needs evidence
Amount, Revenue, and Sales may be synonyms—or different measures. Client may mean an account on one sheet and a contact on another. A mapping is safer when header similarity agrees with types, values, units, and what one row represents.
What needs resolving first
Monthly or regional workbooks often have drifting headers, title rows, different units, formula totals, hidden sheets, lookup tabs, and multiple tables. A simple append can mix metadata with records or double-count totals.
Append, join, or keep separate?
| Operation | Use when | Validate |
|---|---|---|
| Append | Tables share grain and fields | Input rows equal output rows |
| Join | Tables describe related entities | Key uniqueness and row multiplication |
| Keep separate | Grain or meaning differs | Relationship documented |
Warning signs in the file
- The combined output contains header rows in the middle.
- One region is blank because its column used a synonym.
- Totals include subtotal rows.
- Columns align by position but contain different concepts.
Make the output easy to check
- Hidden sheets reviewed
- Table boundaries confirmed
- Subtotal rows excluded
- Meaning and units verified
- Lineage added
- Join number of unique values reconciled
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 Excel sheets with different column names and orders without copy and paste. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Workbook inventory · Table detection · Sheet scope · Lineage columns
One analysis-ready table traceable to the exact workbook location of every record and reconcilable to each input source.
Questions readers usually ask next
Should I combine every sheet?
No. Summary, lookup, notes, and differently grained sheets often remain separate or are joined deliberately.
How do I handle different column names?
Use semantic mapping supported by types, values, units, and grain; preserve original headers and review uncertain matches.
How do I prevent duplicate totals?
Exclude subtotal rows, add lineage, and reconcile output amounts by source against detailed records.