Bank Statement to Excel: Convert, Prove the Balances and Import to Tally

A bank statement to Excel conversion is finished only when the rows add back to the balances your bank printed. Opening balance plus credits, minus debits, must equal the closing balance to the paisa. Every running balance must follow from the row above it, and every date must sit inside the statement period. Skip those checks and the spreadsheet looks right but posts wrong entries into your books.

This guide shows Indian businesses, CA firms and finance teams how to take a bank statement to Excel and on into Tally, Zoho Books or QuickBooks: the three routes, the six traps that break most conversions, the Excel formulas that prove the result, and the import steps for each package. To skip the manual work, ReconScribe’s free bank statement converter that checks the rows against the statement’s printed balances reads one statement a month for each company and exports Tally XML or CSV.

What a bank statement to Excel conversion has to get right

A bank statement is the bank’s record of what moved through your account in a period. Most Indian statements print a date, a narration (the bank’s description), a reference number, withdrawal and deposit columns, and a running balance: what was left after each row.

The statement is written from the bank’s side. Your money is something the bank owes you, so a deposit is a credit on the statement and a withdrawal is a debit. In your books the bank account is an asset, so the same deposit is a debit to your bank ledger. Think of the statement as the bank’s diary about you and your ledger as your diary about the bank: the same events in opposite columns. Neither is wrong. Trouble starts when a bank statement to Excel conversion mixes them up.

So a clean bank statement to Excel file keeps four things intact: every row, every amount on its correct side, every date read correctly, and the bank’s own order of rows.

Three ways to get a bank statement to Excel

Which bank statement to Excel route fits depends on what your bank gives you and what you import into.

1. Download Excel or CSV from net banking

If net banking offers Excel or CSV (comma separated values, a plain text table Excel opens directly), take it. Nothing is read or retyped, so it is the cleanest bank statement to Excel route. Tally builds its own bank import around these downloads: its help page on importing bank statements in TallyPrime supports CSV or Excel files from the portals of 145+ banks, and lists PDF, and CSV or Excel converted from PDF, as formats it cannot import. Older months, though, are often available only as PDF.

2. Use Excel’s own PDF import

Recent versions of Excel read a digital PDF through Data > Get Data > From File > From PDF, as Microsoft’s guide to importing data with Power Query shows, so if you own Excel this bank statement to Excel route costs nothing extra. Microsoft’s notes on the Power Query PDF connector say multi-line rows are not always identified properly, so a wrapped narration can split across two rows and leave data out of line. Expect to delete repeated headers and brought-forward lines by hand.

3. Use a bank statement converter

A bank statement converter reads the PDF for you, scans included, and hands back a bank statement to Excel file ready to check. Before you trust one, check that it reads scanned pages, opens password-protected PDFs without keeping the password, checks the result against the opening, closing and running balances, exports Tally XML and the CSV layouts Zoho Books and QuickBooks expect, and tells you what it stores.

Six traps that break a bank statement to Excel conversion

Scanned pages

A scan is a photograph of paper with no text inside the PDF, so copy and paste returns nothing. If you cannot select a word, it is a scan. Reading it needs OCR (optical character recognition), which misreads in predictable ways: an 8 as a 3, a 1 as a 7, or a lost decimal point turning Rs 1,250.00 into Rs 1,25,000. The balance checks below catch each one, which is why a scanned bank statement to Excel file always needs them.

Password-protected PDFs from Indian banks

Many Indian banks email statements as password-protected PDFs, and the email usually explains how the password is formed. Nothing can read the rows until the file is opened with that password, so unlocking comes before any bank statement to Excel work. Open it on your own machine, never forward an unlocked copy, and delete that copy when you are done.

Carried-forward lines on multi-page statements

On a multi-page statement the bank prints a carried forward line (C/F) at the foot of each page and a brought forward line (B/F) at the top of the next. Both repeat the balance at the page break, and neither is a transaction. Left in your bank statement to Excel sheet as rows, they inflate the totals by that figure. Page subtotals and repeated headers do the same, so delete them first.

Debit and credit read the wrong way round

Two amount columns can drift out of line on a scan, and a single Amount column loses its direction when the Dr or Cr suffix is dropped. One withdrawal read as a deposit moves your closing figure by twice its amount: Rs 12,000 paid out but recorded as received leaves your bank statement to Excel sheet Rs 24,000 off. If your totals match the printed ones swapped, the whole statement flipped.

Statements listed newest first

