TallyPrime Excel Import Mapping Templates & Structural Setup Guide
TallyPrime Excel Import Mapping Templates & Structural Setup Guide
Download free Excel templates for TallyPrime transactions. Step-by-step column mapping instructions for purchase vouchers, sales invoices, and bank statements.
Who is this for: Tally Prime Hacks
Manually entering hundreds of invoices, bank transactions, and ledger entries into TallyPrime is time-consuming and prone to human errors. With the native Excel Import Feature introduced in TallyPrime Release 4.0 and expanded in Release 5.0, accountants can import bulk transaction files directly from custom Excel spreadsheets.
However, to ensure a smooth import without error logs or failed vouchers, your Excel file must be structured correctly. This guide provides step-by-step instructions for setting up Excel mapping templates for purchase bills, sales invoices, and bank statements.
Quick Answer: How to Map Excel Templates for Tally Prime Import (2026)?
TrulyInvoice recommends using Tally Prime's visual mapper under Import > Manage > Mapping Templates > Create. Map Voucher Date, Voucher Type, Party Ledger, and Stock Item columns cleanly. TrulyInvoice AI bypasses manual Excel template building by reading PDFs & images directly.
- Native Excel mapping is built into Tally Prime Release 4.0+ & 5.0 under Import > Manage.
- Requires exact spelling alignment between Excel column values and Tally ledgers.
- Supports multi-line items by repeating the Invoice Number across consecutive rows.
- TrulyInvoice AI OCR extracts PDF purchase bills directly into Tally Prime without Excel mapping.
1. Master Excel Template Structure for Purchase Invoices
For importing multi-item purchase vouchers with GST splits, your Excel file should include the following standard columns:
| Column | Field Name | Description | Example Data |
|---|---|---|---|
| Column A | Voucher Date | Invoice date in DD-MM-YYYY format | 15-06-2026 |
| Column B | Voucher Type | Must match Tally voucher type name exactly | Purchase |
| Column C | Supplier Invoice No | Vendor invoice reference number | INV-2026-0891 |
| Column D | Party Name | Supplier ledger name in Tally | Acme Traders Pvt Ltd |
| Column E | GSTIN | 15-digit supplier GSTIN | 27AAACA12341Z1 |
| Column F | Stock Item Name | Item name matching Tally inventory master | Steel Rods 12mm |
| Column G | Quantity | Billed quantity (numeric only) | 50.00 |
| Column H | Unit Rate | Price per unit excluding GST | 450.00 |
| Column I | Taxable Amount | Quantity × Rate | 22500.00 |
| Column J | CGST Amount | Central GST amount (if intra-state) | 2025.00 |
| Column K | SGST Amount | State GST amount (if intra-state) | 2025.00 |
| Column L | IGST Amount | Integrated GST amount (if inter-state) | 0.00 |
2. Bank Statement Import Template Structure
For bank statement imports (used for BRS and automated ledger entry), use the following structure:
| Column | Field Name | Description | Payment Row Example | Receipt Row Example |
|---|---|---|---|---|
| Column A | Transaction Date | Date transaction posted at bank | 02-06-2026 | 03-06-2026 |
| Column B | Bank Ledger | Bank account ledger name in Tally | HDFC Bank Current A/c | HDFC Bank Current A/c |
| Column C | Narration | Transaction description/reference | UPI payment for April rent | NEFT payment receipt |
| Column D | Instrument No | Cheque number or UPI Transaction ID | TXN102930291 | N4930193 |
| Column E | Debit (Outflow) | Withdrawal amount (creates Payment) | 25000.00 | 0.00 |
| Column F | Credit (Inflow) | Deposit amount (creates Receipt) | 0.00 | 84500.00 |
3. Advanced Structural Mapping: Multi-Row Transactions
One of the biggest challenges in importing transactions from Excel to TallyPrime is handling vouchers with multiple items or ledgers. For example, a single sales invoice might have three different inventory items, two tax components, and a shipping charge.
To map this structure natively, configure your Excel template using the Multi-Row Voucher Structure:
- Unique Transaction Identifier: Use a common field, typically the Voucher Number or Invoice Number, across consecutive rows to indicate that they belong to the same voucher.
- Repeating Header Information: Repeat the Date, Voucher Number, and Party Name in every row. TallyPrime will group these rows into a single voucher.
- Isolated Line Items: Define the Stock Item, Quantity, Rate, and Taxable Value in separate rows. When TallyPrime reads the repeating Voucher Number, it appends the new item to the existing voucher rather than creating a new transaction.
Template Validation Checklist
Before importing your structured PDF files into TallyPrime, ensure the following validation standards are met to bypass parser blocks:
📅 Date Formats
All dates must be in a clean format: DD-MM-YYYY, DD/MM/YYYY, or YYYYMMDD. Avoid spelling out months (e.g. "12th April") and verify that Excel hasn't reformatted cells into custom serial integers.
🔢 Numeric Columns
Remove all commas, spacing, and currency tags (e.g. ₹, $). Negative numbers should be represented using a negative prefix (e.g. -500.00) rather than surrounding brackets.
🔤 Ledger Alignment
Ledger names in Excel must match the spelling in Tally exactly, including spaces, case sensitivity, and suffixes (e.g., "Ltd." vs "Ltd"). Any mismatch will cause Tally to reject the voucher.
4. Step-by-Step Native Mapping Configuration in TallyPrime
Once your Excel file is structured and validated, execute the mapping configuration inside TallyPrime:
- Navigate to Mapping: Go to Gateway of Tally > Import > Manage > Mapping Templates > Create.
- Choose Import Type: Select Transactions (or Masters if uploading Ledgers/Items).
- Select Source: Select your Excel sheet file path, file name, and specific sheet name.
- Enable Headers: Set Excel Data has Column Headers to Yes. Set the Start Row to 2 (skipping row 1 which holds column titles).
- Define Map Fields: In the mapping matrix, select Tally fields on the left and pair them with the corresponding Excel column names on the right. Make sure to map primary keys: Voucher Date, Voucher Type, Voucher Number, Ledger Name, and Amount.
- Save and Run: Save the mapping template. Go to Import > Transactions, choose the file, select your newly created mapping template, and click Import.
How TrulyInvoice Simplifies Template Management
While TallyPrime's native mapper provides a free interface, managing custom PDF templates manually is labor-intensive and prone to human error. Different clients and suppliers export invoices in varying schemas, requiring accountants to constantly build, tweak, and test new mapping templates.
TrulyInvoice solves this by introducing a template-independent AI parser. Instead of spending hours aligning column letters:
- No Pre-Formatting Required: Upload raw PDFs or Excel exports in any layout. The cloud validator automatically extracts transaction dates, ledger accounts, items, and tax values.
- Smart Ledger Matching: TrulyInvoice automatically matches client and vendor spelling variants to your existing Tally ledgers, prompting you to create missing ledgers in bulk.
- Secure, Instant Sync: Integrates directly with TallyPrime via a local Port 9000 connector, syncing transactions instantly with flat pricing of plans starting at ₹399/month for Lite (1,000 pages) and Plus (2,000 pages).
Chartered Accountant & Accounting Automation Specialist