Bank Statement Excel Format for Tally Import: Template Guide (2026)
Bank Statement Excel Format for Tally Import: Template Guide (2026)
Learn the exact Excel format TallyPrime accepts for bank statement imports. Column layouts, date formats, and splitting amounts. Template guide.
Who is this for: Tally Guides
TallyPrime supports Excel-based imports directly in its newer releases (TallyPrime 4.0 and 5.0). However, if your Excel column headers, date formats, or numerical values do not match Tally's exact mapping schema, the import will fail with blank entries, wrong balances, or silent validation errors.
This technical guide outlines the exact column structure, data formatting rules, and Excel cleaning workflows required to format bank statements for importing into TallyPrime.
Does TallyPrime Accept Raw Bank Statements?
No. TallyPrime does not contain an AI parser to automatically identify columns in arbitrary bank statements. You cannot upload a raw Excel file downloaded from HDFC, SBI, or ICICI Bank directly. You must first format the spreadsheet columns, clean bank-specific headers, and structure the sheet to match Tally's mapping configurations.
The Standard Excel Column Structure for Tally Import
To prepare your Excel sheet for importing bank transactions, clean the raw file and structure it into these exact columns:
| Column Name | Format Requirement | Example Value | Tally Field Mapping |
|---|---|---|---|
| Date | DD-MM-YYYY or YYYYMMDD | 01-04-2026 | Voucher Date |
| Narration | Plain Text (max 255 chars) | NEFT/987654/Rent Payment | Narration |
| Reference Number | Alphanumeric | TXN1002398 | Bank Ref No / Instrument No |
| Debit / Withdrawal | Decimal Number (positive) | 15000.00 | Payment Amount (Ledger Cr) |
| Credit / Deposit | Decimal Number (positive) | 4500.00 | Receipt Amount (Ledger Dr) |
Splitting Single-Column Amount Layouts
Many banks export statements with a single "Amount" column where withdrawals are represented as negative numbers (e.g. -500.00 or 500.00 DR) and deposits as positive numbers. TallyPrime requires separate Debit and Credit columns.
To split a single amount column (Column E) into Debit (Column F) and Credit (Column G) columns in Excel, use these formulas:
Debit Column (F2): =IF(E2<0, ABS(E2), 0)
Credit Column (G2): =IF(E2>0, E2, 0)
Drag these formulas down to separate the amounts, then copy and paste the columns as Values to strip the formulas before importing.
Common Formatting Errors to Avoid
- Merged Headers: Many banks export files with 5-10 lines of account details at the top. You must delete these rows so that Column 1 Row 1 contains the actual column headers.
- Blank Rows: Remove blank lines inside transaction records. Tally stops importing as soon as it encounters an empty row in your file.
- Text inside Amount Columns: Symbols like currency tags (e.g., ₹ or USD) or text elements (e.g., "Nil") must be stripped. The columns must contain only numeric digits.
- Invalid Date formats: Dates like "1st April" are not valid. Ensure the cell properties are set to standard Short Date formats.
Tally Prime Native Excel Mapping
Once your Excel sheet is formatted, map these columns in TallyPrime:
- Go to Gateway of Tally > Import > Manage > Mapping Templates > Create.
- Name your mapping template (for example,
Standard Bank Import). - Select the Excel sheet columns and map them to Tally's transaction fields (Date, Ledger Name, Amount).
- Save the template. Go to Import > Transactions, choose the file, select your template, and import.
How TrulyInvoice Handles Bank Formats Natively
Creating custom mapping templates for every bank statement format is time-consuming. TrulyInvoice eliminates this step:
- No Formatting Needed: Upload the raw bank statement PDF or Excel directly.
- Automatic Column Detection: TrulyInvoice automatically identifies transaction dates, debit amounts, credit amounts, and narration rows.
- Ledger Mapping Suggestion: It suggests ledger allocations based on prior mapping history, preventing ledger creation errors in Tally.
- Direct Sync: Syncs entries to Tally Prime over a secure Port 9000 connector at a flat subscription rate of **plans starting at ₹399/month**, eliminating the need for manual spreadsheet cleanup.
Chartered Accountant & Accounting Automation Specialist