Got a bank export with separate Debit and Credit columns, but your importer wants one signed Amount field? Happens all the time. The good news: turning those two columns into a single Amount (money out as negative, money in as positive) is quick once you know a couple of safe rules.

Below I’ll show simple Excel formulas, a Power Query setup you can refresh every month, a Google Sheets method, and how to make it bulletproof with a few checks. If you’d rather not babysit spreadsheets, I’ll also show how BankXLSX handles the boring parts for you.

• The go-to rule: Amount = Credit − Debit (plus what to do if your bank already uses signed numbers)
• Excel and Sheets formulas, with fixes for text numbers and different locales
• Power Query steps for a repeatable “credit minus debit” custom column
• Validation tips so totals and balances actually match the statement
• How to export to CSV/XLSX and automate with BankXLSX, even from PDFs

What this guide covers and why a single Amount column matters

Most accounting imports and reports expect one Amount column: positive for money in, negative for money out. Keeping Debit and Credit separate looks fine on screen, but it makes totals, pivots, and imports easier to mess up.

When you switch to a single Amount, a few nice things happen fast: your net cash flow is just SUM(Amount), and your balance check becomes simple math: opening + SUM(Amount) = closing. No more double-counting because someone summed both Debit and Credit by accident.

• Imports are simpler. A lot of tools map “Amount” directly with a sign convention.
• Analysis is faster. One number to group, filter, and chart.

Do yourself a favor and note your sign rule right in the file. Add a tiny check cell like SUM(Credit) − SUM(Debit) − SUM(Amount) and expect 0. That single line catches most mistakes before they go downstream.

Understand your bank’s export and sign conventions

Before you write a formula, scan 10–20 rows. You’re checking sign rules and whether numbers are truly numbers. Common patterns you’ll see:

• Debit and Credit show positive values, with one column blank per row
• Debits already negative, credits already positive
• Parentheses for negatives, like (123.45)
• Trailing minus, like 123.45-

Locale matters a lot. In many European files, 1.234,56 is one thousand two hundred thirty‑four euro and fifty‑six cents. Excel may misread that unless you use NUMBERVALUE instead of VALUE and set separators. Also watch for non‑breaking spaces (CHAR(160)) and currency symbols sneaking in.

One quick profiling trick: add a tiny summary above your table showing counts of numeric vs text cells for Debit/Credit, and how many rows have both filled. You’ll spot format drift when the bank tweaks a download format without warning.

The quick rule of thumb (most cases)

In the most common layout, Debit and Credit show as positive numbers and the other column is blank. Use this and you’re set: Amount = Credit − Debit. It also handles those odd rows where both columns have values (reversals, fee adjustments, etc.).

Small example:

• Debit 125.00, Credit blank → Amount = −125.00
• Debit blank, Credit 1,200.00 → Amount = 1,200.00
• Debit 50.00, Credit 50.00 → Amount = 0.00

Check yourself with two quick tests:

• SUM(Amount) equals SUM(Credit) − SUM(Debit)
• Opening + SUM(Amount) equals Closing (if balances are present)

Edge case: if Debits show up as negatives and Credits as positives, don’t subtract. Just do Amount = Debit + Credit. A small diagnostic cell that looks at a few hundred rows can flip the rule automatically if it detects signed values.

Prepare your data in Excel for reliable transformations

Set it up once, avoid rework:

• Turn your range into an Excel Table (Ctrl/Command+T). Tables auto-fill formulas and use headers like [@Debit].
• Keep an “Imported” sheet raw. Do your math on a “Working” sheet that references the table.
• Format Debit/Credit as Number. Fix any “numbers stored as text” before calculations.

Reusable logic helps. Make a named LAMBDA like AmountFromDC(d,c) that returns IFERROR(c,0)-IFERROR(d,0), then use it across the Amount column.

If you need to export, decide your final columns now (Date, Description, Amount, etc.) and keep that order every time. Map once, reuse forever.

Bonus tip: add a “Schema Version” note. If you change columns or rules later, bump the version so your team knows what they’re looking at.

