Debits, credits and negative amounts in bank statement spreadsheets
Statements disagree about how to show money going out. Some use two columns, some use minus signs, some use brackets or the letters DR and CR. Turn all of them into one signed column and most of your downstream problems disappear.
By Muhammad Sharif · October 2026 · 8 min read
"Debit" is one of the most confusing words in everyday money, because it means opposite things depending on whose books you are in. On your bank's statement of your account, a debit is money leaving your account. In your own accounting records, a debit to an asset account such as cash means money coming in. A converted spreadsheet that mixes these conventions is not just untidy, it gives wrong totals.
The remedy is to decide on a single convention before you do anything else, and to convert every layout into it.
The convention to use: one signed amount column
For working with bank data, the simplest and least ambiguous convention is:
- Positive = money entering the account (deposits, credits, refunds, interest earned).
- Negative = money leaving the account (withdrawals, payments, fees, purchases).
It matches how most accounting import tools read a three-column file (date, description, amount), and it lets you total a column with a single SUM. Everything below converts toward this.
The five layouts you will meet
| Layout | Looks like | Convert to signed amount with |
|---|---|---|
| Single signed column | -84.17 and 2450.00 | Nothing to do |
| Two columns | Withdrawals | Deposits | =Deposit-Withdrawal (blank cells count as 0) |
| Brackets for negatives | (84.17) | Excel reads brackets as negative if the cell is numeric; if text, use =-VALUE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")) |
| DR / CR suffix | 84.17 DR · 2450.00 CR | =IF(RIGHT(A2,2)="DR",-1,1)*VALUE(LEFT(A2,LEN(A2)-2)) then trim spaces |
| Trailing minus | 84.17- | =IF(RIGHT(A2,1)="-",-VALUE(LEFT(A2,LEN(A2)-1)),VALUE(A2)) |
Test each formula on two or three rows against the PDF before filling down. In the DR/CR case, check for extra spaces or non-breaking spaces, which look like normal spaces but break VALUE; =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) cleans them.
Why rows lose their sign during conversion
When a statement prints withdrawals and deposits in different columns, a line of text extracted from the PDF contains just one number for the amount and gives no hint which column it came from. Many tools therefore export every amount as a positive. The information is still recoverable, because the running balance records the direction: if the balance after the row is lower than before, the row was a withdrawal. The converter on this site applies this inference automatically when a balance column exists, only for rows that have no explicit sign, and uses the same arithmetic you can check yourself:
=IF(ROUND(D3-D2,2)=ROUND(C3,2),1,IF(ROUND(D3-D2,2)=-ROUND(C3,2),-1,0))
Put that in a helper column. A 1 means the row increased the balance (a deposit), a minus 1 means it decreased it, and 0 means the row did not follow the pattern and deserves a look. This assumes oldest transaction first. If your statement is newest first, compare to the row below instead.
Credit cards flip the story
On a credit card statement, the balance is what you owe. A purchase increases the balance, and a payment reduces it. Banks print purchases as positive and payments and refunds as negative or with a CR marker. If you want one convention across checking and card accounts, so that spending is always negative, multiply the card amounts by -1 and make a note of it in the sheet. Otherwise a pivot table that combines accounts will net your card payments against your card purchases incorrectly.
The same applies to loan accounts and lines of credit. Decide for each account type whether the sheet reports "cash view" (money in or out of your pocket) or "balance view" (what the account balance does), and label the sheet.
Refunds, reversals and transfers
- Refunds appear as deposits from a merchant. Do not delete them; match them to the original purchase when you categorise.
- Reversals and chargebacks appear as a pair: the original, and an opposite entry. Both belong in the data so that the closing balance reconciles.
- Transfers between your own accounts show as a withdrawal in one and a deposit in the other. They are not income or spending. Mark them as transfers, so they cancel out in reports.
Checking the result
After normalising signs, run the equation from our reconciliation guide: opening balance plus the sum of the signed amounts must equal the closing balance. A sign error on a single row of $500 shifts the total by $1,000, which is easy to spot. If the sum is off by exactly twice a transaction, find that transaction and flip its sign.
A last practical test is to pick three obvious transactions, such as a payroll deposit, a rent payment and an ATM withdrawal, and confirm each has the sign you expect before you rely on the column.
Frequently asked questions
Should expenses be positive or negative in my spreadsheet?
For bank data, negative for money out is the most compatible choice with import tools and with SUM-based checks. If you report expenses to someone who expects positive numbers, create a separate column for the report using =-C2, and leave the source column untouched.
My import tool wants separate debit and credit columns. How do I split mine?
Use =IF(C2<0,-C2,"") for the debit column and =IF(C2>0,C2,"") for the credit column. Both come out positive, which is how four-column layouts expect them.
Why do some of my numbers show as text with a green triangle?
They contain characters Excel cannot read as numbers, such as brackets, currency symbols, trailing minus signs, DR/CR letters or non-breaking spaces. Clean them with the formulas above, then convert to values.
