Steps showing How to Bank Statement from Excel to Tally
1. Go to Help sheet and match Tally Port in Cell 'D4' with the port set in Tally.
(Port in tally can be set by pressing F12 on Gateway of Tally --> Advanced Configuration)
2. Now click "Connect Tally" button available on Help sheet.
1. Select Bank Name from the drop-down available at Cell No. .....
2. Copy following fields from Bank Statement to Cash/Bank sheet:
a) Date of Transaction
b) Particulars(Narration) column to Particular Column available in QuikAccount Cash/Bank sheet. This particular column data will be shown in Narration field of tally.
c) Withdrawal Amount and Deposit Amount in the Withdrawal and Deposit columns given respectively.
d) You can also copy cheque no. column from bank statement in the instrument no. column given in Cash Bank sheet.
1. Now we need to give the corresponding ledger for each bank entry.
2. In the Column name Party/Expense ledger, please specify the name to which that particular bank entry belongs.
For example if payment was made to M/s R K Bros., you need to select R k Bros. ledger in this column.
3. You can fill this column by selecting ledgers from drop-down. All tally ledgers are shown in this drop down.
4. For fast searching please use the list search addin.
5. In case you want to enter any new ledger which is not available in tally. Simply type it into the cell and press enter.
1. Once you fill all the ledgers, please click on Validate button.
2. Errors if any will be highlighted.
3. If your data contain any new ledger it will be transferred to Ledger sheet. Go to ledger sheet and create new ledgers by clicking export to tally.
4. Rectify other errors if any and validate again.
5. Once validation is successful, click on Export to Tally button.
You can import Bank Statement with cheque no., cheque date.
You can import Bank Entries with bill reference (as against reference or new reference).
Import bank entries with bank allocation option of tally wherein you can put bank name of counter party.
You can use voucher no., date, reference no & date for receipt and payment entries.
Put the date in Banker's date column given in Cash/Bank sheet. This will auto reconcile your bank statement.
You can increase the decimal points as per your need. For example if you mainatin amount in 4 decimal points, you can set it in cash/bank seeet.
Kamal Jain, FCA, CCA
Prachi Shah, Senior Executive