Separate Debit and Credit Columns in Excel (Bank Data)

TL;DR To split one signed amount column into two, use =MAX(-C2, 0) for money out (debits on a bank statement) and =MAX(C2, 0) for money in (credits). To merge two columns back into one, use =N(Credit) - N(Debit). Before either, make sure the amounts are real numbers: convert "CR" and "DR" suffixes, bracketed negatives and trailing minus signs into proper signs first. Then check that the totals of each column match the statement's summary.

Bank statement data comes in two shapes. Some statements and exports use a single amount column with negative numbers for money out. Others use two columns, often labeled debit and credit, withdrawals and deposits, or paid out and paid in. Accounting software, templates and lenders each expect one shape or the other, so converting between them is one of the most common spreadsheet jobs in bookkeeping.

This guide covers both directions, the messy formats you'll meet along the way, the debit-and-credit terminology that trips people up, and how to check that nothing went wrong.

Bank debits and credits versus accounting debits and credits

Before any formulas, it's worth clearing up a confusion that causes real mistakes.

On a bank statement, the words are from the bank's point of view, applied to your account:

• A credit is money into your account (a deposit).
• A debit is money out of your account (a withdrawal or payment).

In double-entry bookkeeping, from your business's point of view, the cash account works the other way:

• Money coming into your bank account is a debit to cash.
• Money going out is a credit to cash.

Both are correct; they're just two different perspectives. The bank's account for you is a liability on its books, which is why its terms are reversed relative to yours.

For importing statements, follow whatever the importing software expects. Most bank import screens use the bank's convention: credit means money in. If you're unsure, test with two rows, one deposit and one withdrawal, and look at how they appear.

In this guide, "debit" and "credit" follow the bank statement convention unless stated otherwise.

Step 0: make sure amounts are numbers

Formulas can only split or merge numbers. Converted statements often contain text that looks like a number but isn't. Check with:

=ISNUMBER(C2)

If that returns FALSE, fix the format first. Here are the common cases.

Currency symbols and thousands separators

$1,234.56 stored as text. Remove the symbol and convert:

=NUMBERVALUE(SUBSTITUTE(C2, "$", ""))

NUMBERVALUE handles the comma as a thousands separator by default in most locales. For statements that use a comma as the decimal separator (1.234,56):

=NUMBERVALUE(C2, ",", ".")

CR and DR suffixes

Some statements print 125.00 CR for money in and 84.12 DR (or no suffix) for money out:

=IF(ISNUMBER(SEARCH("CR", C2)), 1, -1) * NUMBERVALUE(TRIM(SUBSTITUTE(SUBSTITUTE(C2, "CR", ""), "DR", "")))

If unsuffixed amounts are money in and only debits have a suffix, swap the logic to look for "DR".

Bracketed negatives

Accounting-style negatives like (84.12):

=IF(LEFT(TRIM(C2)) = "(", -1, 1) * NUMBERVALUE(SUBSTITUTE(SUBSTITUTE(C2, "(", ""), ")", ""))

Trailing minus signs

Some systems print 84.12-:

=IF(RIGHT(TRIM(C2)) = "-", -1, 1) * NUMBERVALUE(SUBSTITUTE(C2, "-", ""))

Hidden characters

Data copied from a PDF can include non-breaking spaces (character 160) that TRIM doesn't remove:

=NUMBERVALUE(TRIM(SUBSTITUTE(C2, CHAR(160), "")))

Our guide to cleaning bank statement data in Excel covers these and other cleanup steps in more detail.

Split one amount column into two

With a single signed column (positive money in, negative money out) in C:

Debit (money out):

=MAX(-C2, 0)

Credit (money in):

=MAX(C2, 0)

Both return a positive number or zero. If you'd rather have blanks than zeros, which some import tools prefer:

=IF(C2 < 0, -C2, "")
=IF(C2 > 0, C2, "")

Be careful with blanks returned as "": they're text, not empty cells. Some software treats them as empty and some rejects them. If an import complains, copy the columns and paste as values, then use Find and Replace to clear any remaining empty strings, or use zeros instead.

In an Excel table

With structured references, the formulas fill automatically:

=MAX(-[@Amount], 0)
=MAX([@Amount], 0)

With Excel 365 dynamic arrays

For a whole column at once:

=LET(a, C2:C500, HSTACK(IF(a<0, -a, 0), IF(a>0, a, 0)))

This spills two columns side by side.

Merge two columns into one

With money in (credit) in D and money out (debit) in E:

=N(D2) - N(E2)

N converts blanks and text to zero, so empty cells don't cause errors. The result is positive for deposits and negative for withdrawals.

