Bank statements

Bank of India statement PDF to Excel: convert and tidy it

Bank of India statement PDF to Excel without the clutter: choose a method, clean dates and amounts, remove extra rows and match the closing balance.

Editorial illustration for Bank of India statement PDF to Excel: convert and tidy it

To get a Bank of India statement from PDF into Excel, first look for a CSV or Excel version in your own net banking, since that avoids conversion. If only the PDF is available, extract the table, convert dates and amounts into real values, remove headers and running balances, and add up the debits and credits to confirm the closing balance. Below are the steps and the formulas, one at a time.

What is the least risky way to extract the table?

Open the PDF and see if you can select text. If you can, a table-aware import or conversion is possible. If the PDF is a scanned picture, nothing will select, and you would need text recognition, which makes more mistakes with numbers. For a scan, ask your branch for an electronic statement instead.

  • Password prompt: use the one set for your own statement and open it before you convert.
  • Selectable text: try a table import in Excel if your version has From PDF under Get Data.
  • Not selectable: ask for a proper electronic copy.
  • Several pages: check that the conversion has not shifted columns between pages.

How do you tidy the first pass?

Paste into a new tab and keep a copy of the raw paste. Then work through the columns left to right. Widen them so you can read; look for dates in the first column, the narration next, amounts after that, and a balance column if one exists. I will not state the exact order for Bank of India, since it varies by statement type.

Which formulas fix dates and amounts?

ProblemFormulaResult
Text date 07/09/2025 in A2=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))A real date, 7 September 2025
Amount text 12,400.00 in C2=VALUE(SUBSTITUTE(C2,",",""))The number 12400
Blank cell in a debit column=N(D2)0 when empty
Net amount from debit D2 and credit E2=E2-D2Money in minus money out

Fill each formula down, then copy the column and paste as values. Check that the sort order is date-wise afterwards: a column of text dates sorts alphabetically and puts 10 June before 2 June.

What should you remove?

  1. Headings repeated at the top of each page.
  2. Summary blocks and totals.
  3. Blank lines left by wrapped narration.
  4. The balance column, after checking.

How do you check the cleaned data?

Take the opening balance from the statement. Add the credits, subtract the debits, and compare with the closing balance. With made-up numbers, ₹33,000 opening, ₹70,000 credits and ₹64,500 debits: 33,000 + 70,000 - 64,500 = ₹38,500. Use =SUM($E$2:$E$600) for credits and =SUM($D$2:$D$600) for debits in the columns of this example. If they disagree, filter the date column for blanks and look for rows that lost their date.

Another check is the row count. Count the transaction lines on the printed pages, then compare it with =COUNT($A$2:$A$600) on your sheet.

Making the sheet reusable

Add a month column so you can summarise later: =TEXT(A2,"mmm yyyy") turns a real date into a label such as Sep 2025. You can also write a crude keyword rule, for example =IF(ISNUMBER(SEARCH("fuel",B2)),"Fuel","") when B2 holds the narration. SEARCH needs the word you look for first and the text to look in second. Once the labels exist, Insert, PivotTable with month down the side and category across the top gives a quick summary. Rules written this way work only for words you thought of, which is why they run out of steam.

Keep the sheet for what it does best

A sheet is good for a one-off analysis. For regular monthly use, SpendixAI can read the PDF, CSV or Excel directly, show the rows for you to review, and add the categories for you, which makes the cleaning above a fallback instead of a routine.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports