Turn debit and credit columns into a signed amount in Excel

Your file may not need changing

The bank-native download may already fit your importer's mapping screen. If it does, changing it only risks reversed signs or lost rows. Your review copy should still keep Date, Description, Money out and Money in, even if the importer needs fewer columns.

This covers transaction CSVs, not journal-entry imports or full accounting migrations. The references cover QuickBooks Online US, Xero Central CSV statements, YNAB file-based import and Sage 50 Canada 2023 Bank Services. We checked them on October 17, 2026, but didn't test imports. The Excel formulas use English function names and comma argument separators. If your Excel needs semicolons, replace only the argument-separating commas, not quoted decimal or grouping characters.

Intuit's formatting guide, updated August 24, 2026, supports Date, Description, Amount or Date, Description, Credit, Debit. Its three-column example shows a payment as -100.00 and a deposit as 200.00. Yet its four-column example puts the payment under Credit and the deposit under Debit, which is why headers alone aren't enough to choose signs.

Xero's CSV guide, checked in the expanded browser page on October 17, 2026, requires one amount column with positive income and negative expenses. Card payments are positive and purchases negative too, so liability-positive card amounts need their signs reversed. Commas aren't allowed as decimal separators.

YNAB's file-based import guide, updated September 22, 2026, maps Date, Payee, Memo, Outflow and Inflow. Its single-Amount FAQ says to map the same signed column to both Inflow and Outflow, so there's no need to split it. YNAB also has Swap Inflow and Outflow, a mapping worth trying before you rebuild the file.

Sage's Canada 2023 help, published July 17, 2023, covers CSV or OFX uploads to Bank Services. That flow requires you to confirm money in or money out before import and have a service plan that includes Bank Services. It doesn't establish a CSV recipe for other Sage products, countries or editions.

The account and importer decide what positive means

A rent payment, customer deposit or card payment you recognise tells you more than a heading. Look at its amount column and write down what it means from the account holder's view. A transaction going the other way lets you check that interpretation.

In this bank example, money out means withdrawals and purchases, while money in means deposits and refunds. That gives you Amount = money in - money out. A liability-positive card view uses Amount = spending - payments and refundsinstead: positive increases what you owe. Paying the card isn't spending on it, even though money left your bank account.

Intuit's card-specific note explains that deposits can appear negative because paying the card reduces its balance. It also tells you to choose the credit card account during upload. Xero uses the opposite signs, so knowing the account type still doesn't tell you the importer's convention. See credit-card purchases, refunds and payments for the full comparison.

What happens to the same six transactions

Here's a fictional March 2026 bank account, also used in our statement CSV checking guide. It isn't a customer file or paid conversion. Debit means money out here, and Credit means money in. “Blank” means an empty amount cell, not zero.

Money in is 1,200.00 + 42.50 = 1,242.50, while money out is 1,150.00 + 86.45 + 29.00 + 12.00 = 1,277.45. That leaves a net of 1,242.50 - 1,277.45 = -34.95. With an opening balance of 2,400.00, the closing balance is 2,365.05. All six transactions, both gross totals and the net need to survive the conversion.

The net alone can hide mistakes: adding the same wrong amount to each side leaves it unchanged. Reversing every sign turns -34.95 into +34.95 without losing a digit. You need to check both directions, because matching totals can show consistency without proving the signs correct.

You can inspect these rows by choosing Load example in the converter. Switching perspective is useful for testing signs, but it doesn't turn this bank example into a card statement.

Merge in Excel without hiding problem rows

You'll need an untouched CSV alongside your working workbook, because opening a CSV directly uses Excel's default format settings. Microsoft's CSV import instructions describe Data → From Text/CSV. Protect dates and identifiers on import: these calculations leave those fields alone and can't repair dates Excel has already interpreted.

For this example, Date goes in A, Description in B, money out in C and money in in D. Put headings in row 1 and the six transactions in rows 2 through 7. With validated cells, the bank formula is =IF(D2="",0,D2)-IF(C2="",0,C2). On unchecked data, though, it turns two blanks into zero and silently nets two filled cells.

This guarded version flags those rows. Put it in E2 and fill down to E7:

=IF((C2="")=(D2=""),"REVIEW",IF(C2="",IF(ISNUMBER(D2),IF(D2>=0,D2,"REVIEW"),"REVIEW"),IF(ISNUMBER(C2),IF(C2>=0,-C2,"REVIEW"),"REVIEW")))

The result is REVIEW when both cells are blank or both are filled. Otherwise, the filled cell must be numeric and nonnegative. Incoming values stay unchanged, while outgoing values are negated. Microsoft explains the branches in its IF reference and confirms in its IS functions reference that ISNUMBER doesn't convert numeric text.

