How to combine multiple Excel files with different columns into one fixed report, and check the totals

If you work in operations, admin, a PMO or a budget office, you probably know the routine: every month or quarter, several projects, departments or regions each send you their own file or sheet, and you roll them up into one report with a fixed layout that goes to leadership or finance.

Disclosure: I'm on the Duaer team, and this article was drafted with AI help.

The merge itself is rarely the hard part. The hard parts are that no two sheets look alike, and that once the report is done, nobody can quite explain why the total is the number it is. This article walks through a method you can reuse every period: a column mapping table, a fixed set of cleaning rules, an exceptions table that separates "included after a fix" from "excluded", a reconciliation page that ties every number back to its source, and an acceptance checklist you can copy.

The images below come from an illustrative demo workbook we built: four regional source sheets, a merged detail sheet, an exceptions sheet, a summary report and a checks page. Every number in it is randomly generated, and row 1 of every sheet says so. None of it comes from a real business or a real transaction.

Why "everyone fills in their own sheet" goes wrong

When the sheets come from different people, a predictable set of problems shows up period after period:

• Different column names and order. One region calls it "Order ID", another "Order #", a third "Order No". Dates are in column A in one sheet and column B in the next.
• Mixed date formats. One sheet has real dates. Another has text such as 2026/7/3. A third has text such as 3 Jul 2026.
• Amounts stored as text with commas. The cell shows 4,420.00, but it is a string. SUM quietly skips it, the total comes out low, and nothing warns you.
• Subtotal rows mixed into the data. Someone adds a "month total" row at the end of each month. Treat it as an order and that month is counted twice.
• Blank rows and exact duplicates. Left over from copy and paste.
• One order ID on two rows with different quantities. The dangerous one, because there is no way to tell from the sheet which row is right.

None of these is hard to fix once. The problem is fixing them every period, slightly differently each time. The goal is to turn each fix into a written rule, keep the rules in the workbook, and leave a trail anyone can follow.

Before you start

1. Pin down the report layout. The best spec is last period's report, the one that was accepted. Which columns, which breakdown (for example month by region), where the totals sit. Once the layout is fixed, each period only the data changes.
2. Keep an untouched copy of every source sheet. Never clean in place. Every check later on goes back to the original, and an edited original cannot be reconciled.
3. Count the raw data rows per sheet. Everything below the header, including blank and subtotal rows. That count is where your reconciliation starts.
4. Decide what you don't want anyone deciding for you. If an order appears twice with different quantities, do you want the person doing the merge to pick one, or to list both for you to confirm? I'd strongly suggest the second.

Step 1: Build a column mapping table

The mapping table answers one question: for every column in every source sheet, which unified field does it feed, and what was done to it? It is the instruction sheet for the job. Next period you follow it again; if someone else takes over, they follow it too.

Here is the mapping sheet from the illustrative demo:

A header you can copy (illustrative values, swap in your own column names):

Unified field	Sheet A column	Sheet B column	Sheet C column	Handling
Order ID	Order ID	Order #	Order No	Kept as is; used for duplicate checks
Date	Date	Date (text, 2026/7/3)	Order time (text, 3 Jul 2026)	Converted to real dates, shown as YYYY-MM-DD
Region	(none)	(none)	(none)	Filled in from the sheet name
Amount	Amount	Amount (text with commas)	Deal amount	Commas removed, converted to numbers; blanks go to Exceptions
(not used)			Notes	Not in the report; kept in the source

Three rules for a good mapping table:

• Every column of every sheet appears. Columns you don't need are listed as "not used", so the next person knows they were dropped on purpose.
• The Handling column states a rule, not an outcome. "Commas removed, converted to numbers" works next period too. "Fixed" doesn't.
• Fields the report needs but the sources lack say where they come from. Region from the sheet name, month from the date.

Step 2: Clean by category, with a fixed rule for each

With columns aligned, work through the problem types in this order. For each one, decide what happens to the row and where the evidence goes.

Text amounts. Strip the thousands separators and convert. A helper column in Excel or Sheets does it:

=VALUE(SUBSTITUTE(E3,",",""))

Mixed dates. Normalise text into something unambiguous before converting. DATEVALUE on a string like 3 Jul 2026 depends on your locale settings and failed in one of our test environments, so for that pattern a lookup on the month abbreviation is safer:

