Understanding Debit/Credit Sign Conventions Before You Begin
Before applying any separation formula in Excel, you must verify the sign convention used by your bank export. Financial exports typically follow one of two conventions:
• Standard Bank Statement Convention: Positive numbers denote money deposited into your account (Credits / Inflows), while negative numbers (prefixed with a minus sign -) denote money paid out (Debits / Withdrawals).
• Credit Card / Merchant Statement Convention: Purchases and fees are often exported as positive numbers (charges increasing your balance), while payments and refunds are exported as negative numbers.
In this guide, our formulas assume the standard bank account convention where deposits are positive (e.g., +2,500.00) and withdrawals are negative (e.g., -84.50). If your bank exports withdrawals as positive numbers and deposits as negative numbers, simply reverse the comparison signs in the formulas below.
Always check that your raw amount column contains real numeric values rather than text strings. If cells contain currency symbols ($ or £), trailing spaces, or European comma decimals, convert them to numbers first so logical comparisons (>0) evaluate accurately.
Method 1: Splitting Amounts with the IF Function (All Excel Versions)
The IF function works in all versions of Microsoft Excel (from legacy Excel 2007 through Microsoft 365). Assuming your raw transaction amount is in cell C2:
In your new Deposits column (Column D):
=IF(C2>0, C2, "")In your new Withdrawals column (Column E), convert negative withdrawals into positive accounting numbers using a minus sign or the ABS function:
=IF(C2<0, -C2, "")Using empty quotes ("") ensures non-applicable cells remain cleanly blank rather than displaying 0.00, keeping your worksheet readable and preventing unintended zeros in averaging formulas.
Method 2: Zero-Fill Separation Using MAX and MIN
If your accounting database or ERP software requires zeros rather than blank cells when importing numeric columns:
For Deposits (Cell D2):
=MAX(0, C2)For Withdrawals (Cell E2), taking the absolute value of the minimum extracts the outflow as a positive number:
=ABS(MIN(0, C2))Handling Statements with "CR" and "DR" Indicators
Many UK, Australian, and Canadian commercial banks export unsigned positive numbers in Column C with a transaction direction indicator ("CR" for credit/deposit or "DR" for debit/withdrawal) in Column D.
To separate these without circular references, place your output formulas into new adjacent columns (Columns E and F):
In your new Deposits column (Column E, cell E2):
=IF(UPPER(TRIM(D2))="CR", C2, "")In your new Withdrawals column (Column F, cell F2):
=IF(UPPER(TRIM(D2))="DR", C2, "")Wrapping D2 in TRIM and UPPER protects against erratic bank exports that contain leading spaces or lowercase indicator codes ("cr", "dr").
Method 3: Power Query Conditional Columns (Excel 2016, 2019, 2021, M365)
For recurring monthly statements or multi-thousand row files, Power Query automates the separation without manual formula dragging:
1. Select your table and go to Data > From Table/Range.
2. In the Power Query editor, select Add Column > Conditional Column.
3. Name the column "Deposit", and set: If Amount is greater than 0, then Amount, else null.
4. Add a second conditional column named "Withdrawal": If Amount is less than 0, then Amount, else null.
5. Transform the Withdrawal column by selecting Transform > Standard > Absolute Value to make withdrawals positive.
6. Click Home > Close & Load to output the separated columns into your worksheet.
Performing an Arithmetic Consistency Check Against the Statement Ending Balance
Before importing your data into accounting software, perform an arithmetic consistency check against printed statement checkpoints:
=OpeningBalance + SUM(DepositsRange) - SUM(WithdrawalsRange) - ClosingBalanceImportant Audit Limitation: If this formula evaluates to exactly 0.00, your debit/credit separation is mathematically consistent with the bank's opening and closing balances. However, an arithmetic balance check does not guarantee row-level completeness or correctness—it cannot detect mutually offsetting errors (such as an omitted deposit paired with an omitted withdrawal of the same amount). Always spot-check individual transactions against the official PDF statement.
Convert Bank Statement PDFs Cleanly with PDFtoRow
Tired of fixing broken columns, corrupted dates, and numbers stored as text? PDFtoRow extracts transaction tables directly from digital PDF vector layers, preserving check numbers and outputting true numeric amounts. Statements up to 5 pages are free once per email every 24 hours. Check the 10-row preview before you download.
Frequently Asked Questions
When stored in a single combined amount column, withdrawals are typically negative. However, when split into two dedicated columns (Deposits and Withdrawals), accounting convention and software importers (like QuickBooks and Xero) require both columns to contain positive values.
If SUM returns zero, the original amount column was formatted as text strings rather than real numbers. Convert the original column to numbers using the VALUE function or Text to Columns before applying the split formulas.
Primary Evidence & Official References
- Microsoft Support: IF Function — support.microsoft.com (Verified 2026-10-19). Official source documentation →
- Microsoft Support: Add a Conditional Column in Power Query — support.microsoft.com (Verified 2026-10-19). Official source documentation →