Some banks list the latest transaction first, so in a bank statement to Excel sheet the running balance seems to move backwards and the balance and date checks fail on almost every row. Do not just sort by date, because rows that share a date can shuffle. Add a serial number column (1, 2, 3 as printed) and sort it from largest to smallest. That reverses the statement exactly and keeps same-day rows in the bank’s order.

Dates and amounts Excel quietly changes

Indian statements print the day first (dd/mm/yyyy). On a computer set to a month-first region, Excel can read 03/04/2026 as 4 March and leave 25/04/2026 as text. Lakh-style amounts (1,25,000.00) can also land as text, which aligns left instead of right. Fix the formats first, or your bank statement to Excel totals will silently skip rows. Our tested AI prompts for Excel include one that fixes the dates and amounts in a bank CSV.

Four checks that prove your bank statement to Excel file

These checks use only figures the bank printed, so they test the bank statement to Excel sheet against the statement itself. Put dates in column A, narration in B, reference in C, withdrawals in D, deposits in E and the printed balance in F, with transactions in rows 2 to 61. Type the printed opening balance in J1 and the printed closing balance in J2. If SUM, ROUND or COUNTIF is new to you, start with our guide to the 15 Excel formulas for finance teams.

1. Balance equation. =ROUND(J1+SUM(E2:E61)-SUM(D2:D61)-J2,2) must return 0. Anything else is the amount you are out by.
2. Running balance on every row. In G2 enter =ROUND($J$1+E2-D2-F2,2), in G3 enter =ROUND(F2+E3-D3-F3,2), and fill G3 down. Every cell should be 0. The first one that is not marks the row where the sheet breaks.
3. Totals and counts. Where the statement prints total withdrawals, total deposits or entry counts, compare them with =SUM(D2:D61), =SUM(E2:E61), =COUNTIF(D2:D61,">0") and =COUNTIF(E2:E61,">0").
4. Dates in order and in period. In H3 enter =A3>=A2, fill it down, and confirm =COUNTIF(H3:H61,FALSE) is 0. Then confirm =MIN(A2:A61) and =MAX(A2:A61) fall inside the statement period.

A worked example: Rs 24,000 out

Neha keeps the books for a small trading firm in Pune. The firm’s August 2026 statement prints an opening balance of Rs 2,45,300.00 and a closing balance of Rs 2,72,849.50. Her converted sheet sums deposits of Rs 6,10,000.00 and withdrawals of Rs 5,58,450.50.

Rs 2,45,300.00 plus Rs 6,10,000.00 minus Rs 5,58,450.50 is Rs 2,96,849.50, which is Rs 24,000.00 more than the printed closing balance. Rs 24,000 is exactly twice Rs 12,000, so she looks for a Rs 12,000 row on the wrong side, and column G turns non-zero first on row 19: a 14 August NEFT of Rs 12,000 to a transporter, sitting in deposits. Moved to withdrawals, it leaves deposits of Rs 5,98,000.00 and withdrawals of Rs 5,70,450.50, and the equation lands on Rs 2,72,849.50 to the paisa. Only now is her bank statement to Excel file ready to import.

When all four checks pass, the bank statement to Excel file is proven, and it is the starting point for your monthly reconciliation. Our free bank reconciliation template in Excel walks you through matching it against your ledger.

Bank statement to Tally: importing XML vouchers

A proven bank statement to Excel file is half the job, because the bank statement to Tally route in this guide brings transactions in as an XML file through Tally’s Import menu. Each deposit becomes a Receipt voucher and each withdrawal a Payment voucher (a voucher is Tally’s word for one accounting entry), with two ledger lines: your bank ledger and the ledger the row belongs to, such as Rent or a supplier. These steps follow Tally’s help page on importing data from XML into TallyPrime:

1. Create the masters first, because the company must already have every ledger the transactions use: your bank ledger under the Bank Accounts group, each ledger the rows are coded to, and a ledger named Suspense under the Suspense A/c group for uncoded rows.
2. Press Alt+O (Import) and select Transactions.
3. On the Import screen, enter the File Path and select the XML file.
4. Select Yes to back up the company data. Always take this backup.
5. Press Enter to import. Then check the Day Book for the period and use the Exceptions report to fix anything that failed for a missing ledger.

Import each file once: Tally’s import data FAQ warns that importing the same data again and again duplicates transactions. Tally’s separate Alt+O > Bank Statement import reads only CSV or Excel files downloaded from a supported bank’s portal, not PDFs or files converted from them, so it will not take a bank statement to Excel file you built from a PDF. For PDFs and scans, XML is the bank statement to Tally route.

