Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
Books and vendor statements for several suppliers disagree, and checking each supplier separately takes too long.
Stack both sources into one table with a Source column, pivot party against source with a difference, filter to non-zero differences, and drill down to the documents behind each one.
One table with party, date, document number, amount and source columns, formatted as an Excel Table, with a pivot table using Show Values As Difference From.
A short list of parties whose balances differ, with the documents behind each difference one double-click away.
Stack both ledgers into one table with a column that says where each row came from. Build a pivot table with party on rows, source on columns and amount as the value. Add a difference. Every party where the two sources disagree stands out at once, and a double-click shows you the invoices behind the number.
Everything here works in Excel 2010 and later, including Microsoft 365.
How do I combine the books and the statements into one sheet?
Use five columns, the same for both sources:
| Column | Books row example | Statement row example |
|---|---|---|
| Party | Arun Brothers | Arun Brothers |
| Date | 28 Sep 2026 | 28 Sep 2026 |
| Document no. | AB/312 | AB/312 |
| Amount (payable, positive) | ₹1,40,000 | ₹1,40,000 |
| Source | Books | Statement |
Steps:
- Paste your creditors’ ledger lines (open items or closing balances) under the headers. Fill Source with “Books”.
- Paste each vendor’s statement lines below them. Fill Source with “Statement”.
- Make sure party names are spelt the same in both. A vendor called “Arun Bros.” in one and “Arun Brothers” in the other becomes two rows in the pivot.
- Select the data and press Ctrl+T to make it an Excel Table.
Keep the sign the same in both sources: amount payable as a positive number.
How do I build the pivot?
- Click in the table, then Insert, PivotTable, New Worksheet.
- Drag Party to Rows, Source to Columns, Amount to Values.
- Right-click a value, Show Values As, Difference From. Base field: Source. Base item: Books.
The Statement column now shows how much higher or lower each vendor’s figure is than yours. The Books column shows blank, because it is the base.
If you prefer to see both totals and a difference, leave the values as Sum and add a formula next to the pivot, such as =C5-B5, copied down.
Worked example: six vendors, ₹47,800 apart
Your books show ₹18,64,500 payable to six vendors at 30 September. Their statements total ₹19,12,300. The gap is ₹47,800.
The pivot, with a difference column added:
| Vendor | Books | Statement | Difference |
|---|---|---|---|
| Arun Brothers | ₹4,20,000 | ₹4,50,000 | ₹30,000 |
| Kiran Steel | ₹3,10,000 | ₹3,22,800 | ₹12,800 |
| Sai Packaging | ₹2,95,000 | ₹3,00,000 | ₹5,000 |
| Metro Logistics | ₹3,60,000 | ₹3,60,000 | ₹0 |
| Delta Chemicals | ₹2,04,500 | ₹2,04,500 | ₹0 |
| Vyas Traders | ₹2,75,000 | ₹2,75,000 | ₹0 |
| Total | ₹18,64,500 | ₹19,12,300 | ₹47,800 |
Three vendors explain the whole gap. Double-clicking each difference shows why:
- Arun Brothers, ₹30,000: invoice AB/318 dated 29 September is on the statement but not in your books. You have not received it yet.
- Kiran Steel, ₹12,800: you paid by NEFT on 30 September and booked it; the vendor credited it on 1 October. Payment in transit.
- Sai Packaging, ₹5,000: you raised a debit note for damaged cartons; the vendor has not booked it yet.
Check: ₹30,000 plus ₹12,800 is ₹42,800. Add ₹5,000 for ₹47,800, which is the full gap.
How do I show only the differing rows and drill down?
Only differences: pivot value filters test the summed amounts, not a Show Values As difference, so filter outside the pivot. Apply a conditional-formatting rule such as =D5<>0 to the formula difference column next to the pivot, and three vendors stand out. Or copy the pivot, paste it as values, and filter the difference column for anything other than 0.
Drill down: double-click a number in the pivot, such as Arun Brothers’ Statement total. Excel opens a new sheet listing every row behind it. Compare it with the Books rows for the same vendor to find the missing document.
Next month: paste the new data into the same Table, then right-click the pivot and choose Refresh. Because the pivot reads from the Table, new rows are included.
What to read next
A pivot finds differences but does not track who resolved them. Next, read manual detection techniques for reconciliation, then when manual reconciliation tops out. For one vendor in detail, see how to reconcile a vendor’s statement with your ledger in Excel. Related: account reconciliation software for India.