The standard column layout, ready-to-use Excel formulas, and categorization tips for any bank statement — whether you converted from PDF or typed it manually. Free, no signup.
Have a PDF? Convert it to Excel first
Drop your bank statement PDF here to auto-fill the template
Convert PDF to Excel Free →Every bank statement — Indian, US, UK, Kenyan — has the same logical columns, though they're labelled differently. This is the universal structure:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Description / Narration | Debit (–) | Credit (+) | Balance | Category |
| 2 | 01/04/2024 | Opening Balance | ₹12,450.00 | — | ||
| 3 | 02/04/2024 | SALARY/ACME CORP LTD | ₹55,000.00 | =E2+D3-C3 | Income | |
| 4 | 03/04/2024 | UPI/SWIGGY/9XXXX@okaxis | ₹320.00 | =E3+D4-C4 | Food | |
| 5 | 05/04/2024 | ATM WDL/HDFC ATM/NEW DELHI | ₹5,000.00 | =E4+D5-C5 | Cash | |
| 6 | 08/04/2024 | NACH DR/LIC PREMIUM/REF001234 | ₹2,800.00 | =E5+D6-C6 | Insurance |
Column A: Date — Format as DD/MM/YYYY or MM/DD/YYYY depending on your locale. In Excel, format the column as Date type (not General) so sorting works correctly.
Column B: Description — Paste the full narration from the bank. Do not truncate — the full text is needed for keyword-based categorization formulas.
Columns C / D: Debit and Credit — Keep these as separate columns, not a single "Amount" column with negatives. Separate columns make SUMIF calculations much simpler.
Column E: Balance — Use running balance formula (see below) rather than pasting the bank's printed balance — the formula will catch any data entry errors by producing a different result if a row is wrong.
Column F: Category — Add manually or use auto-category formulas (see below).
Put the opening balance in E2 as a hardcoded number. Then in E3 (and all rows below):
Where C is Debit (money out) and D is Credit (money in). The formula adds credits and subtracts debits from the previous balance.
If you have empty rows in your data (common when copy-pasting from PDFs), use this variant to avoid showing a balance for blank rows:
This returns blank if the Date column (A3) is empty, showing the balance only for rows that have a transaction date.
To confirm your formula matches the bank's printed balance, add this check in a separate column:
Uses 0.01 tolerance to handle rounding differences. If you see Mismatch on any row, that row has a data entry error.
In the Category column (F), use nested IF with SEARCH() to auto-assign categories based on keywords in the Description column. Example for Indian bank statements:
Extend the nested IFs with your own keywords. IFERROR wraps the whole formula to handle formula errors gracefully.
For a large statement with many categories, create a Keywords sheet with two columns: Keyword and Category. Then use a helper formula that checks each keyword against the Description. This avoids deeply nested IFs and is much easier to maintain.
Add a Summary sheet that uses SUMIFS to aggregate totals by month and category. Structure:
| Month | Category | Total Debit | Total Credit | Net |
|---|---|---|---|---|
| Apr 2024 | Food | =SUMIFS(Transactions!C:C, Transactions!F:F, "Food", Transactions!A:A, ">="&DATE(2024,4,1), Transactions!A:A, "<="&DATE(2024,4,30)) | — | — |
SUMIFS is the workhorse of bank statement analysis. It sums one range (C:C = Debits) when multiple criteria match (category matches AND date falls in the month).
For ITR Schedule OS (Other Sources), you need total interest income from savings accounts. Filter the Category column for "Interest Income" and SUM the Credit column. For business income (ITR-3, ITR-4), you need monthly business receipts and expenses broken down by head — the category formulas do this automatically.
Banks ask for 6 months' statements when processing a home loan or business loan. Using the template, prepare a clean Excel with (1) monthly income totals, (2) average monthly balance, (3) total EMI obligations (NACH DR entries summed). This supplements the raw bank PDF.
For sole proprietors and small businesses, the categorized template is your primary accounts book. Month-end, filter by expense category, export as CSV, and import into Tally or QuickBooks to create ledgers. This avoids re-entering every transaction in your accounting software.
Compare monthly spending by category with SUMIFS, build a pivot table of expenses over time, and spot category trends year-on-year. The Credit column shows all income sources — freelance income, interest, dividends, and salary will each land in different rows.
Date, Description/Narration, Debit Amount, Credit Amount, Running Balance, and Category. Keep Debit and Credit as separate columns — not a single Amount column with negatives — for simpler SUMIF calculations.
Put the opening balance in E2. In E3: =E2+D3-C3 where C = Debit and D = Credit. Copy this formula down all rows. Use =IF(A3="","",E2+D3-C3) to leave empty rows blank.
Use nested IF + SEARCH() in the Category column: =IF(ISNUMBER(SEARCH("SALARY",B2)),"Income","Other"). For many categories, build a keyword-to-category lookup table on a separate sheet and use IFERROR + VLOOKUP or INDEX/MATCH to auto-assign categories.
Separate Debit and Credit columns. A single Amount column with negatives requires you to handle negatives in every SUMIF — with separate columns, the formula is straightforward: =SUMIF(F:F,"Food",C:C) for total food spending.
Yes. Save as CSV and use the import wizard in Tally (Gateway of Tally → Import of Data → Transactions) or QuickBooks Online (Banking → Upload). Both tools require the columns to be in a specific order — typically Date, Description, Amount or Debit/Credit. Our converter outputs exactly this format.