Every REVIEW needs checking against the source before export, and formula errors block export too. Replacing them with zero just forces a total. For liability-positive output, use =IF(ISNUMBER(E2),-E2,"REVIEW") in another column after this calculation, keeping both results so you can check a purchase and a payment.

Splitting a signed amount should keep zero rows too

If you need separate columns again, start with a money-in-positive amount in E2. Put Money out in F1 and Money in in G1, then fill these formulas down:

Money out, F2:
=IF(ISNUMBER(E2),IF(E2<0,-E2,""),"REVIEW")

Money in, G2:
=IF(ISNUMBER(E2),IF(E2>=0,E2,""),"REVIEW")

Negative amounts become positive Money out amounts, while positives stay in Money in. Zero stays in Money in with Money out empty, which preserves the row without assigning zero an economic direction. Empty or text source cells return REVIEW.

A liability-positive source needs its numeric amount negated in a helper column first; split that result, not the original. You can compare against the six-row example: rent returns to 1,150.00 money out, the refund to 42.50 money in, and totals stay at 1,277.45 and 1,242.50.

Your formula workbook is worth keeping, with a separate values-only sheet for export. Microsoft's export instructions warn that text export saves only the current worksheet and drops unsupported workbook features. Before importing, reopen the CSV as text to check its headings, separators and values.

Blanks and text need a closer look

One blank side and one amount is normal, but two blanks mean there's no amount. A zero beside another amount still counts as two filled cells and needs review. An importer's zero-cell rule isn't a reason to erase zeros across the file, including dates and references.

A cell can look numeric and still be text. A helper column lets you convert it and compare against the source without stripping punctuation everywhere. Neither 1,234 nor 1.234 tells you the locale by itself.

Microsoft's NUMBERVALUE reference lets you specify decimal and grouping separators. For known decimal-comma text, the formula is =NUMBERVALUE(C2,",","."). This helper also preserves blanks and already-numeric cells before the guarded merge:

=IF(ISNUMBER(C2),C2,IF(TRIM(C2)="","",NUMBERVALUE(C2,",",".")))

NUMBERVALUE isn't a plain-amount validator: it ignores even spaces inside numbers and accepts percentages. Check the text first, because wrong separators can silently change values. Keeping the source beside the helper lets you compare every result. The blank guard uses TRIM, which handles ordinary spaces, not nonbreaking spaces copied from web pages.

Xero accepts bracketed negatives, but this converter flags parentheses for review rather than guessing a sign. In Excel, a numeric negative with accounting formatting is different from literal text containing parentheses, so check which you have.

Two amounts on one row might be a subtotal, a malformed export or two collapsed events. Keep the original and resolve the row there rather than netting it automatically. Absolute values or blind subtraction destroy that distinction.

The number-format and date guide covers ambiguous separators and dates. The statement or export settings should determine your separators, not whichever makes the balance look right.

Microsoft documents a 15-digit numeric precision limit for Excel. For amounts beyond that precision, keep the source as text and use exact-decimal arithmetic rather than these formulas. The browser converter uses exact integers at the selected precision.

Keep the audit copy separate from the import

The browser tool keeps source order and non-amount columns while replacing your selected amount fields, and it can append original debit/credit values. Its JSON report keeps every original row, including exclusions. CSV export neutralizes formula-like text and lists the change, but parsed negative amounts remain numbers. You must approve the CSV before downloading it.

If you can't resolve an issue row, you can explicitly exclude it, but label the file partial. Excluded amounts aren't in the included-row totals, so a partial file can't show that the whole statement reconciles. Keep the report with the original rather than presenting the reduced file as complete.

Before export, confirm you have one account, currency and sign convention, with every REVIEW cell resolved or explicitly excluded. Compare included row counts, money-in totals, money-out totals and net before and after. The review copy should keep originals and audit columns; the separate import copy needs the destination's required headings. Then inspect one known purchase and one deposit, refund or payment in the destination preview before confirming, so you can check the direction there too.

If you only have a PDF, look for a bank-native download, prepare rows manually, or use your bookkeeping app's statement-upload feature. Intuit's formatting page links its native PDF/image upload route. Alternatively, AnyStatement converts PDFs into reviewable data with source-line links and statement arithmetic checks. That separate service doesn't prove document authenticity or finish your bookkeeping reconciliation.

What we checked ourselves

On October 17, 2026, we opened the linked Intuit, YNAB, Sage and Microsoft pages and expanded Xero Central's data-preparation section in a real browser. We independently reproduced the Excel formulas' branch logic in Python Decimal for all six synthetic rows, checked both gross totals, net and closing balance, and tested blank, double-filled, negative and text cases. We didn't run desktop Excel or import into QuickBooks, Xero, YNAB or Sage. Equal totals check that interpreted amounts survived conversion, but they don't establish statement completeness, source authenticity or destination acceptance.