=DATE(VALUE(RIGHT(B3,4)),
      MATCH(MID(B3,FIND(" ",B3)+1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),
      VALUE(LEFT(B3,FIND(" ",B3)-1)))

For 2026/7/3, =DATEVALUE(SUBSTITUTE(B3,"/","-")) worked in our tests. Whatever you use, spot-check a few rows afterwards and confirm every date falls inside the reporting period.

Subtotal rows. They are not orders, so they stay out of the detail. Don't just throw them away, though. A subtotal is a number the sheet owner calculated themselves, which is exactly what you want to reconcile against later. Log each one in the exceptions table as "excluded, used as a cross-check".

Blank rows. Excluded, but logged with sheet and row number, so the gap between raw rows and merged rows can be explained line by line.

Exact duplicates. Only treat a row as a duplicate when the order ID and every other column match. Keep the first occurrence and log the rest. Building a key from the whole row is more reliable than checking the order ID alone:

=COUNTIF(K3:K2000,K3)

Here column K joins order ID, date, product, quantity and amount with a | separator; anything above 1 is a duplicate.

Same order ID, different quantity. Do not guess. "Keep the larger one" or "keep the later one" sounds reasonable, but either way you have made a business decision for someone else. Exclude both rows, list them as waiting for the owner's call, and add the confirmed row back once you hear.

Blank amounts. Two cases. If quantity and unit price are both present, you can fill in quantity × unit price and include the row, but it must be listed in the exceptions table so everyone knows the number was calculated. If quantity is missing too, leave it out and wait; don't estimate.

Step 3: An exceptions table that separates "included after fix" from "excluded"

Every row that was skipped, recalculated or put on hold goes into one exceptions table. This is the one from the illustrative demo:

Header to copy:

Source sheet	Source row	Order ID	Issue	Handling	Included	Amount involved

The "Included" column takes only Yes or No:

• Yes means the row was corrected and is in the merged detail. The exceptions table is simply telling you it was touched.
• No means the row is not in the merged result at all: blanks, subtotals, duplicates, and anything waiting for confirmation.

In this illustrative demo file, the four source sheets hold 6,324 raw data rows in total and the merged detail holds 6,303. The exceptions table has 25 rows: 21 excluded and 4 included after the amount was recalculated as quantity × unit price. The 21 excluded rows are 8 exact duplicates, 3 blank rows, 3 monthly subtotal rows, 3 rows with neither amount nor quantity, and 4 rows belonging to two order IDs that each appear twice with different quantities. Those four are all left out until the owner confirms. 6,303 plus 21 is 6,324, so every raw row is accounted for.

Step 4: A reconciliation page that explains the total

This is the page worth the most effort. It doesn't ask whether the report looks right; it asks whether a handful of equations hold. The checks page from the illustrative demo:

Three groups of checks cover most of the risk.

Per sheet, rows and amounts. One line per source sheet: raw data rows, merged rows, excluded rows from the exceptions table. Then the source amount calculated under the same rules, and the merged amount.

=IF(B3=C3+D3,"Match","Mismatch")
=IF(ROUND(F3-G3,2)=0,"Match","Mismatch")

"Calculated under the same rules" is doing real work there. Text amounts are summed after stripping commas. A sheet with subtotal rows is summed without them. A sheet with recalculated amounts adds those back. A sheet with excluded duplicates subtracts them. If the check uses different rules from the cleaning, it proves nothing.

Existing subtotals against merged totals. If a source sheet came with its own monthly subtotals, compare each one with the merged total for the same region and month. When the owner's own arithmetic agrees with your merge, you know no rows went missing or got doubled in between.

Report total against merged detail total. Build the summary report entirely from formulas that point at the merged detail (SUMIFS by month and region, for example), so the two totals should be equal by construction. When they aren't, it is usually a month or region label written slightly differently and silently left out.

In the illustrative demo, that adds up to 12 checks: 4 row checks, 4 amount checks, 3 monthly subtotal checks for the North sheet and 1 grand-total check. All 12 show "Match".

Acceptance checklist (answer yes or no to each)

• [ ] The column mapping covers every column of every source sheet; unused columns are marked "not used"
• [ ] The report's columns, breakdown and total positions match the agreed fixed layout
• [ ] Every date in the merged detail is a real date and falls inside the period
• [ ] Every amount, quantity and unit price in the merged detail is a number, not text
• [ ] Every merged row carries its source sheet and source row, and a few picked at random trace back correctly
• [ ] Per sheet: raw data rows = merged rows + excluded rows in the exceptions table
• [ ] Per sheet: source amount under the cleaning rules = merged amount
• [ ] Each existing subtotal in a source sheet (if any) = the matching merged total
• [ ] Report grand total = merged detail amount total
• [ ] Every exceptions row states the issue, the handling and Yes/No for included
• [ ] Every recalculated row is listed in the exceptions table with how it was calculated
• [ ] Rows sharing an order ID with different content were not resolved by guessing and are listed for the owner

Hand the report over only when every line is yes.

Common pitfalls

Text amounts skipped without warning. SUM ignores text and doesn't complain. Add "every amount is a number" to the checklist, and calculate the per-sheet source amount from comma-stripped values.

Subtotal rows counted as orders. They look like data rows with a blank order ID. Pull them out before merging, log them as exceptions, and add "existing subtotal = merged total" to the checklist.

Deduplicating on order ID alone. A repeated ID can be a pasted copy or two genuinely different records. Remove only exact duplicates automatically; list the rest.

Dates landing in the wrong month. Day and month get swapped, and 3 July becomes 7 March. Check that every date is inside the period, and compare row counts per month with last period.

Hand-typed numbers in the report. Typed in under deadline pressure, they break the moment next period's data arrives. Keep every report cell a formula, and say so in the checklist.

Logging what was dropped but not what was recalculated. Recalculated rows feel finished because they're in the result. Log both kinds, with the Included column filled in every time.

Handing it off

If you pass this job to a colleague or to an AI, give them three things up front: the original source sheets, last period's accepted report, and your preference for rows that need confirmation. Ask for the mapping table, the exceptions table and the reconciliation page back, then go down the checklist.