1. Home
  2. How do I Import Data From Excel to Tally
  3. How do I Import Purchase With Inventory Data From Excel to Tally

How do I Import Purchase With Inventory Data From Excel to Tally

Watch Demo Video

Follow Step by Step process

1. Login in to your Excel2Tally.in account

 

2. Select Template “Purchase With Inventory”

 

3. Click “Process Data”

 

4. Add Company & Select Template.

 

5. Download Sample file format

6. Once you are ready with your excel data then Choose file and Upload Excel

STEP – 1

 

 

7.Now Click Process Excel

STEP – 2

 

 

8. Now Click Download XML

 

STEP – 3

 

9. Now import the converted XML to Tally, using option import data > Vouchers

10. In case data is not imported in Tally than check Tally.imp file for data import log report.

Column Wise Explanation

The column names given in RED are compulsory columns and are required to be filled where as the column names given in BLACK are optional.

COLUMN NAME EXPLANATION EXCEL TEMPLATE EXAMPLE
INVOICE DATE Invoice date is the date on which an invoice for goods is purchased  which is usually the same day on which the goods are received

Tally is very flexible and thus allows also allows the user to present the invoice date in various formats like wise it will be expressed/represented in tally as DD/MM/YYYY OR DD-MM-YYYY.

Recommended formats are
01/02/2018 OR 01-02-22018
INVOICE NO. The invoice number is any number (or any other character) assigned to that bill by the issuing company so that they can track it later .You can also set the invoice number as per your choice, tally also supports prefix & suffix.

In tally invoice can be represented as

First part : Prefix – ST/
Second Part : Invoice Number – 00085
Third Part is : Suffix – /2015-16

After the introduction of GST Government has mended the invoice number length should not exceed 16 characters(as per GST recommendation)but however tally is flexible and will support more than 16 characters also

INV/GST/00112/1819, INV/DEL/112/1819
SUPPLIER INVOICE NO This is the Invoice number of the supplier printed on his invoice, In Tally you can mention the same in the field provided Supp – 425
SUPPLIER INVOICE DATE This is the day on which the supplier has issued the invoice Recommended formats are
01/02/2018 OR 01-02-22018
VOUCHER TYPE Voucher type pertains to Accounting, Inventory and Payroll. These are used for recording various transactions according to the user needs.

In Tally ERP9 Voucher type is the data entry form or screen where we are entering different types of transactions like Sales,Purchase,Receipt etc.
There are 18 vouchers already created in Tally Software,which is divided into four parts

1) ACCOUNTING VOUCHER

2) INVENTORY VOUCHER

3) PAYROLL VOUCHER

4) ORDER VOUCHER

(THE ABOVE VOUCHERS WILL BE DISPLAYED BASED ON THE FEATURE YOU HAVE ENABLED)

Purchase FY 18-19, purchase Delhi, Sales Amazon, Sales Local
TIN NO. TIN stands for Taxpayer Identification Number, is unique number allotted by Commercial tax department of respective State. It’s an eleven digit number to be mentioned in all VAT transactions and correspondence. TIN number is used to identify dealers registered under VAT. 2744587275V
CST NO. CST (Central Sales Tax) ) is a form of indirect tax imposed only on goods sold from one state to another state, 2744587275C

24AABCV123EDG54

