Clean a Tally Export in Excel (Dr/Cr, Blank Rows, Ledger Names)

📘 Excel for India Course · Lesson 6 of 7

Tally exports are designed for printing, not analysis: company name rows on top, blank lines, amounts like “1,25,000.00 Dr” stored as text, and ledger names only on the first row of each group.

The goal

One row per entry with Date, Voucher type, Voucher no., Ledger, and a single signed Amount column (debit positive, credit negative).

Amounts with Dr/Cr suffix

=LET(t,TRIM(B2), sign,IF(RIGHT(t,2)="Cr",-1,1),
     sign*VALUE(SUBSTITUTE(TRIM(LEFT(t,LEN(t)-2)),",","")))
"1,25,000.00 Dr" → 125000      "4,500.00 Cr" → -4500

If debits and credits are in separate columns instead: =N(Debit)-N(Credit).

Fill ledger names down

Select the ledger column → Home → Find & Select → Go To Special → Blanks → type = and press ↑ → Ctrl+Enter. Then copy the column and Paste Values.

Power Query (repeatable every month)

1. Data → From Table/Range (or From Workbook).
2. Remove top rows until the header row; Use First Row as Headers.
3. Filter out blank rows and “Total”/”Opening balance” rows.
4. Select the ledger column → Transform → Fill → Down.
5. Add a custom column for the signed amount, change type to Decimal.

Next month, drop the new export in the same place and click Refresh — no formulas to redo. See our Power Query course for the details.

Check the totals

The sum of the signed Amount column for a day book should be zero (every debit has a credit). If it isn’t, a row was lost or a Dr/Cr sign was misread.