Bank statements

Canara Bank statement PDF to Excel: convert and tidy it

Canara Bank statement PDF to Excel: strip repeated headers and running balances, and use a row-by-row balance test to find missing transactions.

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

A Canara Bank statement converted from PDF to Excel usually arrives with repeated header rows, page totals and a running-balance column that you do not want in the data. Clean those out, then use the running balance itself as a test: each row's balance should equal the one above plus credit minus debit, and any row where it does not points to a missing or misread transaction.

What do I delete first?

  • Column titles that repeat at the top of each page.
  • Page totals, carried-forward lines and bank footers.
  • Blank spacer rows.
  • The opening-balance line, if it sits as a row (keep the number, it is useful).

Do not delete the running-balance column yet. It is your proof.

How do I mark rows to delete?

First make sure dates are real dates. Add a helper column G and enter:

=IF(ISNUMBER(A2),"keep","remove")

Real dates are numbers in Excel, so title rows and footers come out as remove. Filter column G for remove, select the visible rows, delete them and clear the filter. If every row says remove, your dates are text and need converting first.

Can the running balance find missing rows?

Yes. Say your columns are A Date, B Narration, C Debit, D Credit, E Balance. Row 1 holds the headings, row 2 holds the opening balance in column E, and the first transaction is in row 3. In F3 enter:

=ROUND(E3-(E2+N(D3)-N(C3)),2)

Copy it down. A result of 0 means the row agrees with the one before. A non-zero value means something between those two rows is missing, wrong or in the wrong order. The ROUND keeps small decimal noise from showing up as an error.

A worked example

Imagine these hypothetical rows. The opening balance in row 2 is ₹20,000.

RowDebitCreditPrinted balanceTest result
32,500–17,5000
4–45,00062,5000
51,200–61,3000
6800–59,900−600

Row 6 should leave 61,300 − 800 = ₹60,500, but the printed balance is ₹59,900. The test shows 59,900 − 60,500 = −600, which says ₹600 more has gone out than the sheet records. A debit of ₹600 is probably missing between rows 5 and 6, so go back to the PDF and look for a payment of exactly that amount.

What if the narration is spread over several rows?

Long narrations sometimes wrap onto an extra row that has no date and no amount. Copy the extra text into the narration cell above and delete the leftover row, then rerun the balance test.

How do I keep a clean copy for next month?

Doing the clean-up once is fine. Doing it twelve times from scratch is where people stop. Make a template workbook instead:

  1. Create two tabs, Raw and Clean.
  2. On Clean, set up the headings and the helper formulas in the first row, ready to copy down.
  3. Each month, paste the new statement into Raw only, then point the formulas at it.
  4. Save the finished file as Canara-2026-05 or whatever the month is, so earlier months are never overwritten.

The balance test stays in the template, so every month gets the same proof for free.

Next step once the test column is all zeros

  1. Delete the helper columns or paste values.
  2. Keep Date, Narration and one signed amount.
  3. Save a copy named by month so the cleaned file is not overwritten.

If cleaning every month feels heavy, an importer that shows rows for review, like SpendixAI, does the repetitive part and leaves the checking to you.

If the test column is mostly zeros with one stray non-zero, you have almost certainly found a single missing row. If it is full of non-zeros after one point, the rows are out of order, perhaps because a sort was applied to one column. Sort the date column ascending with the whole table selected, then rerun the test.

SPENDIXAI JOURNAL

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

Explore your reports