Convert to Debit/(Credit) Amount by using Paste Special

Excel is one of the important tool for finance professionals. If I want to present anything or want to draft anything, my mouse directly hit Excel.

Today topic may be very much useful for the accounting guys, who post Journal Entries in the systems.

Most of the ERPs (Enterprise Resource Planning) allow the Journal Entry in the following way.

Particulars	Dr/(Cr) Amount
Cash a/c	$2,500.00
Income a/c	$(2,500.00)

But not in following way. However, sometimes we use this way to present the Journal Entry in the Excel.

Particulars	Dr Amount	Cr Amount
Cash a/c	$2,500.00
Income a/c		$2,500.00

As I said earlier, the presentation may be different from posting the entry into the system.

How to convert to Dr/(Cr) Amount from Dr Amount & Credit Amount

Or else, let me frame the question in this way that how to convert the second table data as first table data (referring above data).

I will take a real example and explain you further.

The entries in the Excel are as below.

Particulars	Dr Amount	Cr Amount
Cash a/c	$2,500.00
Income a/c		$25,000.00
Expense a/c	$4,900.00
Bank a/c		$4,900.00
Salaries a/c	$9,500.00
Telephone a/c	$800.00
Bank a/c		$10,300.00

Let say, the above data is in A1:C10 (including headers and blank cells)

Now, our aim is to present Debit and Credit Amounts in a simple column by putting positive for debit and negative for credit

1. Copy the Particulars column and paste in E column (E1:E10)
2. Type header in Cell F1 as Dr/(Cr) Amount
3. Copy B2:B10 and paste in F2
4. Copy C2:C10

5. Go to F2  - Right Click - Paste Special - Select Subtract from operations and check Skip blanks

6. That's All.

Then the final data in E1:F10 is as follows.

Particulars	Dr/(Cr) Amount
Cash a/c	$2,500.00
Income a/c	$(25,000.00)
Expense a/c	$4,900.00
Bank a/c	$(4,900.00)
Salaries a/c	$9,500.00
Telephone a/c	$800.00
Bank a/c	$(10,300.00)

Imagine if you have some 100's of Journal Entries in Excel. In that instances, we have to say 100 thanks to Paste Special options. Know more about Paste Special options from here

How do you solve this? Do have any alternative easier than this, please share to the world.

Get all these tips and tutorials directly into your mail box !!!

Subscribe to our newsletter