Transaction Balances vs Daily Closing Balances
Bank statements record balances in one of two formats: transaction-level running balances (where every single debit and credit updates the balance line-by-line) or daily summary balances (where the bank reports one consolidated closing balance for all activity on that calendar date).
When auditing cash flows or preparing general ledger imports, accountants often need to reconstruct a continuous running balance in Excel. While simple in theory, running balance formulas fail frequently if transactions are sorted in reverse chronological order or if multiple entries clear on the same date.
Always sort your transactions in ascending chronological order (oldest to newest) before calculating a running balance. Running balances calculated top-to-bottom on descending statements will produce meaningless, inverted numbers.
Debit/Credit Sign Conventions Before Writing Formulas
Before applying running balance formulas, verify how amounts are represented in your spreadsheet:
• Two-Column Format (Deposits & Withdrawals): Both columns contain positive numbers. In this format, your formula must explicitly add the deposit cell and subtract the withdrawal cell: =PreviousBalance + Deposit - Withdrawal.
• Single Signed Amount Column: Inflows are positive numbers and outflows have a leading minus sign (-). In this format, your formula simply adds the signed amount cell: =PreviousBalance + Amount.
Below, we demonstrate both methods with your statement opening balance placed in cell E1 (e.g., 2,500.00).
Method 1: The Classic Cumulative Formula (All Excel Versions)
This universal method works in all versions of Excel (Excel 2007 through Microsoft 365, as well as Google Sheets).
Important Data Integrity Rule: Never use the N() function to suppress errors on amount cells. In Excel, N("1,250.00") silently converts numeric text into zero, masking unparsed deposits or debits without warning and corrupting your account balance.
Instead, require validated numeric amounts before calculating balances. If your export contains text strings, clean them first using VALUE or Text to Columns. In your balance formula, flag unexpected text explicitly:
Assuming Deposits are in Column C, Withdrawals are in Column D, and your Statement Opening Balance is in cell E1:
Enter this formula in cell E2 and copy it down the column:
=IF(OR(ISTEXT(C2), ISTEXT(D2)), "ERROR: Text in Amount", E1 + C2 - D2)How this formula works:
• Empty cells in Excel evaluate to 0 in standard arithmetic (=E1 + C2 - D2), correctly handling rows with deposits but no withdrawals.
• If a cell contains a non-numeric text string (such as an unparsed currency symbol or space), the formula returns an explicit "ERROR: Text in Amount" so you can fix the source data immediately rather than silently corrupting your balance.
If transactions are recorded in a single signed Amount column (Column C):
=IF(ISTEXT(C2), "ERROR: Text in Amount", E1 + C2)To keep rows below your data table visually blank, wrap the formula in an IF check on the date column:
=IF(ISBLANK(A2), "", IF(OR(ISTEXT(C2), ISTEXT(D2)), "ERROR: Text in Amount", E1 + C2 - D2))Method 2: Calculating Daily Closing Balances with SUMIFS
If multiple transactions occur on the same day and you need the final closing balance for each date (matching the bank’s daily ledger summary):
In cell F2, calculate the total cumulative change up through that date and add it to your initial opening balance ($E$1):
=$E$1 + SUMIFS($C$2:$C$100, $A$2:$A$100, "<="&A2) - SUMIFS($D$2:$D$100, $A$2:$A$100, "<="&A2)How this formula works:
• SUMIFS sums all deposits (C2:C100) where the date is less than or equal to the current row’s date (A2).
• It subtracts all withdrawals up to that same date.
• Adding the Opening Balance ($E$1) gives the exact end-of-day cash balance.
Method 3: Dynamic Running Balances with SCAN (Microsoft 365 Only)
In Microsoft 365 and Excel for the Web (version 2202 or later), you can generate an entire running balance column dynamically from your opening balance ($E$1) and signed amount range (C2:C100):
=SCAN($E$1, C2:C100, LAMBDA(prev, current, prev + current))Input Validation Requirement for SCAN:
Like all mathematical array functions, SCAN strictly requires validated numeric inputs. If any cell in C2:C100 contains text (e.g. "150.00" exported as text or a space character), SCAN will fail with a #VALUE! error or stop accumulating. Verify that your amount range contains only true numbers using =COUNT(C2:C100)=COUNTA(C2:C100) before applying SCAN.
Note on Excel Versions: SCAN and LAMBDA are exclusive to modern Microsoft 365 subscriptions. In perpetual versions like Excel 2021, 2019, or 2016, use Method 1 (=E1 + C2 - D2), which is universally supported across all spreadsheet software.
Performing an Arithmetic Consistency Check Against Statement Ending Totals
After calculating running balances down to the final transaction row, perform this critical arithmetic consistency check:
1. Does the final calculated balance in your sheet equal the official "Closing Balance" printed on the bank statement?
2. Does Opening Balance + SUM(Deposits) - SUM(Withdrawals) equal Closing Balance?
Important Audit Limitation: A zero discrepancy confirms mathematical consistency across the transaction set and statement balance checkpoints. However, matching totals do not prove row-level completeness or correctness (mutually offsetting errors will net to zero). Always verify that line-item dates and running balances match the official PDF statement.
Convert Bank Statement PDFs Cleanly with PDFtoRow
Tired of fixing broken columns, corrupted dates, and numbers stored as text? PDFtoRow extracts transaction tables directly from digital PDF vector layers, preserving check numbers and outputting true numeric amounts. Statements up to 5 pages are free once per email every 24 hours. Check the 10-row preview before you download.
Frequently Asked Questions
A #VALUE! error usually means cell E1 (your opening balance) contains text rather than a number, or one of your amount cells contains a space or currency character instead of a valid numeric value.
Banks often list deposits first and debits second within the same day, or order them by transaction size. Ensure your spreadsheet preserves the exact line order printed on the PDF statement to match intraday balances.
Primary Evidence & Official References
- Microsoft Support: SCAN Function — support.microsoft.com (Verified 2026-10-19). Official source documentation →
- Microsoft Support: SUMIFS Function — support.microsoft.com (Verified 2026-10-19). Official source documentation →