Xero Import Bank Statement Debit Credit: Fix Signs

Quick Answer: Xero's CSV import expects one amount column with a sign: money into the account positive, money out negative, written as -30.00 or (30.00). If your bank gives separate Debit and Credit columns, or a credit card file lists purchases as positive numbers, combine or flip them in a spreadsheet before importing. Xero has no import switch that reverses signs.

Reversed or vanishing amounts after a Xero import nearly always trace back to one mismatch: the file describes direction one way and Xero reads it another. Xero reads direction only from the sign of a single amount. Separate debit and credit columns, card statements that treat a charge as positive, and amounts with DR or CR suffixes all need converting before upload.

Start here: a five-minute diagnosis

Before editing anything, open the CSV in Excel or Google Sheets (not Notepad) and work through these checks in order. The first one that fails is your cause.

1. Count the amount columns. One column with plus and minus values is what Xero wants. Two columns, often headed Debit and Credit, Withdrawals and Deposits, or Paid Out and Paid In, need merging.
2. Look at a purchase. On a bank account, a payment out should be negative. On a credit card account, a purchase should also be negative, because it reduces what you hold. If purchases are positive, the whole file is inverted.
3. Look for letters next to numbers. Values such as 125.00 DR, 125.00 CR or 125.00- with a trailing minus are text, and the sign is not where Xero looks for it.
4. Look for blanks. In a two-column layout, each row has a value in only one of the columns. If you mapped only one of them, every row from the other column arrives with no amount.
5. Check the first rows. Opening balances, "balance brought forward" lines or a totals row at the bottom will import as transactions unless you delete them.

Why does Xero reverse debits and credits on import?

Because "debit" means opposite things depending on whose books you are reading. On your bank's statement, a debit is money leaving your account. In your accounting ledger, the bank account is an asset, and a debit to it is money arriving. Card issuers add a further twist: on their statement a purchase increases your balance owed, so it often prints as a positive number, while payments you make print as credits or negatives.

Xero sidesteps the vocabulary entirely. Its Xero Central article on CSV imports says both income and expense amounts must be in the same column, with income positive and expenses negative. For credit card accounts the rule is the same from Xero's point of view: card payments positive, purchases negative.

So a card CSV with positive purchases imports with every charge looking like a refund. Xero's Product Ideas forum has a request for a reverse option, "Manual import - Option to reverse Credits and Debits columns", which notes that the current advice is to modify the file with a spreadsheet editor. That is the fix, and it takes a single formula.

What the symptoms look like

How do you map separate debit and credit columns for Xero?

Add one new column and fill it with a formula that turns the pair into a single signed number. Assuming Debit (money out) is in column C and Credit (money in) is in column D:

1. Insert a column E headed Amount.
2. In E2 enter =N(D2)-N(C2). N() treats an empty cell as zero, so a row with only a debit becomes negative and a row with only a credit stays positive.
3. Fill the formula down to the last transaction.
4. Copy column E and paste it back as values, so the file holds numbers, not formulas.
5. Delete the original Debit and Credit columns, or simply leave them unmapped.
6. Save as CSV and, in Xero's column assignment step, map Amount to the transaction amount field.

For a credit card file where the issuer's Debit column holds purchases and its Credit column holds payments, the same formula gives the right result: purchases negative, payments positive.

If the bank labels columns from the bank's point of view and you are unsure which is which, find a transaction you recognise, such as your rent or a salary deposit, and check which column it sits in.

Flipping a whole credit card file

When the file already has one amount column but with the card issuer's sign convention:

1. In an empty column, enter =-B2 (where B is the original amount) and fill down.
2. Paste the result as values over the original column.
3. Spot-check: your monthly card payment should now be positive and a restaurant charge negative.

A refund from a merchant should end up positive as well, because it gives money back to the card account.

Cleaning DR, CR and trailing minus signs

Some banks, especially outside the US, print direction as letters. A formula handles both in one pass. With the raw value in B2:

=IF(RIGHT(TRIM(B2),2)="DR", -VALUE(LEFT(TRIM(B2),LEN(TRIM(B2))-2)), VALUE(SUBSTITUTE(TRIM(B2),"CR","")))

Adjust which suffix is negative to match your statement: on most bank accounts DR is money out. For a trailing minus such as 45.00-, Excel's Text to Columns (under the Data tab) has an Advanced option for trailing minus numbers, or you can use =-VALUE(SUBSTITUTE(B2,"-","")) on the rows that end in a minus. When values still refuse to sum, they are probably stored as text; the Excel help pages at Microsoft Support cover converting text to numbers.

Avoiding the problem with the next statement

If the source is a PDF statement, the direction question can be settled at conversion time. QuickBankConvert writes amounts with money out as negative, which is the same convention Xero uses, so a converted checking account file needs no merge step. On paid plans it also offers a Xero CSV layout; pricing shows which plans include it. Whatever tool produced the file, open it and check a known purchase before importing, because card statements in particular vary.

After any import, compare the closing balance Xero shows for that statement with the closing balance on the bank's PDF. A match means the signs are right. A gap of exactly twice one transaction means that line's sign is reversed.

If you already imported the wrong signs

Fixing hundreds of reversed lines one at a time is slow. Delete the imported statement from the account's Bank Statements screen, correct the CSV, and import it again. If some lines were already reconciled, unreconcile them first; Xero Central's article on deleting a bank statement explains what has to be undone before a statement can be removed.

Related guides

• How bank statement CSV files are laid out
• Import bank statements into Xero
• Precoded statement imports with account codes
• Bank reconciliation debits and credits explained