Reconcile a bank statement before you import it

Published 9 October 2026 · by Tekiba

Why check the file, and not only the account

Both programs reconcile after the import. QuickBooks' reconcile page asks you to “Enter the Ending balance and Ending date from your account statement” and then to tick rows: “Continue matching until the Difference is $0.00.” Xero's Statement Balance is, in its own words, “the opening bank balance plus the sum of all bank statements imported into the bank account in Xero” — so every row a file adds, right or wrong, becomes part of it.

When they disagree, both start with the balances. Intuit's page on a reconciliation that will not balance lists three causes, one of them “There are missing or duplicate transactions in QuickBooks.” Xero says to check that the Statement Balance “matches the actual balance your bank shows, before you reconcile all the bank statement lines.” A wrong row caught in the file costs one edit; caught in the account, it has to be traced back through every statement that went in.

For a US consumer account that electronic transfers can be made to or from, Regulation E requires the periodic statement to show “The balance in the account at the beginning and at the close of the statement period” (12 CFR 1005.9(b)(4)). Totals of deposits and withdrawals, and their count, are worth comparing too where the statement prints them.

The checklist

The formulas assume dates in column A and signed amounts in column C, as in LedgerDrop's free CSV.

1. Write down the statement's own figures. The period's first and last dates, the opening and closing balances and, where printed, the totals of deposits and withdrawals and the count of each. Everything below is compared with these.
2. Check that the period follows on from the last one. Intuit: “Review the Last statement ending date. Your current account statement should start the day after this date.” The opening balance should equal the previous statement's closing balance. A gap means a statement is missing; an overlap, rows going in twice.
3. Check the first and last dates in the file. First, that every date came in as a date: =COUNT(A:A) should equal =COUNT(C:C). Microsoft's MIN function page says “Empty cells, logical values, or text in the array or reference are ignored”, so a date left as text drops out of the next check without a word; find it and correct it from the statement first. Then =MIN(A:A) and =MAX(A:A) should both fall inside the period. A date outside it is usually the year on a December–January statement (why your converted CSV shows the wrong year) or a day and month read the wrong way round (why Excel changes the dates).
4. Add the rows to the opening balance. The opening balance plus =SUM(C:C) should equal the closing balance to the cent. On a credit card, where the statement shows what you owe, subtract the sum instead. How to check a converted bank statement CSV for errors explains what each difference usually means.
5. Total money in and money out separately. =SUMIF(C:C,">0") and =-SUMIF(C:C,"<0") against the statement's totals; the minus turns the withdrawals positive, to match a total printed without a sign. This finds what the net sum cannot: a missing deposit and a missing withdrawal of the same amount leave the net where it was and put both totals out. A sign the wrong way round moves both totals too.
6. Count the rows. =COUNT(C:C) against the statement's count, or against the lines you count on its pages if it prints none. Fees, interest and anything in a separate section of the statement are rows too. Unequal counts point at a missing row, or an extra one such as page furniture that came through with a date and an amount.
7. Check against what the account already holds. Find the last date already in it. With a bank feed, the rows may be there already: Xero warns that “Duplicate bank statement lines can occur if a bank statement is manually imported into an organisation, as well as imported via a bank feed.” Decide before importing whether to cut the overlap from the file — and if you do, note which rows, because the file will no longer add up to the statement on its own.
8. Keep the figures with the file. Name it by account and period, and keep the note from the first step beside it with the three results. If the program's reconciliation shows a difference afterwards, check first that the import matches the file, then look in the account.

When one of them does not match

The balance is off and the count is right. An amount or a sign: twice one row's amount is the shape a reversed sign makes.

The count is off. A row is missing or extra, and the balance difference is usually its amount.

Everything matched here, and the program still shows a difference. Check the import first: the same count, signs and dates as the file, since a date or column can be read differently on the way in. If it matches, look in the account for rows entered by hand, from a feed, or by an earlier import. Intuit lists “Someone entered transactions into QuickBooks that haven't cleared your bank yet”; Xero, that “you might have created duplicate transactions in Xero”. QuickBooks also checks the start: the beginning balance “should match the ending balance of your last reconciliation”, and if it does not, “QuickBooks won't let you continue until you account for the difference” (Intuit, beginning balance issues).

What this page cannot tell you

These checks compare the file with the statement and with the account's last reconciliation. An error that leaves both totals and the count unchanged — an amount moved from one deposit to another, say — passes all of them, and descriptions and dates mid-period are checked only by reading. Nothing here was tested by importing, and both programs change their screens.

Questions