In short
- If you have net banking access, download the statement as Excel or CSV. That is the cleanest source.
- Excel's own Get Data › From PDF works for short, simple statements on Windows.
- For many statements, long periods or password-protected PDFs, use a converter built for bank statements.
- Whatever you use, run the balance check: previous balance + credit − debit must equal the next balance on every row.
Most CA firms don't get to choose the format. Clients send whatever their bank emailed them, and that is nearly always a PDF. A PDF is built for printing, not for spreadsheets. It stores text at positions on a page, so the rows and columns you see aren't really in the file. Any conversion has to rebuild them, and that is where mistakes creep in.
This guide covers three ways to get from a bank statement PDF to Excel, then the check that matters most: whether the running balance still adds up after conversion.
The three ways to get a statement into Excel
| Method | Best for | Watch out for |
|---|---|---|
| Download Excel or CSV from net banking | Your own accounts, or clients who give you read access | Not possible when all you have is the emailed PDF; some banks limit the date range per download |
| Excel: Get Data › From PDF | A few pages, one statement, Windows with Microsoft 365 | Not available in Excel for Mac; needs cleanup in Power Query; struggles with long statements |
| A bank statement converter | Many statements, long periods, password-protected PDFs, Tally import | Where your file goes, whether passwords are kept, and whether it checks balances |
A generic "PDF to Excel" tool is a fourth option, but it doesn't know what a transaction is. It copies the page layout, so you still have to rebuild the rows yourself.
Why copy-paste from a PDF breaks
Selecting the text in a PDF viewer and pasting it into Excel usually goes wrong in the same few ways:
- Everything lands in one column, or columns shift partway down the page.
- One transaction becomes two rows when a long narration wraps onto a second line.
- Page headers and footers show up between transactions every time a new page starts.
- Numbers paste as text because of commas or stray spaces, so
SUMreturns zero. - Dates flip: 04/05 becomes 5 April or 4 May, depending on your system's date setting.
Each of these is fixable by hand for one page. Across a 30-page statement, or 40 clients at month end, it isn't practical, and a single missed row throws off every balance after it.
Method 1: download Excel or CSV from net banking
If you can log in to the account, this is the most reliable route, because the bank exports its own data and nothing has to be read back from a PDF.
- Log in to net banking and open the account statement section.
- Choose the period. Some banks cap one download at a few months, so split a financial year into parts if needed.
- Pick Excel (XLS/XLSX) or CSV instead of PDF, then download.
- Open the file and delete any summary rows above the transaction table before you sort or total anything.
The catch for accountants is access. Clients rarely share net banking logins, and they shouldn't have to. So most of the time you're back to the PDF.
Method 2: Excel's Get Data › From PDF
Excel for Microsoft 365 on Windows can read tables out of a PDF with Power Query. Microsoft documents it as the PDF connector.
- Open a blank workbook and go to Data › Get Data › From File › From PDF.
- Pick the statement. If it is password-protected, remove the password first (see our guide to locked statements).
- In the Navigator, select the tables for each page, then choose Transform Data rather than Load.
- In Power Query, use Append Queries to stack the pages into one table, then Use First Row as Headers.
- Filter out repeated header rows and blank rows.
- Set column types: amounts as Decimal Number, and dates using Using Locale › English (India) so 04/05 stays 4 May.
- Click Close & Load, then run the balance check below.
Method 3: a bank statement converter
A converter built for bank statements outputs transactions, not page layout: one row per entry with date, narration, debit, credit and balance. When you compare tools, ask:
- Does it check the balance? A converter that doesn't tell you when a row is missing just moves the checking work to you.
- Can you edit before export? You will want to fix a flagged row or tag ledgers without reopening Excel.
- Does it open password-protected PDFs, and does it keep the password afterwards?
- What can it export? Excel and CSV are standard. If the data is going into Tally, Tally XML saves a whole step (see importing into TallyPrime).
- Which banks has it handled? Try it on your clients' banks before trusting it at month end.
The balance check: one formula that proves the conversion
A bank statement contains its own answer key. Every row's balance must equal the previous balance, plus that row's credit, minus its debit. If even one transaction is missing, doubled or misread, the chain breaks at that row.
Lay the sheet out as A Date, B Narration, C Debit, D Credit, E Balance, with the first transaction in row 3 and the opening balance in E2. In F3 enter:
=IF(ROUND(E2+D3-C3,2)=ROUND(E3,2),"OK","CHECK")
Fill it down to the last row. Then confirm the whole period in one cell, with the statement's closing balance typed in H1:
=ROUND(E2+SUM(D:D)-SUM(C:C),2)=ROUND(H1,2)
When the check says CHECK, look at that row and the one above it. The usual causes:
| What you see | Likely cause |
|---|---|
| First CHECK, then OK again | One amount was misread, or debit and credit were swapped on that row |
| CHECK on every row after a point | A transaction is missing just above the first CHECK |
| CHECK on a row with no amounts | A wrapped narration became its own row; join it to the row above and delete it |
| Totals differ by an exact transaction amount | That transaction appears twice |
Quick fixes for common Excel problems
| Problem | Fix |
|---|---|
| Amounts are text (green triangles, SUM is zero) | Select the column, then Data › Text to Columns › Finish; or use =VALUE(C3) |
| Dates are text or in the wrong order | Data › Text to Columns › Date: DMY |
| Debits shown as negative numbers in one column | Debit =IF(X3<0,-X3,""), Credit =IF(X3>0,X3,"") |
| Stray spaces stop lookups from matching | =TRIM(B3) on the narration column |
| Repeated header rows | Filter the Date column for anything that isn't a date and delete those rows |
Keep client statements private
A statement holds account numbers, salaries, rent, and who your client pays and gets paid by. Before you upload one to a free website, find out where the file is stored, for how long, and whether the password is kept. Send PDF passwords separately from the file, and delete unlocked copies from shared folders once the work is done.
Converting with FileKey
FileKey is built for this job in CA firms. Upload one or more statement PDFs, enter the password if the PDF has one, and FileKey returns one row per transaction with the balance check already done, so flagged rows are easy to spot. You can fix rows and set ledgers in the browser, then export to Excel, CSV, MCS or Tally XML.
It handles statements from 730+ Indian banks, including SBI, HDFC Bank, ICICI Bank and Axis Bank, plus many co-operative and regional banks. FileKey doesn't save PDF passwords. See the bank statement converter page for details.
Frequently asked questions
Can I convert a scanned bank statement to Excel?
A scan or a phone photo is an image, so it has to go through OCR first. OCR often misreads digits, such as 8 for 3 or a missing decimal point, so the balance check matters even more. Whenever you can, ask the client for the statement as downloaded from net banking or emailed by the bank.
Is it safe to use a free online PDF to Excel converter for bank statements?
It depends on the service. A statement is sensitive financial data, so check the privacy policy for how long files are kept, whether they are used for anything else, and whether passwords are stored. Avoid tools that don't say.
Why doesn't my converted closing balance match the statement?
Usually a row is missing or doubled, an amount was misread, or a debit was read as a credit. Run the row-by-row formula in this guide. The first CHECK shows where the problem starts.
Can I convert a password-protected bank statement PDF?
Yes, as long as you know the password. You can remove it first with a PDF reader, or use a converter that asks for it. Our guide to password-protected statements covers both.
Which format should I use to get the statement into Tally?
Tally XML is the most direct for creating vouchers, since it can carry ledger names for each entry. TallyPrime's own bank statement import, used for reconciliation, accepts Excel, CSV and MT940 but not PDFs. See how to import a bank statement into TallyPrime.
Sources and further reading
Rules change by notification and advisory. Check the GST portal before you file.
Written by the FileKey Team
FileKey builds the tools CA firms use for e-commerce GSTR-1 and bank statement conversion. We write these guides from the questions accountants and sellers ask us every filing month. Spotted something out of date? Write to [email protected] and we will fix it. More about FileKey.