Excel formula methods for common scenarios

Case 1: Debit/Credit are positive (or blank)
Structured refs: =IFERROR([@Credit],0) - IFERROR([@Debit],0)
Column letters (B=Debit, C=Credit): =IFERROR(C2,0) - IFERROR(B2,0)

Case 2: Debits negative, credits positive
Signed already? Use: =IFERROR([@Credit],0) + IFERROR([@Debit],0)
Or pick the nonblank: =IF([@Debit]<>0, [@Debit], [@Credit])

Case 3: Mixed types (text and numbers)
Let Excel coerce them: =N([@Credit]) - N([@Debit])

Cleaner with LET:

=LET(d, IFERROR([@Debit],0), c, IFERROR([@Credit],0), c - d)

Big files tip: keep it simple per row in a Table and avoid volatile functions. For 100k rows, a single table formula is usually faster than complex arrays.

And add this tiny assertion somewhere obvious:
=SUM([Amount]) - (SUM([Credit]) - SUM([Debit]))
Format 0 as “OK”, otherwise show “Check”. You’ll spot sign flips instantly.

Converting text numbers, locales, and negatives to numeric

Banks love weird formatting: commas, dots, currency signs, even invisible spaces. Clean first, then do math.

Locale-aware parsing

• US style: NUMBERVALUE(text, ".", ",")
• EU style: NUMBERVALUE(text, ",", ".")

Negatives that aren’t obvious

• Parentheses: SUBSTITUTE(SUBSTITUTE(text,"(","-"),")","")
• Trailing minus: IF(RIGHT(text,1)="-","-"&LEFT(text,LEN(text)-1), text)

Noise to remove

• Non‑breaking space: SUBSTITUTE(text,CHAR(160),"")
• Currency symbols: SUBSTITUTE(text,"$",""), same idea for “€” and “£”

All-in-one pattern:

=LET(t, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),""),"(","-"),")",""), t2, IF(RIGHT(t,1)="-","-"&LEFT(t,LEN(t)-1), t), NUMBERVALUE(SUBSTITUTE(SUBSTITUTE(t2,"$",""),"€",""), ".", ","))

Lock your separators in NUMBERVALUE so someone else’s regional settings don’t quietly change your numbers on another laptop.

Rows with both Debit and Credit populated

It happens: a fee and an instant reversal on one line, or some adjustment that hits both columns. Your Amount rule should still work: Amount = Credit − Debit nets it out.

Examples:

• Debit 250.00 and Credit 250.00 on the same day → Amount = 0.00
• Debit 35.00 with Credit 10.00 refund → Amount = −25.00

Two quick controls help here:

• Flag rows where both columns have values: AND([@Debit]<>0, [@Credit]<>0)
• Sum those rows and confirm the math still equals Credit minus Debit for that subset

Keep the raw Debit/Credit on a protected “Raw” sheet and do your netting on a “Working” sheet. Auditors (and your future self) will thank you when a zero Amount needs explaining.

Repeatable, one-click refresh using Power Query (Get & Transform)

If you grab new files each month, Power Query keeps the steps the same and lets you refresh in seconds.

1. Data > Get Data > From Text/CSV (or From Workbook)
2. Change types “Using Locale” for Debit/Credit so commas/dots parse correctly
3. Replace nulls with 0 if you like cleaner math
4. Add Column > Custom Column: [Credit] - [Debit]
5. Drop the original Debit/Credit if you don’t need them in the final table
6. Close & Load back to Excel

Make it tougher with try…otherwise so weird rows don’t break the query. For example, convert parentheses to a minus, then Number.From. Parameterize the file path so you can just swap in a new CSV next month. Also set data types on every column—those types become your guardrails against format drift.

Google Sheets approach for lightweight collaboration

In Sheets, you can net the whole column with one formula:

=ARRAYFORMULA({"Amount"; IF(ROW(B:B)=1, , N(C:C) - N(B:B))})

