How to remove Debit and Credit in Excel? | Make Credit value negative in Excel

Sometimes you may get an Excel file where Debit and Credit values are in the same column with special formatting.

This is the usual case when you export files from Tally software.  

Due to this type of data arrangement, one may find it difficult and time-consuming to separate debit and credit values or sum only debit or credit values or make credit values negative.

Here I am sharing solution to make things fast and easy for the above issues.

Remove Debit and Credit and make credit negative

To remove formatting and to make credit value negative, open VBA IDE by pressing ALT+F11.

In VBA IDE create one standard module by clicking on Insert tab then click on Module.

In the newly created Standard Module paste the below VBA code.

Notice below program (sub) name is s_makeCredit_Negative

Sub s_makeCredit_Negative()
''removes special formatting and make Cr values -ve in selection
 Dim rng As Range, cel As Range
 Set rng = Selection
 For Each cel In rng
    If InStr(1, cel.NumberFormat, "Cr", vbTextCompare) <> 0 And cel.Value > 0 Then
      cel.Value = cel.Value * -1
    End If
 Next cel
 rng.NumberFormat = "General"
End Sub

Now select range then press ALT+F8 shortcut key to bring macros pane (Click on Developer tab then click Macro option).

Select “s_makeCredit_Negative” option then click on the Run button.

You can also read Various ways to run VBA (macro) in Excel

After running the program all values will get converted into the general format and credit values become negative.

Sum only Debit and Credit values in Excel

After converting credit values into negative values you can easily sum credit and debit values separately using the Sumif function.

Below is the sum is formulas used in cell.

Example Cell A11 formula is =SUMIF(A2:A8,”>0″,A2:A8)

You may also like to learn the below topics

How to insert and run VBA code in Excel?

How to lock or protect specific cells (range) in Excel?