To split a signed amount into two columns, use =MAX(0,-C2) for the money-out column and =MAX(0,C2) for money in. MAX picks the larger of zero and the figure, so each amount lands in exactly one column and the other shows 0. A check column then proves nothing was lost.
Why would you need two columns?
Many accounting and import tools, including ones that ask for a debit and a credit field, expect two columns of positive numbers. Bank-statement exports and your own cleaned sheet often hold one signed column. Converting back is a short formula job.
What are the formulas?
Assume your signed amount is in C, with money out negative and money in positive. Put debit in D and credit in E.
- D2: =MAX(0,-C2). A payment of -2,350 gives 2,350. A credit of 52,000 gives 0.
- E2: =MAX(0,C2). A credit of 52,000 gives 52,000. A payment of -2,350 gives 0.
- Fill both down the column.
If your sheet runs the other way, with spending positive and refunds negative, swap them: debit =MAX(0,C2) and credit =MAX(0,-C2). Decide your convention first and write it in the sheet header so nobody confuses it later.
| Signed amount (C) | Debit (D) | Credit (E) |
|---|---|---|
| -18,000 | 18,000 | 0 |
| -2,350 | 2,350 | 0 |
| 52,000 | 0 | 52,000 |
| -500 | 500 | 0 |
| 1,299 | 0 | 1,299 |
Should the other column show 0 or stay blank?
Resist the urge to write =IF(C2<0,-C2,"") in order to get a blank. That puts an empty text string in the cell, which later returns #VALUE! when someone subtracts the columns. Keep the numeric zero and hide it with a number format: select the columns, press Ctrl+1, choose Custom and enter #,##0.00;-#,##0.00; so zeros display as nothing while the cell remains a number.
How do you confirm nothing was lost?
Run a check column beside the split, and a total check under it.
- Row check, in F2: =ROUND(E2-D2-C2,2). With money out negative, credit minus debit must equal the original, so this should be 0 on every row.
- If you prefer the other phrasing, debit minus credit equals the original when spending is positive: =ROUND(D2-E2-C2,2).
- Overall: =ROUND(SUM(E2:E500)-SUM(D2:D500)-SUM(C2:C500),2) should be 0.
- Count rows where both columns are non-zero: =SUMPRODUCT(--(D2:D500>0),--(E2:E500>0)). It should be 0 because one amount belongs to one side only.
Does a zero amount need handling?
A row with 0 goes to neither side, which is correct, but you may want to review it, because a zero in a statement is unusual. Filter column C for 0 and see whether it is a genuine line or a failed import.
Worked example with hypothetical figures
Say you have five signed amounts: -18,000, -2,350, 52,000, -500 and 1,299. Debits become 18,000, 2,350 and 500, which total 20,850. Credits become 52,000 and 1,299, which total 53,299. Credit minus debit is 32,449, and the sum of the original signed column is -18,000 - 2,350 + 52,000 - 500 + 1,299 = 32,449. They agree, so the split is clean.
What about Google Sheets?
The MAX formulas work unchanged. To fill a whole column from one cell, use =ARRAYFORMULA(IF(C2:C="","",IF(C2:C<0,-C2:C,0))) for debit and =ARRAYFORMULA(IF(C2:C="","",IF(C2:C>0,C2:C,0))) for credit. The empty-string results are fine here because the column is only read, not subtracted.
Keep the signed column as the master copy. If a figure changes later, change it there, and the two split columns will follow on their own.
Closing checklist
- Decide which sign means money out.
- Use MAX with zero for both columns.
- Hide zeros with a format rather than blanks from formulas.
- Add the row and total checks before exporting.
If the next step is an accounting tool, SpendixAI has an export centre with templates for Tally, Zoho Books and QuickBooks, which may save you the reshaping; check the template against what your accountant expects.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

