To convert a Central Bank of India statement PDF to Excel, begin by checking if a CSV or Excel statement exists, since it saves effort. If not, extract the table from the PDF, convert text dates and amounts, split money in from money out, remove the clutter rows and then check that opening balance plus credits minus debits equals the closing balance.
Step one: copy, import or convert?
There are three routes and each suits a different statement.
- Copy and paste from the PDF viewer: fine for a page or two, but expect messy columns.
- Excel's PDF import (where your version offers Get Data, From File, From PDF): good for tidy tables, and it shows a preview.
- A separate converter: handy for long statements, but the file leaves your computer, so read its privacy terms first.
If a password is asked, it is the one set for your own statement. Open the file, then save an unlocked copy on your own computer.
Step two: dates
Dates often come in as text. If A2 reads 21.04.2025, then =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) gives a real date because day sits in the first two characters, month in characters four and five, and year in the last four. Paste the results as values and change the format to DD-MMM-YYYY so you can see at a glance that they are real dates.
Step three: amounts and the debit-credit split
Suppose the amount is in B2 as text with commas and the type is in C2 as Dr or Cr. Use =VALUE(SUBSTITUTE(B2,",","")) for the number. Then =IF(C2="Dr",D2,"") in one column gives debits and =IF(C2="Cr",D2,"") in another gives credits, where D2 holds the cleaned number. Leave unused cells blank rather than zero if you intend to count rows later.
Step four: clear out what is not a transaction
- Repeated headings on each page.
- Opening or closing lines, once noted down.
- Blank rows between pages.
- The running balance column, after you have used it for checking.
With real dates in column A, filter the sheet so that cells which are not dates show up. =ISNUMBER(A2) in a spare column and a filter on FALSE does it. Read what the filter shows before you delete.
Step five: does it add up?
| Figure | Made-up amount (₹) |
|---|---|
| Opening balance | 12,000 |
| Total credits | 38,500 |
| Total debits | 35,200 |
| Expected closing balance | 15,300 |
The last line comes from 12,000 + 38,500 - 35,200 = 15,300. If the statement closes at the same figure, the table is whole. Use =SUM($E$2:$E$500) for credits and =SUM($F$2:$F$500) for debits, adjusting the range to your rows.
The formulas above assume your numbers sit in columns D, E and F; move the letters to match your own sheet. One more habit helps: after the sums, glance at the biggest five debits and ask whether each looks right. A wrongly signed amount usually stands out among large figures.
Can you turn this into a monthly template?
- Keep a tab named Raw where you paste each new statement and never edit it.
- On a tab named Clean, point formulas at Raw so they refill by themselves.
- Type the statement's opening and closing balances into two cells, say B1 and B2.
- In B3 put =B1+SUM($E$2:$E$500)-SUM($F$2:$F$500) for the expected closing balance.
- In B4 put =B3-B2. A zero means the month is complete.
Each new month then costs a paste and a glance at one cell. It will break if the bank changes its layout, so look at the first rows every time.
When the sheet is not worth it
If you repeat this monthly, SpendixAI can take the PDF, CSV or Excel file straight, let you review the rows and keep the account with each transaction. You still check the closing balance, but the conversion work goes.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

