Debit and credit cleanup
Split Bank Statement Debits and Credits in Excel
Reviewed 12 September 2026
Short answer
Short answer
If the source has one signed Amount column, put negative account movements in Debit and positive movements in Credit. If it has a running balance, verify the direction from the change between consecutive balances. Do not classify a transaction from words such as payment, transfer or refund alone, and remember that credit-card statements may present the account balance from the lender's perspective.
A perfectly formatted spreadsheet with debit and credit reversed is worse than an untidy one because the error is harder to notice.
Use statement values, not description guesses
For a normal transaction account, a falling running balance indicates money out and a rising balance indicates money in. The absolute change should match the printed transaction amount. That gives you a numerical check independent of the description.
Credit cards require care because purchases increase what is owed while payments reduce it. Read the statement's own debit, credit and balance conventions before applying a formula across every row.
Step by step
- 01
Identify the account convention
Determine how the statement labels or signs purchases, deposits, payments and balances.
- 02
Calculate balance movement
Subtract the previous running balance from the current one where consecutive balances exist.
- 03
Compare the amount
The absolute balance movement should equal the transaction amount.
- 04
Populate one direction
Place the value in Debit or Credit, leaving the other direction blank or zero consistently.
- 05
Reconcile totals
Prove that opening balance plus net movement equals closing balance under the statement's convention.
Checks before you rely on the spreadsheet
- Direction comes from signed values or balance movement.
- Every row uses only one of Debit or Credit.
- Credit-card conventions are handled separately.
- Opening balance plus movement reaches closing balance.
Questions people ask
Can I identify debits from the merchant description?
Descriptions can support review, but they are not reliable enough to determine direction. Use signed amounts, printed columns or running balance changes.
Why are credit-card debits confusing?
A purchase increases the amount owed rather than reducing a cash balance. Check the card statement's own convention before applying bank-account logic.
What should happen when the figures do not reconcile?
Stop and find the missing, duplicated or reversed row. Do not force the closing balance by inserting an unexplained adjustment.