Spreadsheets

Split one amount column into debit and credit in Google Sheets

Split one amount column into separate debit and credit columns in Google Sheets using IF and ARRAYFORMULA, from a Dr/Cr marker or from signed amounts.

Editorial illustration for Split one amount column into debit and credit in Google Sheets

To turn one amount column into separate Debit and Credit columns in Google Sheets, put one array formula in the header cell of each new column. With amounts in C and a Dr or Cr marker in D, the Debit column is ={"Debit";ARRAYFORMULA(IF(C2:C="","",IF(D2:D="Dr",C2:C,"")))}, and the Credit column is the same with "Cr". The columns then fill themselves for every new row.

What does the formula actually do?

Say your data starts in row 2, with date in A, narration in B, amount in C and the Dr/Cr marker in D. In E1 you enter the Debit formula above. The curly brackets build a small array: the word Debit becomes the header, and the semicolon stacks the ARRAYFORMULA results beneath it. The inner IF leaves a row blank when the amount is blank, shows the amount when the marker says Dr, and shows nothing otherwise. Comparisons are not case sensitive, so DR and dr also match.

How do I set up both columns?

  1. Clear everything in columns E and F below row 1. An array formula needs empty cells to spill into, otherwise Sheets shows a #REF! error.
  2. In E1 enter ={"Debit";ARRAYFORMULA(IF(C2:C="","",IF(D2:D="Dr",C2:C,"")))}
  3. In F1 enter ={"Credit";ARRAYFORMULA(IF(C2:C="","",IF(D2:D="Cr",C2:C,"")))}
  4. Check the first few rows against the original statement.

If your Google Sheets locale uses semicolons as argument separators, replace the commas that separate the arguments with semicolons. Sheets flags the formula if it does not like what you typed.

What if the amounts are signed instead of marked Dr or Cr?

If debits are negative, use these:

  • Debit: ={"Debit";ARRAYFORMULA(IF(C2:C="","",IF(C2:C<0,-C2:C,"")))}
  • Credit: ={"Credit";ARRAYFORMULA(IF(C2:C="","",IF(C2:C>0,C2:C,"")))}

The minus sign in the debit formula turns the negative amount into a positive one, which is how most people want to read a Debit column. Confirm the convention on a transaction you remember.

What if the statement already has two columns, withdrawal and deposit?

Then you may not need to split. Your problem is the opposite: merging them into one signed amount. Blank cells are the catch, because an empty withdrawal or deposit cell should count as zero. This version handles them: =ARRAYFORMULA(IF(A2:A="","",IF(F2:F="",0,F2:F)-IF(G2:G="",0,G2:G))), where F is withdrawal and G is deposit.

Worked example with made-up rows

NarrationAmount (₹)MarkerDebit (₹)Credit (₹)
Rent transfer18,000Dr18,000blank
Salary credit45,000Crblank45,000
Electricity bill2,150Dr2,150blank

Total debits are ₹18,000 + ₹2,150 = ₹20,150, which =SUM(E2:E) returns. Total credits are ₹45,000. SUM ignores the blank text cells.

Can I sort the sheet afterwards?

Be careful. If you sort rows with the formula columns included, array formulas can break or complain. Select only the columns that hold typed or pasted data, sort those, and let the formula columns recalculate beside them. Alternatively, copy the formula columns and paste as values only before sorting, if you do not need them to update.

After the columns exist, give each its own total at the top of the sheet. A simple pattern is =SUM(E2:E) for debits and =SUM(F2:F) for credits, placed in a summary tab that refers to the data tab. Then add a net check, credits minus debits, and compare it with the change in balance shown on your statement for the same period. If the two differ, a row is missing or marked wrongly.

When a sheet stops being enough

When you import many statements a year, building and checking these columns becomes a chore. SpendixAI imports statements as PDF, CSV or Excel, lets you review the rows before saving, and keeps each transaction against the account you picked, so debits and credits are not something you reassemble by hand.

Checklist before you trust the columns

  • Columns below the formula cells are empty.
  • Spelling of Dr and Cr matches the statement.
  • Debits and credits add up to the totals printed on the statement.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports