Convert tally exported cr. as (-) negative in excel

In general tally data(say Trial Balance) exported as Dr. or Cr. in excel. However for reporting purpose often there is requirement to represent Dr. amount as positive and Cr. amount as negative like (-). Is there any formula or technique available which can convert Dr. or Cr. as positive or negative?

Total likes : 1 times

CA in Practice

Firstly detach the "Dr." and "Cr." from amount and take them into separate Column.

Then you can use "IF" function to convert the amounts in Negative or Positive.



Prateek, thanks for your reply. However the problem is that though Dr. or Cr. visible in excel however while selecting the respective cell it only shows the numeric amount. So that the steps you have mentioned cann't apply here. For your understanding I am attaching herewith the excel file. Pls go thru the same.

Attached File : 82690 20151223131821 tally dr cr.xlsx downloaded 1393 times
CA in Practice

The process would be a bit lengthy but you can do it as follows - 

1. Write any number (eg. 0) in each a column against each item. [Column F in attached File]. Number in each cell of column should be same.

2. Copy the Column in which numbers with Dr of Cr are written. [Column B in file]

3. Use "Paste Special" and Paste "formats" in the column in which "0" is written. [Column F]

4. Copy and Paste the Column B again in 2 more columns. 1 for Dr. another for Cr. [Column H and Column I in file]

5. Now apply "Filter" on column F. First select Dr Entries only. Select the Cr Column (i.e. column I in file) > Select visible cells only using "Go To" Command. Now only Dr Entries are selected. Delete them.

6. Similarly Now change the filter to select Cr entries only. Select the Dr Column (i.e. column H in file) > Select visible cells only using "Go To" Command. Now only Cr entries are selected. Delete them.

7. Remove filter. Now you have Dr. Entries only in Dr Column and Cr, entries only in Cr column.

8. Put formula in next column for "Dr. Column - Cr Column". You will have Cr. entries in negative.

Attached File : 290809 20151223190109 82690 20151223131821 tally dr cr.xlsx downloaded 1167 times

Total likes : 1 times


Thanks for your effort. In case of compilation of bulk data it would be quite tidious and time consuming. If there any formula can be used it would be more convenient as  well effective. I am looking for any formula. Please take a note that I need to represent various sheets removing Dr. /Cr. and place the amount in as it is transaction format like Opening Balance, Transaction(Dr.)/Cr, Closing Balance only the positive & negative number. The method you have mentioned requires more manual work and time consuming. Only any suitable formula can perform well. However thanks for your effort.

CA in Practice

In case you haven’t tried the process i would suggest you to try it for once. It is looking too lengthy only in written instructions. Once you understand the process it won’t take more than 5 minutes, irrespective bulkiness of your data.

There can’t be a single formula for this and you can only be able to do it with a series of commands.


Secondly, if you got the figures with negative and positive in a column you can simply copy and paste them anywhere.


Thanks & Regards

Total likes : 1 times


Ya finally I have managed a function. Using such funtion Dr. or Cr. amount can be shown as positive or (-) negative. I would like to share here the  step by step procedure which may be useful for the forum members. 

Step1: Write the function: In excel file contains of Dr. or Cr. figure, please Right Click on any sheet-----> select View Code---> Right Click below the area of VBA project(blank area in left side)--->Click on Insert--->Select Module. Now write the function as follows:

Function CreditDebit(myCell As Range) As Double
Dim strVal As String
Dim myMult As Integer

strVal = myCell.Text

If Right(strVal, 2) = "Cr" Then
    myMult = -1
    myMult = 1
End If

CreditDebit = myCell.Value * myMult

End Function

Close the window

Now the function CreditDebit is ready which will convert Dr or Cr amount as positive or negative

Step2: Now if you write the function as =CreditDebit in any cell refering the cell contain amount with Dr or Cr it will convert the amount as positive or negative.

For example in attached file let you type the function in cell G7 =CreditDebit(B7) from where data to be converted. Here we need to copy the entire data in G7:J33. So select the range from G7:J33-----> Press F2 and CTRL+ENTER, it will copy the function in entire range.

The above function will covert Dr. or Cr.  as positive or negative.

Now you can hide column B to E to show the converted data. Or may copy the value of converted data and paste in your desired reporting range.


Attached File : 82690 20151226191509 tallycredit debit.xls downloaded 922 times

Total likes : 1 times

General Manager - Finance & Accounts

Thank you for posting such a valuable information

Accounts Manager

You may try this. Go to format - Numbers - make the entire Cr cell in Red color they try your OB+Dr - Cr+ end Balance.It may work out


You can copy the data from MS Excel to MS word to convert Dr/Cr into +/- by replacing the same & then copy that data back to your excel file..
No hassles of using vba. :)

Total likes : 2 times



Your are not logged in . Please login to post replies

Click here to Login / Register