How to Reconcile Two Totals in Excel

Reconcile your books to a bank statement: calculate the book balance, total the items not yet cleared, and find the unexplained difference.

You will learn to

• Calculate a balance from an opening amount and a register
• Total the items in transit with SUMIFS
• Isolate the unexplained difference between two totals

How it works

When two totals should agree but don’t — your books and the bank statement, a sales report and the cash in the till — reconcile them by explaining the gap. First list the timing differences: checks you wrote that haven’t cleared and deposits still in transit. SUMIFS on a "cleared?" column totals them.

The unexplained difference is book balance − other balance − timing items. If it is zero, the totals reconcile. If not, the leftover amount is your clue: a $25 gap is often a bank fee, a number divisible by 9 often points to transposed digits.

=G1+SUM(C2:C9)-G2-SUMIFS(C2:C9,D2:D9,"No")