B is Debit, C is Credit. N() handles blanks and numbers. If values are text (parentheses or trailing minus), normalize first with SUBSTITUTE and VALUE.

• Parentheses fix: =ARRAYFORMULA(IF(LEN(B:B), VALUE(SUBSTITUTE(SUBSTITUTE(B:B,"(","-"),")","")), ))
• Trailing minus converter: =IF(RIGHT(t,1)="-", -VALUE(LEFT(t,LEN(t)-1)), VALUE(t))

Use a filter view and sort by Amount to spot outliers fast. Add conditional formatting to highlight rows where both columns have content. Protect the import range so folks don’t overwrite your formulas, and download as CSV when you’re ready to import elsewhere.

CSV-first automation for ops and data teams

If you automate, do the netting before Excel is even open. Basic flow:

1. Read the CSV
2. Clean numbers (strip currency, spaces; convert parentheses; fix trailing minus)
3. Calculate: amount = credit - debit
4. Write out date, description, amount, balance, reference

In pandas, convert text to numbers with a safe parser, then do amount = credit.fillna(0) - debit.fillna(0). Add a few unit tests so the sign convention doesn’t regress later.

Create a stable transaction_id (e.g., hash of date ISO + absolute amount + last 8 of description + reference). That helps kill duplicates when monthly files overlap.

One more tip: write Amount as a plain decimal string with a dot as the decimal separator (“-1250.50”). Localization belongs in the UI, not the CSV. It avoids confusion across teams and tools.

Validating your results before import

Build the checks into your file so you don’t forget them:

• Sum check: SUM(Amount) = SUM(Credit) − SUM(Debit) (expect 0 difference)
• Balance check: opening balance plus net transactions equals closing balance check
• Row count: no accidental deletions or extra header rows
• Duplicates: use transaction_id or a unique key
• Outliers: filter big values and zeros that look suspicious

Helpful flags:

• BothPopulated = AND(Debit<>0, Credit<>0)
• TextNumbers = OR(ISTEXT(Debit), ISTEXT(Credit))
• Descriptions with “refund” or “reversal” for quick sign sanity checks

If your file has a running balance, use it. The change between balances should equal that row’s Amount. If not, your signs are probably flipped for that export.

Common pitfalls and how to avoid them

• Numbers stored as text: clean currency signs, non‑breaking spaces, then parse with NUMBERVALUE
• Locale swaps: 1.234,56 vs 1,234.56. Parse with explicit separators or “Using Locale” in Power Query
• Double-negatives: debits already negative plus you subtract Debit = wrong sign. Detect the sign rule first
• Repeated headers: some banks add them mid-file. Filter out before math
• Date confusion: 03/04… is that March 4 or April 3? Prefer ISO (YYYY‑MM‑DD)
• Parentheses and trailing minus: normalize to a leading minus, then parse

Consider a tiny “tests” section in the workbook with TRUE/FALSE checks and a big colored status cell. If any test fails, don’t import yet. It keeps surprises out of month-end.

Automate at scale with BankXLSX

If you handle several banks, multiple entities, or PDF statements, manual cleanup gets old fast. BankXLSX handles the heavy lifting:

• Upload CSV/XLS/XLSX, or convert a PDF bank statement to Excel with a single Amount column
• Auto-detect columns, date formats, and tricky number styles
• Map Debit and Credit to a signed Amount once (e.g., Credit − Debit), reuse every month
• Check opening/closing balances and flag mismatches
• Deduplicate when months overlap
• Export clean Excel or CSV in the exact order your importer expects

Save a template per bank or account. Next time, upload, preview, export. You keep the original raw fields for audit, plus the standardized output for imports. It’s tidy and consistent across the whole group.

Importing into accounting and BI tools

Most importers accept a simple layout:

• Date (ISO is safest)
• Description
• Amount (negative out, positive in)
• Optional: Balance, Reference, Category, Account

Before you hit Import, confirm the sign expectation (some tools flip it), verify date format, and set currency if needed. Publishing a short “CSV contract” in your wiki—column names, order, encoding (UTF‑8), decimal separator—saves back‑and‑forth later.

