Guide · StatementTidy
How to merge Debit and Credit columns into one Amount column
Short answer
With Debit in column C and Credit in column D, the signed amount is =N(D2)-N(C2): credits positive, debits negative. Both columns must already be real numbers. StatementTidy does this automatically when a table has exactly one debit-type and one credit-type column (Debit/Credit, Withdrawals/Deposits, Money out/Money in, Soll/Haben, مدين/دائن), and it treats a bracketed debit like (587.20) as money out, not a double negative.
Last updated · by Mumshad Jawad
Why this happens
Bank statements split each transaction across two columns: Debit (money out) and Credit (money in), with the other cell left empty. That is easy to read and awkward to analyse:
- A pivot table or chart needs one value field, not two.
- A monthly net figure means
SUM(Credit) - SUM(Debit)everywhere. - Accounting importers often ask for a single signed amount.
- Some banks put debits in brackets as well, so a naive
Credit - Debitturns(587.20)into +587.20.
Real example: before and after
The rows below are fictional. The “after” table is not a mock-up: this page ran them through StatementTidy’s cleaning engine (the same clean.js the extension uses, v1.0.1) with its default options, and shows exactly what you would export.
| Date | Description | Debit | Credit | Balance |
|---|---|---|---|---|
| 02/09/2026 | CARREFOUR MOE DUBAI | 412.35 | 12,067.65 | |
| 07/09/2026 | IPP TRANSFER FROM A KHAN | 2,500.00 | 14,567.65 | |
| 09/09/2026 | DEWA BILL PAYMENT | 587.20 | 13,980.45 | |
| 25/09/2026 | SALARY SEP 2026 WPS | 18,750.00 | 32,730.45 | |
| 28/09/2026 | EJARI RENT CHQ 000231 | 6,250.00 | 26,480.45 |
| Date | Description | Amount | Balance |
|---|---|---|---|
| 2026-09-02 | CARREFOUR MOE DUBAI | -412.35 | 12067.65 |
| 2026-09-07 | IPP TRANSFER FROM A KHAN | 2500.00 | 14567.65 |
| 2026-09-09 | DEWA BILL PAYMENT | -587.20 | 13980.45 |
| 2026-09-25 | SALARY SEP 2026 WPS | 18750.00 | 32730.45 |
| 2026-09-28 | EJARI RENT CHQ 000231 | -6250.00 | 26480.45 |
- Debit and Credit merged into one signed Amount
- Date column converted to ISO YYYY-MM-DD (DD/MM detected)
- 3 negative amounts as real negative numbers
Open this example in the live demoEdit the rows, paste your own table, toggle options. Nothing is uploaded.
How to fix it by hand in Excel
One formula
Add a column “Amount” next to them. In row 2, with Debit in C and Credit in D:
=N(D2)-N(C2)
N() returns 0 for an empty cell, so either column can be blank. If debits may already be negative or bracketed, use =N(D2)-ABS(N(C2)).
Check first: N() also returns 0 for numbers stored as text, which would silently zero those amounts. Run =ISNUMBER(C2) on a few rows; convert text with Text to Columns → Finish if needed.
Then replace the formulas with values
Copy the Amount column, Paste Special → Values, and delete the Debit and Credit columns.
How to fix it in one click with StatementTidy
- Install StatementTidy. Download the free extension and load it in Chrome, Edge or Brave.
- Open the statement. Any table with a debit-type and a credit-type column.
- Click the StatementTidy icon. Keep “Merge Debit / Credit into a signed Amount” on (it is on by default). A chip confirms the merge.
- Copy for Excel. You get one Amount column; the summary shows money in, money out and the net sum.
Get StatementTidy free for ChromeTry it on a sample statement
Questions
Which column names does it recognise?
Debit/Credit, Dr/Cr, Withdrawal(s)/Deposit(s), Money out/Money in, Paid out/Paid in, Outflow/Inflow, Payments/Receipts, German Soll/Haben, Spanish Cargo/Abono, Italian Addebito/Accredito, Dutch Af/Bij and Arabic مدين/دائن and سحب/إيداع, among others.
What if the table has a separate Dr/Cr indicator column?
If a column next to a single amount column holds only Dr/Cr (or D/C) markers, StatementTidy applies them as the sign and drops the marker column.
Is the balance column changed?
No. Balance stays a separate numeric column, so you can check the running balance against your new signed amounts.