Excel Guides

How to Compare Two Excel Sheets When the Totals Match

Two versions of the invoice register both show four invoices and $12,400. Then someone asks why one customer’s balance changed. The totals agree because three changes cancel each other out. You’ll find them by lining up invoices by ID.

Start with two copies of the same register

Save the versions side by side and confirm they cover the same dates, entity, currency, and amount definition. Then identify what makes a row unique. Use Invoice ID for an invoice-level report. If each invoice has several lines, use Invoice ID and Line ID together. Use the same data type for the keys in both sheets, especially if the identifiers contain leading zeros.

Here’s the original register. Paste it into a worksheet named Previous, starting in A1, and convert the range to an Excel table named Previous.

InvoiceID,Amount
INV-1048,3200
INV-1051,2700
INV-1054,2500
INV-1058,4000

Paste the revised register into A1 on a worksheet named Revised and convert it to a table named Revised.

InvoiceID,Amount
INV-1058,4000
INV-1048,3600
INV-1051,2700
INV-1062,2100

1. Check for duplicate or blank IDs

In each table, filter for blank invoice IDs. For duplicates, add a helper column in Previous with the formula below, then do the same in Revised, using its table name. Filter to counts above one and inspect the records. If the rows are valid invoice lines, compare them using a line-level key.

=COUNTIF(Previous[InvoiceID],[@InvoiceID])

A duplicate key matters because a merge can pair one old row with several new ones, repeating its amount. Get the row definition right before comparing any dollar values.

2. Run a full outer merge in Power Query

Load both tables with Data → From Table/Range. In Power Query, use Merge Queries as New, select Previous and Revised, and select InvoiceID on each side. Keep fuzzy matching off for exact invoice IDs. Choose Full outer. If you’re matching invoice lines, select both key columns in the same order on each side.

Expand the revised table and keep both the revised Invoice ID and Amount. You need the revised ID to identify new rows where the previous ID is blank. Check which IDs are present before you compare the amounts. A real invoice can have a zero balance.

An ID on both sides with a different amount is changed. An ID only on the previous side is removed. An ID only on the revised side is added. An ID on both sides with the same amount is unchanged. If your review also covers due dates, vendor names, or status, expand those fields and compare them too.

3. Explain the zero difference

INV-1048 increased by $400, INV-1054 dropped out with $2,500, and INV-1062 came in at $2,100. Those changes net to $0. With headers in row 1 and the sample pasted as shown, the adjustment is in Previous!B2 and Revised!B3. The removed amount is in Previous!B4 and the added amount is in Revised!B5.

On a larger file, total the signed changes and check that they reconcile to revised total minus previous total. Keep the source rows available so the person reviewing the register can see what to verify. In this example, that’s one amount change and two invoices to check against the source system. The register alone can’t tell you why they changed.

What to ask Jolene.pro

Put both versions on separate tabs in one workbook. Keep the original IDs and the columns you need to compare.

Compare Previous and Revised by InvoiceID. Show the added, removed, and changed invoices with both amounts and source cells. Flag blank or duplicate IDs separately and reconcile the changes to the net difference.

Microsoft reference on full outer joins in Power Query.

The invoice register uses synthetic data. Verify any differences against the source system before updating records.

Try the check at Jolene.pro and follow Jolene.pro on LinkedIn for more spreadsheet guides.

Leave a comment