Helpful Accounting Excel Tip: Convert 2 Columns to 1 Column with Sign Change

www.nonprofitta...
Sometimes it's necessary to consolidate financial transactions from having debits and credits in separate columns, to combining them into one column and changing the sign on the credits to negative. This video shows you how.
1. Add a New Column for Adjusted Debits:
- Insert a new column next to your existing debit and credit columns. This will be where your combined values will appear.
2. Apply a Formula to Combine Debits and Credits:
- Assume your debits are in column A and your credits are in column B. In the new column (let's say C), in the first row of data (assuming row 2 if row 1 has headers), enter the following formula:
=A2 - B2
Note: if you want the debits to be negative and the credits positive, just reverse the formula (=B2-A2). Of course you will need to enter the formula and reference the cells according to your worksheet. Probably you won't be using A2 and B2.
- This formula takes the debit amount and subtracts the credit amount (since credits are to be shown as negative, subtracting them effectively changes their sign).
3. Copy the Formula Down:
- Drag the fill handle (small square at the bottom right of the cell) down through the column to apply the formula to all other rows in your data.
4. Optional - Replace Original Debit Column:
- If you want to replace the original debit column with this new adjusted column, you can copy the entire new column (Column C), and then use "Paste Special" to paste values only over the original debit column (Column A). This action replaces the formulas with their calculated values.
- After pasting, you can delete the original credit column and the new column you created for calculation, leaving only the updated debit column.
5. Finalize Your Worksheet:
- You can now delete the credit column as all the information is consolidated into the debit column. Make sure to check the accuracy of the data transfer before deleting any data permanently.
This approach will leave you with one column where all debits are positive, and credits are negative, as required.