If both columns contain numbers on the same row (which happens occasionally with combined transactions or data errors), the formula still works, giving the net. Flag those rows to check them:

=AND(N(D2) <> 0, N(E2) <> 0)

When both columns are positive and a type column says which

Some exports have one Amount column, always positive, and a separate Type column with values like "Debit" and "Credit":

=IF(B2 = "Credit", C2, -C2)

Check the exact wording of your Type column first; it might be "DR"/"CR", "Withdrawal"/"Deposit" or something else.

Credit card statements

Credit cards reverse the logic, because the balance is what you owe.

On a card statement:

• Purchases, fees and interest increase the balance.
• Payments and refunds reduce it.

If your card data has one column where purchases are positive, splitting gives:

Charges:  =MAX(C2, 0)
Payments: =MAX(-C2, 0)

When importing card data into accounting software, check what the software expects. Many tools want charges as money out (negative, or in the debit column) for a card account. Our guide to importing credit card transactions into QuickBooks Online walks through testing this.

Which shape does your software want?

Our guides to QuickBooks Online imports and the Xero CSV import format have the column details. When in doubt, test with a two-row file.

Check that nothing changed

Splitting and merging should never change the totals. After either operation, check three things.

Column totals match the statement summary. Most statements print total deposits and total withdrawals. Compare:

=ROUND(SUM(Credit) - TotalDepositsPerStatement, 2)
=ROUND(SUM(Debit) - TotalWithdrawalsPerStatement, 2)

Both should be zero. If deposits match and withdrawals don't, the problem is in the withdrawals column, which halves the search.

Net equals the change in balance.

=ROUND(OpeningBalance + SUM(Credit) - SUM(Debit) - ClosingBalance, 2)

Row count is unchanged. Each row should have a value in exactly one of the two columns (or a non-zero signed amount). Count rows where both are zero:

=COUNTIFS(Credit, 0, Debit, 0)

Anything other than zero means a row lost its amount, often because the original was text. The free balance checker handles the first two checks if you'd rather not write formulas.

Running balance with two columns

Once you have separate columns, a running balance works the same way as with one, you just subtract the debit:

=PreviousBalance + N(Credit) - N(Debit)

In an Excel table, a version that survives sorting and inserted rows:

=Opening + SUM(INDEX([Credit],1):[@Credit]) - SUM(INDEX([Debit],1):[@Debit])

Compare the result with the balances printed on the statement. The first row where they differ is where a transaction is missing or in the wrong column. Our guide to the running balance formula in Excel covers the comparison in detail.

Make the columns easy to read

Two columns full of zeros are harder to scan than they need to be. A custom number format hides zeros without changing the values, so totals and imports still work:

#,##0.00;-#,##0.00;""

The three sections are positive, negative and zero formats; the empty third section displays zero as blank. Apply it with Format Cells, Number, Custom.

A few more readability touches that help when sharing the sheet:

• Right-align both amount columns and use the same number of decimals everywhere.
• Freeze the header row so column names stay visible while scrolling.
• Use conditional formatting sparingly. A light fill on rows over a certain amount draws attention to large items without making the sheet look like a traffic light.
• Add totals in a row above the header, not at the bottom, so they don't get included when the table grows. Use SUBTOTAL(9, ...) so the totals respect any filter you apply.

Monthly totals of money in and money out

Separate columns make monthly summaries straightforward. With a Month column (for example =TEXT([@Date], "yyyy-mm")):

Money in:  =SUMIFS(Tx[Credit], Tx[Month], A2)
Money out: =SUMIFS(Tx[Debit], Tx[Month], A2)
Net:       =B2 - C2

Each month's money in and money out should match the statement's summary totals for that month, which gives you a quick cross-check across a whole year. Exclude transfers between your own accounts if you want a true picture of income and spending; see our guide to cash flow statements from bank statements.

A pivot table does the same job with less typing: Month in rows, Sum of Credit and Sum of Debit in values. Our pivot table guide shows the setup.

When someone asks for a specific layout

Lenders, landlords, auditors and accountants sometimes send their own template with columns in a particular order, such as Date, Reference, Description, Withdrawals, Deposits, Balance. Rather than rebuilding your workbook, keep your master table in whatever shape suits you and build an output sheet that references it:

1. Create the template's headers in the order requested.
2. Fill each column with a reference to your table, using =Tx[Date] style spill references in Excel 365, or simple cell references filled down.
3. Format the dates and numbers exactly as the template asks.
4. Copy and paste as values into the template file before sending.

The master table remains the single source of truth, and the output sheet can be regenerated for each request. For audits in particular, see our checklist on preparing bank statements for an audit.

Doing it in Power Query

If you clean the same kind of file every month, Power Query saves repeating the work. After loading the table:

