Indian Bank Statement to Excel: Dr, Cr and Lakh Commas

To convert an Indian bank statement to Excel, turn the Dr (withdrawal) and Cr (deposit) columns into one signed amount, strip lakh-style commas such as 1,00,000 so amounts become numbers, and read dates as day first. Keep UPI and NEFT narrations whole, then check that the opening balance plus every amount equals the closing balance.

Dr and Cr columns on an Indian bank statement

On an Indian bank statement, Dr marks a debit, money taken out of your account, and Cr marks a credit, money paid in. Statements usually print them as two amount columns, often headed Withdrawal (Dr) and Deposit (Cr), with only one filled on each line. Your sheet should turn them into one signed amount.

Why the labels feel backwards

Dr and Cr follow the bank's own books, where your deposit is money it owes you, so a salary payment is a credit and an ATM withdrawal a debit. Why that looks reversed from a bookkeeping view is explained in why a bank statement credit looks reversed to a bookkeeper; in your sheet, credits are positive and debits negative.

One column or two

You can keep the two columns if you like reading the sheet as a passbook. A single signed column is easier to sum and filter. With the debit in column C and the credit in column D, the signed amount is =N(D2)-N(C2). N turns an empty cell into zero, so the same formula works on every row.

Statements with one amount column and a marker

Some layouts print one amount column with Dr or Cr written after each figure, such as 2,350.00 Dr. Split the marker off with a formula like =IF(RIGHT(TRIM(C2),2)="Dr",-1,1) and multiply it by the number. The next section shows how to get the number itself out of a figure with lakh commas.

Lakh commas like 1,00,000 in an Indian statement

The Indian numbering system groups digits differently: the last three digits form one group, and every two digits before them form another. One lakh is 1,00,000 and ten lakh is 10,00,000. Excel set to a US or UK locale may read such text as text, not a number, so remove the commas before converting.

Getting a number out of the text

If an amount lands in Excel as text, it sits on the left of the cell and SUM ignores it. Two ways fix it:

1. Find and replace. Select the amount columns, replace every comma with nothing, then use Text to Columns with no delimiters to make Excel re-read the values as numbers.
2. A formula. In a helper column, =VALUE(SUBSTITUTE(TRIM(C2),",","")) returns the number. Wrap it in IFERROR to leave blank cells blank.

Both work because the comma is only a display mark. 1,00,000 and 100,000 are the same number, one lakh, written two ways.

Showing lakh grouping in Excel