If your BI tool reads the same file, great. One file, one Amount, less room for mistakes.

Troubleshooting edge cases

• Parentheses negatives: turn (123.45) into -123.45 first, then parse
• Trailing minus: convert 123- to -123 before NUMBERVALUE
• Non‑breaking spaces (from PDFs): remove with CHAR(160) or Power Query’s Text.Clean
• Mixed locales in one file: detect patterns and parse with branching rules; use try…otherwise in Power Query
• Same‑day fees/refunds: zero Amounts are fine, just keep traceability
• Debits already signed: don’t subtract them again—use Debit + Credit

When stuff gets weird, park problem rows in a “Quarantine” table with a short note like “trailing minus” or “mixed locale.” Next time the bank changes format, you’ll know exactly what to fix.

FAQs

Is there one formula that works most of the time?
Yes. If Debit/Credit are positive or blank, use Amount = IFERROR([@Credit],0) - IFERROR([@Debit],0).

What if my bank already uses signed numbers?
Use Amount = IFERROR([@Credit],0) + IFERROR([@Debit],0), or just pick the nonblank column if they never overlap.

How do I handle decimal commas?
Use NUMBERVALUE(text, ",", ".") after stripping spaces and symbols. It respects European formats.

Can Google Sheets do this for the whole column?
Yep: =ARRAYFORMULA(N(C:C) - N(B:B)) (C=Credit, B=Debit).

How do I know I didn’t flip signs?
Two checks: SUM(Amount) = SUM(Credit) − SUM(Debit) and opening + SUM(Amount) = closing.

What about PDFs?
Export to CSV/XLSX if you can. If not, convert the PDF to a table, then net columns—or use BankXLSX to convert and output a signed Amount automatically.

How do I stop duplicates across months?
Build a stable transaction_id and dedupe before import.

Checklist: ready for production

• Signed Amount column in place, formula locked in a Table
• Validation passes: sum check and opening/closing balance check
• Dates are real dates (prefer ISO)
• Numbers parsed with explicit locale; symbols/spaces removed
• Debit/Credit kept on a “Raw” sheet, removed from final export
• Duplicates checked via a stable transaction_id
• Column order matches your importer
• Power Query or a template saved so you can repeat this monthly
• Short note at the top: sign rule, schema version, how to refresh

Add an “Assertions” sheet with simple TRUE/FALSE tests and a big colored status. If anything fails, pause. It’s a small guard that stops messy imports.

Quick Takeaways

• Default rule: Amount = Credit − Debit. If the export uses signed numbers already, use Debit + Credit or pick the nonblank column. Always confirm with SUM(Amount) = SUM(Credit) − SUM(Debit) and a balance check.
• Clean first: fix text numbers, locale separators, currency signs, parentheses, and trailing minus. Excel Tables help formulas fill and stay put.
• Catch issues early: flag rows where both Debit and Credit are filled, check dates, and keep a couple of assertion cells. Store raw data on a separate sheet and create a stable transaction_id to avoid duplicates.
• Automate if you can: Power Query for a refreshable “credit minus debit” column, or use BankXLSX to handle mapping, cleaning, validation, dedupe, and export to CSV/XLSX—great for multi-bank or PDF workflows.

Summary and next steps

One signed Amount column keeps imports and analysis straightforward. Most files work with Amount = Credit − Debit; signed exports use Debit + Credit. Clean funky text and locale quirks, then run quick sum and balance checks. Power Query gives you repeatable steps. Google Sheets works fine with an ArrayFormula. Code and CSV-first pipelines are ideal for teams that automate.

Next steps:

• Make a template workbook with your rule, checks, and final column order
• Save Power Query steps or a small LAMBDA so you’re not rebuilding each time
• If you handle multiple banks or PDFs, upload to BankXLSX, save a template per account, and export a clean Excel/CSV in minutes

Want month-end to move faster and with fewer surprises? Try your next statement in BankXLSX, keep the mapping, and let it handle the repetitive parts so you can focus on the actual numbers.