To convert a Kotak Mahindra Bank statement into Excel, look for a CSV or Excel download first. If you only have a PDF, use Data, Get Data, From File, From PDF in Excel for Microsoft 365 or a recent desktop Excel and load the transaction table. Then prove the data is complete by rebuilding the running balance row by row, rather than trusting the converted table because it looks tidy.
Why rebuild the running balance?
A running balance is a built-in answer key. If every row's balance equals the previous balance plus the credit minus the debit, no row can be missing, doubled or in the wrong column without the check failing. We do not know how your own statement orders its columns, so identify them first. Suppose the debit is in D, the credit in E and the balance in F, with row 2 the first transaction and row 1 the headings:
- In G3 enter =ROUND(F2+E3-D3-F3,2).
- Fill it down. Every cell should show 0.
- Filter the column for anything other than 0. Each non-zero row marks a problem, or the row after a missing one.
This works when the statement lists rows oldest first. If yours lists newest first, sort by date ascending before you start; keep ties in their original order by sorting with an extra helper column that numbers the rows.
How do I handle a password-protected file?
Open the PDF with the hint that came with it from the bank, and save a copy only if the bank allows it. If you cannot open it at all, download a new statement from your own login instead of trying to break the lock.
How do I combine several months?
Convert each month on its own sheet, tab names such as Apr, May and Jun. In Excel 365 or 2021, VSTACK can then combine them into one list:
- =VSTACK(Apr!A2:F200, May!A2:F200, Jun!A2:F200)
- If a month has fewer rows, the blank rows from the shorter range will come through, so filter them out afterwards.
- Older Excel does not have VSTACK; copy and paste each month below the last instead.
What if the table loads with merged or split rows?
PDF tables sometimes break one transaction over two lines, for example when a long narration wraps. In the Power Query editor you will see a row with a date and amount followed by a row with only text. Fix this before loading: filter out rows with no date after copying their text up, or, in the sheet, paste the stray text into the narration of the row above and delete the extra row. The running-balance test will fail on the half-empty row, which is a useful signal that you have found it.
How do I catch a duplicate row?
Repeated rows appear when two downloads overlap. With the date in A, the narration in B and the debit in D, this formula in H2 flags a row that appears more than once:
- =COUNTIFS($A$2:$A$500,A2,$B$2:$B$500,B2,$D$2:$D$500,D2)>1
- TRUE means more than one identical row. It may still be genuine, such as two equal fares on the same day, so look before deleting.
What does the final tally look like?
Say the opening balance is ₹9,800, credits ₹41,000 and debits ₹36,450. The closing balance should be ₹9,800 plus ₹41,000 minus ₹36,450, which is ₹14,350. In the sheet, with the opening balance in B1, enter =B1+SUM(E2:E500)-SUM(D2:D500). The answer should match the closing balance on the statement to the paisa.
Once the running-balance check shows zero for every row, lock that column by pasting it as values and note the date you checked it. A sheet that has passed the test is a trustworthy base for totals, filters and charts. One that has not been tested may look perfect and still be missing a row.
When is a sheet no longer worth the effort?
If rebuilding and checking a dozen months starts to feel like a part-time job, a tracker is the next step. SpendixAI accepts PDF, CSV or Excel statements and shows each row for review before it is saved, which takes over the repetitive part while you keep the closing-balance check.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

