Get the running balance right once and the register takes care of itself. Convert your statement PDF into rows with the bank balance kept, then test your formula against it line by line. One-time 15 page demo, no credit card.
Upload your bank statement
Drop file here or click to upload
PDF, JPG, PNG, BMP, HEIC, TIFF, MT940, BAI2, NACHA, camt.053, CSV
Uploading...
With payments in column E, deposits in column F, and the balance in column H, the first balance cell is =F2-E2 and every cell below is =H2+F3-E3, filled down. That is the whole checkbook formula. If you ever sort the list, use =SUM($F$2:F3)-SUM($E$2:E3) instead, because it sums from the top rather than trusting the row above.
The arithmetic is simple. The errors come from how the formula is written, filled, and fed with data.
A blank row in the middle of the register makes the next balance treat the empty cell as zero, or shows a column of the same number all the way down.
Amounts pasted from a PDF often arrive as text with a dollar sign or a trailing space. Excel skips them in sums, so the balance is quietly off.
A formula that points at the row above recalculates against the wrong neighbor once the rows are sorted by payee or amount.
Keeping payments and deposits in one amount column works only if every payment is negative. One positive payment adds instead of subtracts.
New rows typed below a plain range do not get the formula, so the last few balances are blank or typed by hand.
Once the final balance disagrees with the bank, you cannot tell which of three hundred rows started the drift without a comparison column.
Convert the statement and you get the balance the bank calculated for every transaction. Put it next to your formula and the first row where they part is the row with the error.
The running balance printed on the statement comes through as its own column, ready to compare with your formula.
Amounts arrive as numbers with the sign in the right column, so SUM counts every one of them.
Debits and credits stay in separate columns, matching the two column register layout the formulas expect.
The check number column lets you match cleared checks by number instead of by amount.
Convert a run of months in one batch and test the formula across the full period.
XLSX for the register, CSV for Google Sheets, JSON if the register lives in your own tool.
From an empty column to a balance you can trust.
Put payments in column E and deposits in column F, both as positive numbers, with the opening balance as the first deposit in row 2.
In H2 enter =F2-E2. This is the opening balance carried into the register.
In H3 enter =H2+F3-E3 and fill it down, or convert the range to a table so new rows get it on their own.
Paste the converted statement balance into column I and use =ROUND(H3-I3,2) to find the first row that disagrees.
Anyone whose spreadsheet balance has to match the bank, not just look plausible.
Running an operating account in Excel and deciding what to pay based on the register balance.
Auditing a client spreadsheet that stopped agreeing with the bank months ago.
Handing a register to the next treasurer with formulas that still work after they add rows.
Rebuilding a register from statements and proving it to the cent before the close.
Assume the register has Date in A, Number in B, Payee in C, Category in D, Payment in E, Deposit in F, Cleared in G, and Balance in H, with headers in row 1 and the opening balance as a deposit in row 2.
| Cell | Formula | What it does |
|---|---|---|
| H2 | =F2-E2 | Starts the balance from the opening deposit |
| H3 and down | =H2+F3-E3 | Previous balance plus money in minus money out |
| H3 and down, blank safe | =IF(AND(E3="",F3=""),"",H2+F3-E3) | Leaves future rows empty until you type an amount |
| H3 and down, sort safe | =SUM($F$2:F3)-SUM($E$2:E3) | Sums from the top, so it never depends on a neighbor cell |
The blank safe version has one catch: if a row in the middle is left empty, the row below it refers to an empty text value and returns an error. Either keep rows filled or use the sort safe version, which handles gaps without complaint.
Turn the register into a table with Ctrl+T and name the columns. A structured version of the sort safe formula then reads like a sentence and fills itself into every new row:
=SUM(INDEX([Deposit],1):[@Deposit])-SUM(INDEX([Payment],1):[@Payment])
INDEX with 1 anchors the range at the first row of the column, and [@Deposit] ends it at the current row. Sort the table by date before you read the result, because a running balance only means something in date order.
Some registers keep a single Amount column with payments as negative numbers. The running balance is then =SUM($E$2:E3). It is shorter, but one payment typed without the minus sign adds instead of subtracts. If you use this layout, add a check such as =COUNTIFS(D:D,"<>Income",E:E,">0") to count positive amounts outside income categories.
The register balance tells you what you have. The cleared balance tells you what the bank shows. Put C in column G for every row the statement includes, then:
| Result | Formula |
|---|---|
| Cleared balance | =SUMIFS(F:F,G:G,"C")-SUMIFS(E:E,G:G,"C") |
| Outstanding checks and payments | =SUMIFS(E:E,G:G,"<>C") |
| Deposits in transit | =SUMIFS(F:F,G:G,"<>C") |
| Does it agree with the statement | =IF(ROUND(ClearedBalance-StatementEnding,2)=0,"Agrees","Check rows") |
The cleared balance should equal the ending balance on the statement. When it does not, the next section finds the row.
Convert the statement PDF and paste the bank running balance into a spare column, say I, next to the cleared rows. Then add =ROUND(H3-I3,2) in column J. The first non zero value marks the row where the register and the bank split, usually a typo, a missed fee, or a payment entered as a deposit. Fix that row and every balance below it corrects itself. If a gap divides evenly by 9, look for two swapped digits.
Amounts that came in as text will not add up. =ISTEXT(E3) shows them, and multiplying the column by 1 or using Data, Text to Columns turns them back into numbers. Converted statements from BankXLSX arrive as numbers already, which removes this problem at the source.
Google Sheets has no structured table references in a classic range, so use the sort safe range version, =SUM($F$2:F3)-SUM($E$2:E3), and fill it down. For a single formula that fills the whole column, =ARRAYFORMULA(IF(E2:E="",IF(F2:F="","",SUMIF(ROW(F2:F),"<="&ROW(F2:F),F2:F)-SUMIF(ROW(E2:E),"<="&ROW(E2:E),E2:E)),SUMIF(ROW(F2:F),"<="&ROW(F2:F),F2:F)-SUMIF(ROW(E2:E),"<="&ROW(E2:E),E2:E))) works but is slow on long registers, so most people keep the fill down version.
If you are setting up the register itself, start with the check register template in Excel. For the month end proof with both sides of the account, use the bank reconciliation template, and to get the bank balance column itself use the PDF bank statement to Excel converter.
Previous balance plus deposit minus payment. With the balance in H, deposits in F, and payments in E, enter =F2-E2 in H2 and =H2+F3-E3 in H3, then fill down.
Use a formula that sums from the top of the column, such as =SUM($F$2:F3)-SUM($E$2:E3), and sort by date before reading the balance. A formula that points at the row above gives wrong results after any other sort.
Calculation is set to manual, or the amounts are stored as text. Press F9 or set Formulas, Calculation Options to Automatic, then check the amount cells with =ISTEXT and convert any text values to numbers.
Mark cleared rows with C and use =SUMIFS(F:F,G:G,"C")-SUMIFS(E:E,G:G,"C"). The result should equal the ending balance on your bank statement.
Wrap the formula in IF, for example =IF(AND(E3="",F3=""),"",H2+F3-E3), or keep the register as an Excel table so the formula only exists on rows you add.
Convert the statement into Excel, put the bank running balance next to your register balance, and subtract the two. The first row with a non zero difference is where the error starts.
The register layout these formulas sit in.
Stack several months of statements into one sheet.
The formal month end reconciliation format.
Turn the statement PDF into spreadsheet rows.
When a spreadsheet is no longer enough.
Cancel anytime from your account settings · refund policy
Get started converting bank statements to spreadsheets.
USD
per month
per month
$288 charged today
One shared monthly allowance, up to half of it per day
| Base AI Faster | 800 pages |
| Pro AI Best accuracy | 80 pages |
Scale statement conversion across your team with automation.
USD
per month
per month
$888 charged today
One shared monthly allowance, up to half of it per day
| Base AI Faster | 1,920 pages |
| Pro AI Best accuracy | 192 pages |
Enterprise-grade bank statement conversion and controls.
USD
per month
per month
$ charged today
One shared monthly allowance, up to half of it per day
| Base AI Faster | pages |
| Pro AI Best accuracy | pages |
Try it once, free: sign up and convert up to 15 pages (5 per statement) with no credit card. The demo does not renew.