GSTIN/UIN All of these businesses will be assigned a unique Goods and Services Tax Identification Number (GSTIN). (It should not exceed 15 characters)
RECEIPT NOTE NO. This voucher type is used for recording goods received from the supplier goods received note. Record of goods received at the point of receipt. This record is used to confirm all goods have been received and often compared to a purchase order before payment is issued. GRN – 001
RECEIPT NOTE DATE The Receipt Note Date is a document that certifies the Receipt of goods to the buyer, who must sign it to make it clear that the goods have been Received in accordance with the conditions established. Date of issue of the document and date of receipt of the goods. Recommended formats are
01/02/2018 OR 01-02-22018
ORDER NO Number of the order
In tally a order number is the reference to have a link between transactions. For example, if order number is being selected in the Receipt Note, it will help you to automatically display the details of the items in the Purchase Invoice.
The tracking number pop-up list appears after you have entered the name of the item.
ORD-25964A
ORDER DATE The date of the specific order.
It is a written authorization for a vendor to supply goods or services at a specified price over a specified time period.
Recommended formats are
01/02/2018 OR 01-02-22018
LR NO. LR number represents a receipt which is known as Lorry Receipt (LR) LR-12332
DISPATCH THROUGH This will be the method used for sending / receiving the material like by Road, Rail, Sea, Air, Transporter Name, etc V-trans, By Road, By Air, By Rail
DESTINATION The end place. the place where the goods have to be delivered Delhi, Warehouse A
TERMS OF PAYMENT Term of payment means Number of days In tally,Payment terms are the length of time that a customer has to pay your invoices. This number, in days, is the length of time after the invoice date or month end the payment needs to be paid by. This will be the payment due date.
For example; payment terms of 14 days and the invoice issue date is 01/01/2013, then the invoice due date with be 15/01/2012.
30 days, 60 Days, 90 Days
TERMS OF DELIVERY Terms of delivery is a information given by a customer to the supplier.
There are multiple types of terms of delivery which can be explained as
1) If customer is demanding fragile goods he/she would want it in a bubble wrap2) If any liquid pack it in  1 liter bottle3)Dispatch the goods to my warehouse etc
4) COD
Ex-Works, Despatch through XYZ transporter, COD
ITEM NAME Item Name is basically the name of the product you are purchasing Methonal-524188,Thermostat-21540,Cotton Frabic-2541 etc…
ITEM DESCRIPTION Item description is basically detailed information about the specific item like nature,size,colour and so on Best Quality, Size – 12, Color – Blue
TAX RATE It is a tax rate specified for the product every product has a different tax rate 5%,18%,21%,25%and so on…
BATCH NO. Batch No means a specific quantity or material that is intended to have uniform character and quality, within specified limits, and is produced according to a single manufacturing order during the same cycle of manufacture. B-123
QTY Qty is defined as an abbreviation for “quantity” and means the number or amount of something that quantify and u wanted to Sell or Purchase 5 Nos, 5 KGS, 5 Ltrs, 5 Pcs
UOM UOM stands for UNIT OF MEASURE.
A unit of measure is assigned to all inventory items and must be defined before a product can be entered. Each product will have a stocking unit of measure a purchasing unit of measure.
Pcs, Nos, Kgs, Ltrs, Box, etc
RATE A fixed price paid or charged for Goods or Services Per Unit Rs 12500/-
DISCOUNT% In Tally ERP9 Invoice Discount can be given on the product, Discount can be also referred to as a type of deduction in product base price. 5%, 8%, 10%,15%
AMOUNT This is a GROSS amount of the product RS 24000/-
PURCHASE LEDGER Purchase and sales are part of trading account; hence these are most important ledgers in overall accounting process. Tally ERP 9 by default provides Purchase Account as group.You can create unlimited Purchase ledger based on your business Purchase @ 18%, Purchase Interstate @ 18%, purchase Local @ 28%, etc
OTHER CHARGES_1 AMOUNT Other Charges is a amount which is charged over and above the gross value of the product. Other charges can be positive or negative.For Eg discount can be negative. Rs 2800/-
OTHER CHARGES_1 LEDGER Other charges in Tally are ideally created under Purchase or Expenses group Shipping Charges,Delivery Charges,Packing Charges and so on…
OTHER CHARGES_2 AMOUNT Other Charges is a amount which is charged over and above the gross value of the product. Other charges can be positive or negative. For Eg discount can be negative. Rs 4200/-
OTHER CHARGES_2 LEDGER Other charges in Tally are ideally created under Sales or Expenses group Gift Wrapping Charges, Fast Delivery,Shipping and so on
CGST AMOUNT CGST stands for Central Goods & Services Tax which is a component of the tax reform that will be levied by the Central government on all services and goods CGST rate will be applicable on intra-state transactions (within state) Rs 1250/-
CGST LEDGER This is a Tax ledger, ideally created under Duties & Taxes group in Tally. You can give name of the ledger as per your preference and business requirements CGST @5% , CGST @9%, CGST @10%
SGST AMOUNT SGST stands for State Goods & Services Tax which is a component of the tax reform that will be levied by the State government on all services and goods
SGST rate will be applicable on intra-state transactions (within state)
Rs 1250/-
SGST LEDGER This is a Tax ledger, ideally created under Duties & Taxes group in Tally. You can give name of the ledger as per your preference and business requirements SGST @5% , SGST @9%, SGST @10%
IGST AMOUNT IGST Amount stands for Integrated Goods & Services Tax amount payable.
IGST rate will be applicable on inter-state transactions (between the state)
Rs 2500/-
IGST LEDGER This is a Tax ledger, ideally created under Duties & Taxes group in Tally. You can give name of the ledger as per your preference and business requirements IGST @10% , IGST @18%, SGST @20%
COST CENTER A part of an organization to which costs may be charged for accounting purposes.

The cost center in Tally.ERP 9 refers to an organizational unit to which costs or expenses can be allocated during transactions while the cost category is used to accumulate costs or profits for parallel sets of cost centers.

HR Department,
Sales Department, Engineering Department
GODOWN Godown is a place where stock items are stored. You can specify where the stock items are kept. Similarly,Tally is very user friendly and thus provides you options to create mulitple godowns but however by default tally creates single godown MAIN LOCATION. Main location, Bhiwandi Warehouse, Amazon FBA, etc
NARRATION A small brief explanation. Being goods purchased

E-Way Bill Helper

Column Name Recommended Value
Sub Type Exhibition or Fairs
Export
For Own Use
Job Work
Lines Sales
Others
Recipient Not known
SKD/CKD
Supply
Document Type Tax Invoice
Bill of Entry
Bill of supply
Challan
Credit Note
Others
Tax Invoice
Status of E-way Bill Cancelled by me
Generated by me
Generated by other party on my GSTIN
Rejected by me
Rejected by other party
Mode Road
Air
Rail
Ship

Important Note

– Please make sure that the ledger masters are already existing in your tally data

– If possible create separate purchase voucher types with voucher numbering method manual & prevent duplicate = YES

– Option “Ignore errors during data import” should be YES. Find this option on Gateway of Tally > Press F12 > General

In case of any query, kindly write us on support@excel2Tally.in

For more details visit www.excel2tally.in

 

Updated on July 26, 2018

Article Attachments

Was this article helpful?

Related Articles

Add A Comment

Help-Desk