1. Clean the amount. Add a custom column that removes symbols and converts to a number, or use Replace Values then Change Type to Decimal Number.
2. Split. Add two custom columns:

Debit  = if [Amount] < 0 then -[Amount] else 0
Credit = if [Amount] > 0 then [Amount] else 0

3. Or merge. One custom column:

Amount = (if [Credit] = null then 0 else [Credit]) - (if [Debit] = null then 0 else [Debit])

Next month, drop the new file in and refresh. See our Power Query bank statement guide for loading PDFs and CSVs.

Common mistakes

Splitting before cleaning. If some amounts are still text, MAX treats them as zero and the row silently disappears from both columns. Always confirm ISNUMBER is TRUE for every row first.

Flipping the wrong way. It's easy to put money in under Debit because that's how your books would record it. Decide which convention the destination wants, label the columns explicitly (for example "Money in (credit)"), and test with two rows.

Mixing card and bank data. A credit card's positive amounts are charges, a bank account's are deposits. Split them separately, with their own logic, before combining.

Leaving formulas in an import file. Some software reads the formula result, some doesn't. Paste as values before saving the CSV.

Losing the original. Keep the raw amount column, hidden if you like, so you can always trace a split value back to what the statement said.

A worked example

A bookkeeper receives a client's statement converted by copy and paste. It has a single Amount column with values like 1,250.00 CR, 84.12, (15.00) for a fee reversal and a few with trailing spaces. The client's accounting software wants separate Credit and Debit columns.

1. Diagnose. ISNUMBER returns FALSE on every row. The amounts are text.
2. Normalize. A helper column handles CR suffixes, brackets and spaces in one formula, nesting the substitutions. Every row now returns a signed number.
3. Check the sign logic. The statement summary shows total deposits of 6,410.00 and total withdrawals of 5,982.37. The helper column's positive total is 6,410.00 and its negative total is -5,982.37. Both match.
4. Split. Credit and Debit columns use MAX, with zeros.
5. Check again. Column totals still match; the count of rows with both zero is 0.
6. Paste values and import. The bookkeeper copies the Date, Description, Credit and Debit columns as values into a new CSV and imports it.

The whole process takes a few minutes once the formulas are set up, and they can be reused for the next client with the same format.

Avoid the problem at the source

Most of the cleanup above exists because PDFs are page layouts, not data. A converter built for statements, such as SheetStatement, outputs real numbers with a consistent sign, runs the balance check for you, and lets you export CSV, Excel, or QuickBooks and Xero formats in the shape the software expects. The free plan includes 3 pages a month, and Starter ($9), Pro ($19) and Business ($39) monthly plans cover larger volumes.

FAQ

How do I split positive and negative numbers into two columns in Excel?

Use =MAX(C2, 0) for positives and =MAX(-C2, 0) for negatives (shown as positive numbers). Use IF instead if you want blanks rather than zeros.

How do I combine debit and credit columns into one?

Use =N(Credit) - N(Debit). Deposits come out positive and withdrawals negative.

Is a bank statement credit a debit in my books?

From your bookkeeping point of view, money into your bank account is a debit to cash, even though the bank calls it a credit. For imports, follow the convention your software's import screen uses.

Why does my formula return #VALUE!?

The amount is text. Remove currency symbols, suffixes, brackets and hidden spaces, and convert with NUMBERVALUE first.

How do I handle CR and DR on amounts?

Detect the suffix with SEARCH, set the sign accordingly, remove the suffix with SUBSTITUTE and convert the rest with NUMBERVALUE.

Should I use zeros or blanks in the empty column?

It depends on the destination. Spreadsheets and most templates handle zeros fine. Some imports prefer blanks. Test with a small file.

How do I know the split worked?

Compare each column's total with the statement's total deposits and withdrawals, and confirm opening balance plus credits minus debits equals the closing balance.

Can I split amounts for several accounts at once?

Yes, as long as every account uses the same sign convention in the source column. Keep an Account column so you can total each account's money in and money out separately and check them against each statement.

How do I hide the zeros without deleting them?

Use the custom number format #,##0.00;-#,##0.00;"". Zeros display as blank but remain numbers, so totals and imports are unaffected.

Why do my totals change after I filter?

SUM always includes every row, filtered or not, so it shouldn't change. If a total does change, it's a SUBTOTAL or AGGREGATE formula, which only counts visible rows. Use whichever matches what you want to see.

Does this work in Google Sheets?

Yes. MAX, IF, N, SUBSTITUTE and SEARCH all work the same way. If NUMBERVALUE isn't available in your version, remove separators and symbols with SUBSTITUTE and convert with VALUE. See bank statement PDF to Google Sheets.