Importing into Zoho Books or QuickBooks with CSV

Both packages take the checked rows as a CSV with no ledger column, and you categorise or match the transactions inside the package after import, so the same bank statement to Excel sheet only needs saving as CSV. Still choosing a package? Read our Zoho Books vs TallyPrime comparison for Indian businesses.

Zoho Books

These steps follow Zoho’s help article on importing a bank statement into Zoho Books:

1. Select Banking, choose the bank account and click Import Statement.
2. Click Choose File, select your CSV and pick the Amount Column Type: Double Column for separate withdrawal and deposit columns, Single Column and Amount Type, or Single Column with Negative Values. Set the encoding and delimiter and click Next.
3. In Map Fields, match your headers to Zoho Books fields and click Next.
4. Check the Preview and click Import. Rows arrive uncategorised, ready to categorise or match.

Zoho Books accepts CSV, TSV, XLS, OFX, QIF, CAMT.053 and CAMT.054, and its troubleshooting page asks for DD-MM-YYYY dates and positive amounts under Double Column, so set both in your bank statement to Excel sheet before you save the CSV.

QuickBooks Online

These steps follow Intuit’s help article on how to manually upload transactions into QuickBooks Online:

1. Go to All apps > Accounting > Bank transactions and select the account’s tile.
2. Open the Link account dropdown, select Upload from file, add your CSV and select Continue.
3. Choose the account, map the columns and select the transactions, selecting Continue after each step, then Yes and Done.

The file needs three columns (Date, Description, Amount, with money out negative) or four (Date, Description, Credit, Debit), must be 350 KB or less, and can hold up to 1,000 lines per upload, so a busy account’s bank statement to Excel file may need splitting by month. Keep one date format (Intuit recommends dd/mm/yyyy) and leave cells blank rather than entering zero.

A bank statement converter that checks its own work

ReconScribe’s Bank to Books module does the whole bank statement to Excel job in one pass. With a free account, upload the PDF, digital or scanned, up to 4 MB, with its password if it is locked; the password unlocks that one read and is not stored. Blank, terms and disclosure pages at either end are skipped and do not count against your page limit, and a newest-first statement is put in date order.

Seven checks then run, each one wherever the statement prints the figure it needs: the balance equation, total debits and credits, debit and credit counts, the running balance on every row, and dates in order and inside the period, with amounts agreeing to the paisa. A statement shows Verified only when the proof runs from a printed opening to a printed closing balance, and a failed check shows what is off, down to the row and date where the running balance breaks. A wrong-side row is switched only when the bank’s own balances prove it, one button swaps debit and credit on every row if the whole statement came out reversed, and possible duplicates are flagged.

For a bank statement to Excel export, choose Custom CSV: nine columns to pick, order and rename, and five date formats. It is a CSV rather than an .xlsx workbook, so it opens in Excel or any import screen. For a bank statement to Tally import, the XML posts uncoded rows to Suspense or a ledger you name, and each voucher carries an ID built from the bank ledger name, the account and what the row says, so an unchanged row keeps the same ID in a later export. Zoho Books and QuickBooks get their own CSV layouts.

On both plans the PDF file is not kept and account numbers are cut to their last 4 digits. Try it on a sample statement in the Bank to Books live demo, no sign-up needed, then run your next statement through the free bank statement to Excel and Tally converter.

Related guides for your bank statement to Excel work

• Bank reconciliation template: match the checked statement against your ledger, step by step.
• MIS report format: carry the reconciled bank balance into the cash page of your monthly MIS.
• Cash flow decision tool: a free 6-week cash flow forecast template in Excel.
• AI prompts for accounting: tested prompts for the month-end close, from the close calendar to a trial balance flux check.

Please note: we update this page from time to time, but software menus, import formats and bank statement layouts change, and there can be a gap between a change and our update. Check the official help pages linked above before you act. This guide helps you convert and check bank statements; it is not accounting, legal or tax advice, and ReconScribe is not responsible for decisions made on it.

Sources

• TallyPrime: Import bank statements
• TallyPrime: Import data from XML
• TallyPrime: Import data FAQ
• TallyPrime: Ledgers and groups
• Zoho Books: Import a bank statement
• Zoho Books: Import errors
• QuickBooks Online: Upload transactions
• QuickBooks Online: Format bank CSV files
• Microsoft Excel: Power Query import
• Microsoft Learn: PDF connector