Once the values are numbers, you can still display them the Indian way. A custom number format such as [>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00 shows 125000 as 1,25,000.00. It only changes how the cell looks. The stored value stays a plain number, so formulas behave normally.

Crore and lakh in words

Narrations and summary boxes sometimes write amounts in words, such as Rs 1.25 lakh or 2 crore. Those are for reading, not for adding. Keep the numeric columns as the only source for totals.

Why do Indian statement dates turn into the wrong month?

Indian statements write dates day first, as 05-08-2026 for 5 August 2026. If Excel is set to a US locale, it reads month first, turning that date into 8 May. Dates where the day is above 12 are left as text, so the column becomes a mix of wrong dates and unreadable ones.

Spotting the problem

Sort the date column. If 13-08-2026 sits at the bottom as left-aligned text while 05-08-2026 is shown as May, Excel guessed month first. Another sign is a month with dates scattered across January to December when the statement covers one month.

Fixing it on import

The cleanest fix is to tell Excel the order before it guesses. When opening a CSV, use the import steps rather than double-clicking the file, and set the date column to DMY. For values still held as text, select the date column, open Text to Columns, skip to the last step, and choose Date with DMY. Excel then reads them as day, month, year. Dates Excel has already turned into the wrong month are safer to fix by importing the file again with DMY set.

Short dates and month names

Some statements print 05 Aug 26 or 05-Aug-2026. Month names avoid the order problem, but a two-digit year can be read as the wrong century in rare settings. Check one row near the start and one near the end of the statement after conversion. The guide to date formats in a bank statement CSV explains how to keep dates in a form any program reads the same way.

UPI, NEFT, IMPS and RTGS narrations in Excel

The narration on an Indian bank statement often starts with the payment rail: UPI, NEFT, IMPS or RTGS, followed by a reference number and the other party's name, joined by slashes or hyphens. Keep the full narration in one column, then pull the rail and the payee into their own columns with formulas.

What the parts are

UPI is the instant payment system behind most phone payments in India. NEFT and RTGS are bank-to-bank transfer systems, and IMPS is an instant interbank transfer service. The rail tells you how the money moved. The reference number is what you quote to the bank in a dispute. The payee name tells you who it was. Each bank arranges these parts in its own way, so look at your statement before writing formulas.

A worked example

The lines below are made-up examples in a common style. The opening balance for the month is 2,15,400.00 Cr.

After the fixes above, the sheet holds dates of 2026-08-05, 2026-08-07, 2026-08-12 and 2026-08-28, and signed amounts of 125000, -2350, -40000 and -250000. The sum is -167350. The opening balance of 215400 plus -167350 gives 48050, which matches the closing balance of 48,050.00 Cr.

For a rail column, =LEFT(B2,FIND("/",B2)-1) returns NEFT, UPI, IMPS or RTGS. A pivot table by rail then shows how much moved by each route in the month, which is handy if you want to see how much of your spending goes through phone payments.

Keep the reference number as text

If you split the reference number into its own column, Excel may treat a twelve-digit number as a value and show it in scientific notation, or cut its last digits. Set that column to Text before splitting. Stopping Excel from switching to scientific notation covers the settings that keep long references intact.

Do Indian statement balances carry Dr and Cr too?

Often, yes. A balance marked Cr means the bank owes you money: a normal positive balance in a savings or current account. A balance marked Dr means you owe the bank, as on an overdraft or a cash credit account. Convert Cr balances to positive numbers and Dr balances to negative ones.

Converting the balance column

Use the same pattern as for amounts: strip the commas, take the number, and apply a sign from the marker. =VALUE(SUBSTITUTE(LEFT(E2,LEN(E2)-3),",",""))*IF(RIGHT(E2,2)="Dr",-1,1) handles a balance like 48,050.00 Cr, assuming one space before the marker. Check the result on a few rows, since spacing varies.

Using the balance as a check

With signed balances in place, every row can be checked: the previous balance plus this row's amount must equal this row's balance. A row that fails shows exactly where a line was missed or a sign was read wrongly. On an overdraft account the balance may cross from Cr to Dr in the middle of the month. The signs make that crossing visible, where the printed figures alone just look like a big number that went down and then up.

FastBankConvert is being built to read statement layouts like these and to tell you before download when a balance does not follow from the lines. It is getting ready to open, and the FastBankConvert home page describes what it will do.

Indian statement PDFs with passwords and long reference numbers

Two problems catch people with Indian statements: the PDF arrives protected by a password, and long reference numbers or account numbers get mangled in Excel. The first stops conversion entirely. The second does its damage quietly after conversion, so check for both before trusting the sheet.

Password-protected statements

Statements sent by email are often protected with a password, and the bank's email usually explains how that password is formed. Open the file in a PDF reader with the password, then save or print an unprotected copy for conversion. Store that copy carefully, since it now opens for anyone who finds it.

Account numbers in the header

The account number at the top of the statement is long and should be text, not a number. Leading zeros vanish and long values round if Excel treats them as numbers. Type an apostrophe before the number or set the cell to Text first.

Statements from other countries

The same habits help with statements from elsewhere, though each country has its own quirks. Converting a UK bank statement to Excel covers pound signs and day-first dates in British layouts, and converting a Canadian bank statement to Excel covers bilingual statements and accounts held in two currencies.