Sage™ Debit and Credit columns: combine them into one signed amount in Excel™
Accounting exports often show money in two columns, Debit and Credit, with one of them empty on each row. Many imports and most analysis want a single signed amount instead. Combining them is easy to do wrongly, and a wrong sign flips a row's meaning.
Decide the sign first
Pick one convention and write it down: for example, debits positive and credits negative. Then apply it to every row. Check which convention the destination file expects before you commit, since it differs between files.
Combine by hand
These steps apply to Excel™ in Microsoft 365. Work on a copy.
- Check both columns are numbers, not text. Select each and look for a sum in the status bar.
- In a new column, enter
=N(C2)-N(D2), where C is Debit and D is Credit.Nturns a blank or text cell into zero so the formula does not error. - Fill down.
- Paste as values, delete the original two columns, and name the new column Amount.
- Check: the sum of the new column should equal total Debit minus total Credit from before. If it does not, a row held text.
- Check a few rows against the original by eye.
If a row has both columns filled, stop and look. It may be a correction or a mistake.
Let the pane do the conversion
Import Cleaner for Sage™ handles this as one of its cleaning checks (SE-05: Debit and Credit columns to one signed amount), with a preview of the exact cell changes before anything is written. The scan, every explanation and Undo are free; applying the fix is the paid part. It does not connect to Sage™ and your workbook is not uploaded.
See Import Cleaner for Sage™ for Excel™
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Sage is a trademark of The Sage Group plc. Import Cleaner for Sage is an independent product, not affiliated with or endorsed by Microsoft or The Sage Group plc.