Pull statements from three different banks into one workbook and stack the amount columns, and the total will very likely be wrong. Not wrong because a number was misread, but wrong because one bank's debit is another bank's credit, arithmetically speaking, and nothing in the raw data tells Excel which is which until you tell it.
This is the ordinary case of a bank statement debit credit sign convention that varies from issuer to issuer, and it has to be normalized before any cross-bank total, pivot table or reconciliation means anything.
Why every bank encodes debits and credits differently
There is no shared standard for how a statement marks money leaving an account versus money arriving. Some banks split debits and credits into two separate columns, each holding a positive number. Others use a single amount column and rely on positive and negative values to carry the sign. Some print parentheses around debit amounts instead of a minus sign. Some append a "DR" or "CR" suffix directly to the number, as plain text.
Any one of these conventions is manageable on its own. The trouble starts when you combine statements that use different ones. A debit of 200 on one statement might read "200.00 DR"; the same debit on another bank's statement might read "(200.00)"; a third might simply put 200.00 in a column labeled "Debit" with no sign at all. Stack all three into one column and sum it, and the result adds up to nothing meaningful.
The fix is not to guess at each row. It is to convert every convention into the same signed number, once, with a formula that treats the source text consistently.
The four conventions you will meet
- Split debit and credit columns. Two columns, each with unsigned positive numbers; a value in the Debit column is money out, a value in the Credit column is money in.
- Single amount column with positive and negative values. Already signed, but you still need to confirm which sign the bank assigned to debits before you combine it with anything else.
- Parentheses around debits. The number itself is unsigned text with parentheses wrapped around it, for example (150.00), which Excel will not treat as a negative number unless you convert it.
- DR or CR text suffixes. The sign is spelled out after the number as a letter code rather than encoded arithmetically, for example "150.00 DR" or "88.20 CR".
Normalizing DR/CR suffixes into a signed number
Suppose column A holds amounts as extracted text: "142.50 DR", "88.20CR", or similar, with or without a space before the suffix. You want a single signed number in column B: negative for DR, positive for CR.
Use this formula in B2:
=IF(RIGHT(TRIM(A2),2)="DR",-VALUE(TRIM(LEFT(TRIM(A2),LEN(TRIM(A2))-2))),VALUE(TRIM(LEFT(TRIM(A2),LEN(TRIM(A2))-2))))
Read it in order. The outer TRIM removes any leading or trailing whitespace from the cell. RIGHT checks the last two characters against "DR"; if they match, the row is a debit. LEFT strips those same two characters off the front of the string, leaving the number and possibly a trailing space, which the inner TRIM removes. VALUE converts the remaining text into a real number. The outer IF applies a negative sign only when the suffix was DR, leaving CR rows positive.
If your statement uses "Dr" and "Cr" with mixed case, wrap the comparison in UPPER: RIGHT(UPPER(TRIM(A2)),2)="DR". Keep the rest of the formula unchanged.
Turning parenthesized debits into true negative numbers
Parentheses around a debit look unambiguous to a human reader, but Excel only auto-negates a parenthesized value when the cell already carries an accounting number format. A value pulled out of a statement document, parentheses and all, usually lands as plain text, so the parentheses are just characters, not a sign.
If column A holds text like "(150.00)" for debits and plain "88.20" for credits, this formula in B2 converts both into signed numbers:
=IF(LEFT(TRIM(A2),1)="(",-VALUE(MID(TRIM(A2),2,LEN(TRIM(A2))-2)),VALUE(TRIM(A2)))
LEFT checks whether the first character is an open parenthesis. If it is, MID extracts everything between the two parentheses, starting at position 2 and running for the length of the string minus the two parenthesis characters, and VALUE turns that substring into a number, which the leading minus sign then negates. If there is no open parenthesis, the ELSE branch just converts the plain text to a number directly.
Collapsing split debit and credit columns into one signed amount
When a bank exports separate Debit and Credit columns, the two are typically mutually exclusive: a row has a value in one or the other, never both. With Debit in column D and Credit in column E, this formula in F2 produces a single signed amount:
=IF(OR(D2="",D2=0),E2,-D2)
If the Debit cell is empty or zero, the row is a credit, so the formula returns the Credit value unchanged. Otherwise it returns the negative of the Debit value. If your extraction leaves blank cells as genuinely empty rather than as the text "0.00", the D2=0 half of the OR is redundant but harmless; keep it anyway, since extraction tools are not always consistent about which one they produce.
Once each bank's amounts are normalized into the same signed column, the mechanical part of combining them is straightforward: stack the rows, tag each with its source account, and the running totals for each account still work independently even after the sheets are merged. The article on combining checking, savings and a credit card in one workbook covers how to keep those per-account subtotals intact once everything lives in one sheet.
Check a mixed batch
Verifying the normalization before you trust it
A formula that looks right can still be wrong on a handful of rows, usually where the source text had an extra space, a currency symbol, or a suffix in a different case than you expected. Before you rely on a normalized column for anything, confirm it against the numbers printed on each statement.
- 1
Identify each bank's convention
Open one statement from each bank in the batch and note whether it uses split columns, a single signed column, parentheses, or a DR/CR suffix. - 2
Apply the matching formula
Add a helper column next to the raw amount and use the formula that matches that bank's convention to produce a signed number. - 3
Convert the helper column to values
Copy the helper column and paste it back as values, then delete the original text column so nothing downstream references the raw format again. - 4
Recompute the running balance
Starting from the statement's opening balance, add the signed amounts down the column and compare the final total to the closing balance printed on the statement.
A mismatch like the one on Bank B usually traces back to a single row where the sign formula did not fire, often because a suffix carried a stray space or a lowercase letter the formula did not anticipate. The article on amounts that import as text and make SUM return zero walks through the related problem of a column that looks numeric but is not, which is worth ruling out at the same time.
If the mismatch survives after you have checked the sign formula, the next place to look is the row-by-row detail rather than the total. Finding the row when a converted statement balance does not reconcile covers the search systematically, narrowing from the full statement down to the specific line where the running balance first diverges.
When the batch also crosses currencies
Combining sign conventions gets more complicated when the same batch mixes currencies, since a debit in one currency cannot simply be summed against a credit in another without a conversion step first. If your multi-bank workbook is also multi-currency, normalize the sign convention within each account first, then handle the currency conversion separately rather than folding both fixes into one formula. The guide on converting multi-currency statements for QuickBooks and Xero covers that sequencing in more detail, including where reconciliation tends to break if the two steps are done out of order.
Normalizing the sign convention is a small, mechanical step, but it is the step that determines whether every later total, pivot and reconciliation across the combined workbook is trustworthy or just coincidentally close.
Frequently asked questions
What does a DR or CR suffix mean on a bank statement amount?
DR marks the amount as a debit, meaning money left the account, and CR marks it as a credit, meaning money came in. The suffix sits after the number as plain text, so Excel reads the whole cell as text rather than as a signed value until you strip the suffix and apply the sign yourself.
How do I convert a parenthesized amount like (150.00) into a true negative number in Excel?
Use a formula that checks whether the first character is an open parenthesis, then pulls the digits from inside it with MID and negates the result. If A2 holds the text (150.00), the formula IF(LEFT(TRIM(A2),1)="(",-VALUE(MID(TRIM(A2),2,LEN(TRIM(A2))-2)),VALUE(TRIM(A2))) returns -150 as a real number.
Can I combine statements from banks that use different sign conventions in one spreadsheet?
Yes, once every amount has been normalized to a single convention, typically debits as negative and credits as positive. Combining columns before normalizing is what breaks the running total, because a debit column from one bank and a credit column from another get summed as if they meant the same thing.
Why does Excel not automatically recognize (150.00) as a negative number?
Excel only auto-negates parenthesized numbers when the cell is formatted as a genuine number with an accounting or custom number format applied. A value extracted from a statement document usually lands as plain text, parentheses included, so Excel treats it as a string until you convert it explicitly.
Does a statement converter handle sign convention differences automatically?
A converter that recognizes each bank's layout can normalize debit and credit signs as part of extraction, so amounts arrive in a single signed column instead of a mix of suffixes, parentheses and split columns. It is still worth spot-checking a few rows against the source statement before you rely on the result.
What is the fastest way to confirm normalization did not introduce an error?
Recompute the running balance from the opening balance down the normalized column and compare the final figure to the closing balance printed on the statement. If they match, the sign convention was applied consistently; if they do not, a row somewhere still carries the wrong sign.