To combine debit and credit columns in Excel, add a column with =N(C2)-N(B2), where B holds debits and C holds credits. Money in comes out positive and money out negative. To split one signed column into two, use IF to send negatives to the debit side and positives to the credit side, returning "" for the other.
One signed amount or separate debit and credit columns?
The layout you need depends on where the data goes next. Budgeting apps, pivot tables and most CSV imports expect one signed amount per row. Ledgers, some accounting imports and anyone reading the sheet like an old cash book expect two columns, debits and credits, with one of them empty on every row.
Bank statements print both styles. Many checking statements show Withdrawals and Deposits as two columns. Others show a single Amount with a minus sign or brackets on money out. A converter usually keeps whatever the PDF printed, so the file you get may not match the shape your next step wants.
A quick way to decide:
- One signed column is easier to total, sort and chart. A single SUM gives the net movement for the period, and a filter on "less than 0" shows every payment.
- Two columns are easier to read and to check against a printed statement, because each figure sits where the bank put it. They are also what a double-entry journal expects.
You can keep both. Build the signed column from the two, or the two from the signed one, and hide whichever you do not need for a given job. The formulas below work in either direction.
A formula to combine debit and credit columns into one amount
With debits in column B and credits in column C, put =N(C2)-N(B2) in D2 and fill it down. N turns an empty cell into 0, so a row with only a debit returns a negative number and a row with only a credit returns a positive one.
Plain =C2-B2 works when the empty cells are truly empty. It fails with #VALUE! if an empty-looking cell holds a space or an empty string, which converted files often do. N treats text as 0, so the formula keeps going.
That tolerance has a cost. If a debit cell holds "1,250.00" as text, N also reads it as 0, and the row comes out as zero instead of an error. The section on text cells below shows how to catch that before you rely on the result.
Statements that print debits as negatives already
Some statements put a minus sign in front of every debit, even in a separate column. Subtracting a negative turns it positive, so the combined amount comes out wrong for every payment. Check one debit cell first. If it already reads -62.18, add instead: =N(C2)+N(B2).
Excel tables
If the data is an Excel table (Insert, Table), the formula reads more clearly with column names: =N([@Credit])-N([@Debit]). It fills the whole column on its own and extends when you add rows.
Splitting a signed amount column into debit and credit
Splitting a signed amount sends each value to one of two columns based on its sign. With the amount in D2, put =IF(D2<0,-D2,"") in the debit column and =IF(D2>0,D2,"") in the credit column. Debits come out as positive numbers, which is how a ledger shows them.
If you would rather keep the minus sign on the debit side, drop the leading minus: =IF(D2<0,D2,""). Pick one convention and keep it across the workbook, because a later SUM of the debit column flips sign depending on the choice.
A zero amount lands in neither column under these formulas. That is usually right: a zero line is an information row, such as a notice of a rate change. If you want zeros to show, change one comparison to >=.
When the source amount uses brackets for negatives, such as (62.18), convert it to a real number first. Brackets read as text in a converted file, and IF will compare text with zero in ways that look random. Running Data, Text to Columns on the column and pressing Finish makes Excel re-read each cell as if typed, and a typed (62.18) becomes -62.18.
Keeping empty debit and credit cells empty instead of zero
Zeros appear when a formula returns 0 for the side that does not apply, or when the source cell is a formula rather than truly empty. Return "" instead of 0 in your IF formulas, and the unused side stays visually blank. Formats can hide any zeros that remain.
There are three ways to get a clean look, and they behave differently:
- Return "" from the formula. The cell shows nothing. SUM skips it. COUNT skips it. COUNTA and COUNTBLANK treat it in surprising ways, because the cell holds an empty string, not nothing.
- Custom number format. Select the column, press Ctrl+1, choose Custom, and enter
#,##0.00;-#,##0.00;;@. The empty third section hides zeros while the cells keep their 0 values. - Excel option. Under Options, Advanced, untick "Show a zero in cells that have zero value". This hides zeros on the whole sheet, including ones you might want to see.
For data that leaves this sheet, the first route is the safest. A zero hidden by a format or by the Excel option is still 0 underneath, so a pivot table, a COUNT or a formula elsewhere still sees a debit of 0 on every credit row. An empty string is not a number at all.
What sign should a credit card statement's payments take?
On a credit card statement, purchases increase what you owe and payments reduce it, so the right sign depends on what you are tracking. With charges in column B and payments in column C, the bank-account formula =N(C2)-N(B2) makes purchases negative and payments positive.
Whether that is wrong depends on the question you are asking. To track your spending, you want purchases negative, like money leaving a checking account. To track the card balance, you want purchases positive, because each one adds to the amount owed.
For spending, keep that formula. To track the card balance, swap the order: =N(B2)-N(C2), so each purchase adds to the amount owed. Either way, label the column so the sign is not a guess for the next person, such as "Amount (negative = spent)".
Refunds follow the same logic as payments on a card. They reduce the balance, so they sit in the credit column. A refund and a payment look the same in the numbers, and the description is the only way to tell them apart.
Debit and credit cells stored as text after conversion
Debit and credit cells often arrive as text after a conversion, even when they look like numbers. A text amount breaks the combining formula in one of two ways: plain subtraction returns #VALUE!, and the N version quietly treats it as zero. Test before you trust either.
Run two checks on each source column:
=SUMPRODUCT(--ISTEXT(B2:B200))counts text cells in the debit column. Anything above zero needs fixing. Empty strings count here too, so compare the count with the number of rows that should be blank.=COUNT(B2:B200)counts real numbers. It should equal the number of rows that show a debit on the statement.
The usual causes are a currency symbol, a hidden space or a line break inside the cell. Line breaks are common when the PDF wrapped a long description into the amount area, and removing line breaks from Excel cells shows the CLEAN and SUBSTITUTE fixes. A cell where the converter misread a character, such as an O for a 0, will not convert by any formula; what to do about missing or wrong letters after a PDF to Excel conversion covers how to find those.
Scanned statements raise the odds. When text recognition reads a page, a column border can drift and drop digits into the next column. Reading set regions of the page, as described in how zonal OCR reads fixed areas of a page, is one way converters keep debits and credits apart on scans.
Checking the combined amount column against the running balance
The running balance on the statement is the test of every sign. Start from the opening balance, add each combined amount, and compare your total with the balance the bank printed on the same row. A match on every row proves the signs, the blanks and the text fixes are all right.
A worked example
The table below shows five example rows with an opening balance of 1,000.00. The figures are made up for illustration.
| Row | Debit | Credit | Combined =N(C)-N(B) |
Printed balance | Running check | Match? |
|---|---|---|---|---|---|---|
| 2 | 62.18 | -62.18 | 937.82 | 937.82 | Yes | |
| 3 | 2,310.00 | 2,310.00 | 3,247.82 | 3,247.82 | Yes | |
| 4 | 1,450.00 | -1,450.00 | 1,797.82 | 1,797.82 | Yes | |
| 5 | 18.40 | -18.40 | 1,779.42 | 1,779.42 | Yes | |
| 6 | 1.04 | 1.04 | 1,780.46 | 1,780.46 | Yes |
The running check in row 2 is =1000+D2, and from row 3 down it is =F2+D3, the previous check plus this row's amount. The match column is =ROUND(F2-E2,2)=0, which avoids false mismatches from floating-point leftovers in the last decimal place.
Reading a mismatch
When a row fails, the size of the gap tells you what went wrong:
- Gap equals twice the amount. The sign is flipped on that row, usually because the value sat in the wrong column.
- Gap equals the amount. The amount was read as zero, which points at a text cell that N skipped.
- Gap appears once and carries down. A row is missing or duplicated above that point.
Fix the first failing row, and every row below usually snaps back into line.
If you do this every month, the same steps are worth building once in Power Query, where each step reruns on next month's file. Cleaning a PDF import with Power Query steps shows how to set that up. FastBankConvert is getting ready to open; it is built to check that balances add up and tell you before you download if they do not, and you can join through Get started.