How to Format Bank Statement in Excel for Tally
Importing an Excel bank statement into Tally Prime can save you hours, but only if the Excel file is formatted correctly. If your columns are misaligned or date formats are incorrect, Tally will throw errors or skip transactions. Here is the exact format Tally expects.
The Required Columns
Tally's default bank statement import utility maps to specific data points. Ensure your Excel sheet (`.xlsx`) has clear headers in the first row. The essential columns are:
- Date: The transaction date.
- Particulars / Description / Narration: The details of the transaction.
- Instrument Number: (Optional) Cheque or reference number.
- Withdrawal (Debit): Amount deducted from your account.
- Deposit (Credit): Amount added to your account.
Critical Formatting Rules
- Date Format: Tally is strict about dates. Ensure the Date column is formatted uniformly (e.g., `DD-MM-YYYY`). Avoid mixing formats like `12-Oct-2023` and `10/12/23` in the same sheet.
- Clean Amounts: Debit and Credit columns must contain pure numbers. Remove currency symbols (₹, $, etc.) and commas if they are stored as text. Blank cells in the Debit/Credit columns should ideally be `0` or left entirely empty (not spaces).
- No Merged Cells: Tally cannot parse merged rows or columns. Every transaction must be on a single, flat row.
- Remove Passwords: The Excel file cannot be password protected.
The Automated Approach
Formatting downloaded CSVs or manually converted PDFs to meet these strict rules takes time. By using Parsify to convert your PDF bank statements, the resulting Excel file is automatically cleaned, unmerged, and formatted precisely to be Tally-ready.
Once formatted, simply go to Tally Prime > Banking > Bank Reconciliation > Import, and map your pristine columns for a flawless import.