How to calculate and verify a running bank balance in Excel
A running balance in Excel gives each transaction a calculated closing amount that you can compare with the bank's printed balance. Begin with the opening amount, add the first signed movement, and carry the result into the next row. Keep an independently copied printed balance beside the calculation. A difference appearing on one particular line tells you where to start looking, even when a later error brings the final total back into agreement. The fictional example below uses one account, one currency and original posting order.
In this guide
Choose the balance you want to reproduce
Read the statement header carefully. A bank may show an opening ledger balance, an available balance, and a balance adjusted for pending authorizations. Select the balance associated with the posted transactions in the section you are extracting. Combining pending card activity with the posted transaction list can make a technically correct addition fail to reproduce either figure. Record the account label, period and balance type alongside the opening amount so an independent reviewer can repeat your calculation.
For a current or checking account, deposits commonly increase that balance. For a credit card, a purchase can increase the displayed amount owed. Verify the direction by comparing a transaction against two adjacent printed balances before applying a running formula to the whole file. Use the actual settlement currency and the smallest unit printed on the statement. An example rounded to two decimal places suits a two-decimal statement. More decimal places, or none at all, may be appropriate for other records.
Establish the opening value once
Suppose the statement says the opening balance is 2,400.00 and the first posted transaction is a payment of 95.50. Put 2,400.00 in H1 as the opening control and treat the payment as negative 95.50. If the PDF extraction also returned an “Opening balance” row, keep it in the raw sheet but exclude it from the movement table. Adding it as a transaction would count the starting amount twice. The table should begin with the first actual posted transaction.
Set up the Excel running balance formula
Use A for source position, B for description, C for signed movement, D for printed balance, E for calculated balance and F for difference. With headers in row one, enter =$H$1+SUM($C$2:C2) into E2. Fill it down so the first end of the range stays fixed while the second expands by one row each time. The formula recalculates each balance from the same opening amount and all signed movements up to the current position. Verify that the movement column contains numbers first because SUM can ignore text.
For a very large sheet you can use a previous-row calculation. Enter opening amount plus first movement in E2 and previous calculated balance plus current movement in E3, then fill E3 down. This version is easy to explain to a colleague reading one line at a time. It also means a missing formula or accidental hard-coded value affects every result below that point. Microsoft's running balance example illustrates the carry-forward method. Check formula continuity before trusting the last cell alone.
Compare calculated values with independent printed balances
Enter =IF(ISNUMBER(D2),ROUND(E2-D2,2),"") in F2 for this two-decimal example. Fill it down, and review any printed balances that were imported as text separately. A blank difference in this design means there is no numeric comparison on that row. It must never be interpreted as proof that the row agreed. Add a separate count of numeric printed checkpoints and make sure it matches the number you expected from the PDF. Label the difference direction as “calculated minus printed.”
Follow an example through four transactions
Start at 2,400.00. A supplier payment of 95.50 gives 2,304.50. A customer payment of 320.00 gives 2,624.50. A fee of 4.50 gives 2,620.00. A rent payment of 600.00 gives 2,020.00. The four signed movements sum to negative 380.00. Adding that movement to the opening value reproduces the closing amount. Compare all four printed checkpoints, not just the last one, and confirm that the difference column contains four zeros rather than three zeros and a blank.
Now imagine the extracted supplier line reads 59.50 instead of 95.50. The first calculated balance is 2,340.50, which is 36.00 too high. The next three differences will also be positive 36.00 if their amounts are otherwise correct. That plateau suggests the earliest mismatched row is the place to inspect. Start at the last verified match and compare the following source line with the export. Do not use an unexplained adjustment on the final row merely to force it to match.
Recognize a mismatch that disappears
Suppose the 320.00 receipt and the 4.50 fee are displayed in swapped order but their printed balances were preserved in the original order. The running check will fail within that interval and then recover once both movements have been included. The closing balance alone misses the disorder. Consult the printed transaction sequence, including order within each day, and restore it before sorting by description or amount. If the statement only prints daily balances, you may be able to verify the daily subtotal but not the internal order of the day's movements.
Find the first break without losing source order
Keep a sequential source position and an original transaction date. Preserve physical order if the statement lists transactions oldest first. If the bank presents newest first and shows the balance after each posting, reverse the complete chronological sequence for this forward calculation, preserving the relationship between each transaction and its own printed balance. Sorting only by date can reorder transactions that occurred on the same day. Establish the sequence by walking through a small group of adjacent printed values before reversing an entire period.
Apply a filter to the difference column to show nonzero numeric values. Locate the earliest mismatch in the verified calculation order and inspect that line plus the preceding and following source rows. If the difference begins immediately after a page boundary, check the final transaction on the previous page, a possible carried-forward total, and the first transaction on the new page. A carried-forward balance is a checkpoint; entering it as a movement adds the full balance to the next page's activity.
Handle deposits and payments printed in separate columns
Two positive amount columns can support a running total if you convert them to one signed movement first. On a deposit account where credits increase the printed balance, the usual candidate is credit minus debit. Verify a representative payment and a representative deposit using independent printed balances. Review any row with both sides empty or both sides populated. A summary line frequently has two nonempty fields. It may be a useful total for validation even though it does not belong in the transaction list.
Work with incomplete balance information
A statement might provide only an opening and closing amount. You can still build a calculated running sequence for review, but intermediate values should be labeled as calculated. The only independent checkpoint for the period may be the closing balance. If the final amount fails, compare debit and credit totals, row counts, and any subtotal printed at the end of a page. If none are available, break the statement into manageable groups of transactions and manually verify one group's signed sum at a time.
Watch for days on which an outgoing transfer and an incoming transfer have equal amounts. Omitting both produces the right net movement and the right closing balance while understating activity. Checking separate deposit and payment totals, transaction counts, and representative documents improves the evidence. A balanced sheet confirms one arithmetic relationship. It cannot by itself establish who authorized a payment, whether a transaction has the right category, or whether an offsetting pair disappeared during extraction.
Avoid mixing balances across currencies or accounts
A combined bank statement can show a checking account and savings account under the same customer name. A transfer can appear once in each section, once as an outflow and once as an inflow. Compute separate opening balances, movement sequences and closing checks for each account and currency. If the workbook later shows a consolidated report, retain the individual reconciliation sheets. They preserve information that disappears when you add movements across both sides of an internal transfer.
Package the result for repeatable review
Save a raw sheet, a reconciled sheet and a compact control block. In the control block record the opening balance, total credits, total debits, net movement, calculated close, printed close and difference. Include the number of transaction rows and the number of independently compared checkpoints. If a whole page lacked running balances, name that limitation beside the control. A reviewer can then distinguish a fully checked sequence from a workbook whose ending amount merely happened to match.
To prepare a first table from a digital PDF, use the statement to Excel converter and review the extracted cells before download. If the worksheet still shows a difference, follow the guide to locating a statement mismatch. When your review is complete, preserve both the formula version and, if needed, a separate values-only copy for the recipient. The formulas explain how each balance was computed and give the next reviewer a practical starting point when a later statement changes layout.
Which version should you send for a review?
If the recipient needs to investigate a difference, send a copy containing the formulas and source positions while retaining the original separately. A reviewer can select the first unmatched balance, trace its cumulative movement and identify the exact interval that changed. If the recipient only needs final amounts to import into another application, supply a values-only copy with clear column names and the period control totals. Test one import first. Some systems treat blank comparison cells as zero, which can obscure which checkpoints were actually verified.