Excel based GSTR-9 offline tool -Filing and Entry Instructions

Introduction to Excel based GSTR-9 offline tool

1. The Excel based GSTR-9 Offline Tool is designed to help taxpayer to prepare his GSTR-9 return offline

2. Details for following Tables of GSTR-9 return can be added by taxpayer using the offline Tool. It is not Mandatory to fill data in all worksheets. The worksheet for which no details then need to be declared, it can be left blank.

4. Outward

5. Outward

6. ITC Availed

7. ITC Rev

8. Other ITC

9. Tax Paid

10. PY trans in current FY

14. Differential Tax

15. Demand & Refunds

16. Comp DS & Goods sent on appr

17.  HSN Outward

18. HSN Inward

3. The Offline tool has following features in ‘Home’ sheet to help taxpayer in Return Preparation

a. Open Downloaded GSTR9 JSON File: To import records from downloaded JSON file. The details would be populated to respective table wise worksheets.Upon successful import of file the details would be
populated to respective table wise worksheets.

b. Generate JSON File to Upload : To generate JSON file for upload of GSTR-7 return details prepared offline on GST portal

c. Open downloaded error JSON file : To open file downloaded from GST portal from the ‘Processed with error’ link. The downloaded zipped folder consists of two JSON files. Unzip the file and use this button to open both the JSON files together in a single click by selecting both the files. Upon successful import of both the files both the details processed with errors and successfully would be populated to respective table wise worksheets. Please refer ‘Handling Error’ section for details

d.Validate Sheet: To Validate the data entered in respective worksheet of this offline Tool. Successful validation is notified to Taxpayer via pop-up while on failure of validation the cells that fail
validation would be marked in Red.

4. The high level process flow for GSTR-9 return preparation is as follows

a. Validate the details filled in various  tables using ‘validate’ button at the top of every sheet.

b. Generate JSON using ‘Generate JSON File to Upload’ option

c. Upload the generated JSON on GST Portal. Preview the details uploaded and File return on the GST portal

d. Open saved version (Yes/No):- If you select ‘No’ option then previously saved data not visible and if you select ‘Yes’ then saved data will be available in the respective worksheets

Preparing GSTR-9 return Using Offline Tool

1. Please ensure you download the latest version of  GSTR-9 Offline Tool from the GST portal. https://www.gst.gov.in/download/returns

2. Launch the GSTR-9 Excel based Offline Tool a pop up (Open saved version – Yes/No) would appear and navigate to worksheet named ‘Home’

a. On click of ‘Yes’ any previously saved data in the offline tool will be available.

b. On click of ‘No’ any data previously saved data in the offline tool will be lost and you can’t recover the data.

3. Enter your GSTIN in home sheet. Entered GSTIN would be validated only for correct structure

4. Select the applicable Financial Year from the drop-down. It is a mandatory field

5. Download GSTR-9 JSON file from GST portal after logging in to the portal from ‘Prepare Offline’ section of GSTR-9 tile.

6. Open downloaded  GSTR-9 JSON file in to the Offline tool. JSON file can’t be generated from Offline tool until GSTR-9 JSON file downloaded from the portal has been opened in the Offline tool.

7. Auto drafted details shall be populated in respective work sheets.

8. Enter additional details/edit auto drafted details as applicable in various worksheets. It is not Mandatory to fill data in all worksheets. The worksheet for which no details need to be declared can be left blank

9. Click Validate Sheet to check the status of validation. In case of validation failure; please check for cells that have failed validation and correct errors as per help text

10.Click on ‘Generate JSON File to Upload ‘ to generate JSON file for upload of GSTR-9 return details prepared offline on GST portal

Steps for uploading prepared GSTR-9 JSON file on GST portal

1. Login to GST Portal and select ‘Returns Dashboard’

2. Select applicable Financial Year and click on “Prepare Offline” option in “Annual Return” tile/Box

3. Upload the JSON prepared using offline Tool using upload option in the return dashboard.

4. The uploaded JSON file would be validated and processed.

5. In case of validation failure upon processing ; errors if any would be shown on portal

6. Post successful upload of data on GST portal ; Taxpayer to Preview the form and file GSTR-9

Notes : You may download the records successfully processed on GST portal anytime by navigating to ‘Services > Returns > Annual Return >  Financial Year > Search > Prepare Offline > Download’. On click of download a JSON file would be available for download in about 20 Mins. The downloaded JSON file may be opened in Offline Tool using ‘Open Downloaded GSTR-9 JSON File’ to view/ edit/update details. Post update of details create JSON to upload on GST portal

Error File Handling

1. In case of validation failure of one or more details upon processing of uploaded JSON file; the upload status would be updated as ‘Processed with Error’.

2. A link would be available to download a Zipped Error file beside the ‘Processed with Error’ status

3. Download the Zipped Error File and save on your system

4. Now click on the button “Open Downloaded Error JSON files” to open a file dialog box.

5. Select both the files from the unzipped folder and click on “Open”.

6. A message saying “Error Files successfully Opened” upon successful opening of files in Offline tool. A pop-up message with work sheets name with error would be shown

7. Please navigate to each worksheets and ensure all records from both the files have been successfully opened in Tool

8. Correct errors in records with error text in column ‘GST Portal Validation Errors’ in each worksheet.

9. Validate the taxguru.in  sheets again post making corrections

10. Click on ‘Generate summary’ to update the summary post details have been successfully validated for each worksheet.

11. Click on ‘Generate JSON File to upload’ to generate JSON file for upload of GSTR-9 return details prepared offline on GST portal.

12. Follow steps mentioned in GSTR-9 JSON upload on GST Portal section to file GSTR-9

For Issues with Persistent Comments In Cells

1. If the comments in cells are persistent then the following steps are to be followed:

a. Go to File -> Options

b. Select Advanced from the options pane in the left.

c. Here go to “Display” tab.

d. In the options for “For cells with comments, show”, select “Indicators only, and comments on hover”.

GSTR 9 Data Entry Instruction

Worksheet Name	GSTR-9 Table Reference	Field Name	Help Instruction
PT. II Details of Outward and inward supplies made during the financial year
4. Outward	Taxpayer has to import the JSON file in to offline tool to check the auto filled details in Table no. 4(A) to 4(G) and Table no. 4(I) to 4(L) of GSTR 9 based on the supplies reported during the relevant financial year in GSTR-1. However, the auto filled details can be edited. If you have edited/modified any auto filled value, then that value shall be considered as final after successful uploading of JSON file on to portal
	4.Details of advances, inward and outward supplies made during the financial year on which tax is payable	4A. Supplies made to un-registered persons (B2C)	1. Aggregate value of supplies made to consumers and unregistered persons (B2C supplies) on which tax has been paid shall be declared here. These will include details of supplies made to unregistered persons/consumers through E-Commerce operators, if any. 2. Details are to be declared as net of credit notes or debit notes issued during the Financial Year. 3. Table 5, Table 7 along with respective amendments in Table 9 and Table 10 of FORM GSTR-1 may be used for filling up these details. This table shall be auto filled based on the outward supplies reported in GSTR-1.
		4B. Supplies made to registered persons (B2B)	1. Aggregate value of supplies made to registered persons (including supplies made to UINs) on which tax has been paid shall be declared here. 2. These will include supplies made through E-Commerce operators but shall not include supplies on which tax is to be paid by the recipient on reverse charge basis. 3. Details of debit and credit notes taxguru.in are to be mentioned separately. Table 4A and Table 4C of FORM GSTR-1 may be used for filling up these details
		4C. Zero rated supply (Export) on payment of tax (except supplies to SEZs)	Aggregate value of exports (except supplies to SEZs) on which tax has been paid shall be declared here. Table 6A of FORM GSTR-1 may be used for filling up these details.
		4D. Supply to SEZs on payment of tax	Aggregate value of supplies to SEZs on which tax has been paid shall be declared here. Table 6B of GSTR-1 may be used for filling up these details.
		4E. Deemed Exports	Aggregate value of supplies which are in the nature of deemed exports on which tax has been paid shall be declared here. Table 6C of FORM GSTR-1 may be used for filling up these details.
		4F. Advances on which tax has been paid but invoice has not been issued (not covered under (A) to (E) above)	Details of all unadjusted advances i.e. advance has been received and tax has been paid but invoice has not been issued in the current year shall be declared here. Table 11A of FORM GSTR-1 may be used for filling up these details.
		4G. Inward supplies on which tax is to be paid on reverse charge basis	1. Aggregate value of all inward supplies (including advances and net of credit and debit notes) on which tax is to be paid by the recipient (i.e.by the person filing the annual return) on reverse charge basis. 2. This shall include supplies received from registered persons and unregistered persons on which tax is levied on reverse charge basis. This shall also include aggregate value of all import of services. Table 3.1(d) of FORM GSTR-3B may be used for filling up these details.
		4H. Sub-total (A to G above)	This field shall be auto calculated
		4I. Credit Notes issued in respect of transactions specified in (B) to (E) above (-)	Aggregate value of credit notes issued in respect of B to B supplies (4B), exports (4C), supplies to SEZs (4D) and deemed exports (4E) shall be declared here. Table 9B of FORM GSTR-1 may be used for filling up these details.
		4J. Debit Notes issued in respect of transactions specified in (B) to (E) above (+)	Aggregate value of debit notes issued in respect of B to B supplies (4B), exports (4C), supplies to SEZs (4D) and deemed exports (4E) shall be declared here. Table 9B of FORM GSTR-1 may be used for filling up these details.
		4K.  Supplies / tax declared through Amendments (+). 4L. Supplies / tax reduced through Amendments (-)	Details of amendments made to B to B supplies (4B), exports (4C), supplies to SEZs (4D) and deemed exports (4E), credit notes (4I), debit notes (4J) and refund vouchers shall be declared here. Table 9A and Table 9C of FORM GSTR-1 may be used for filling up these details
		4M. Sub-total (I to L above) 4N. Supplies and advances on which tax is to be paid (H + M) above	This field shall be auto calculated
5. Outward	Taxpayer has to import the JSON file in to offline tool to check the auto filled details in table no 5 of GSTR-9 based on the supplies reported GSTR-1s of  relevant financial year. However, you may edit the auto filled details. If you have edited/modified any auto filled value, then that value shall be considered as final after successful uploading of JSON file on to portal
	5. Details of Outward supplies made during the financial year on which tax is not payable	5A. Zero rated supply (Export) without payment of tax	1. Aggregate value of exports (except supplies to SEZs) on which tax has not been paid shall be declared here. 2. Table 6A of FORM GSTR-1 may be used for filling up these details.
		5B. Supply to SEZs without payment of tax	1. Aggregate value of supplies to SEZs on which tax has not been paid shall be declared here. 2. Table 6B of GSTR-1 may be used for filling up these details.
		5C. Supplies on which tax is to be paid by the recipient on reverse charge basis	1. Aggregate value of supplies made to registered persons on which tax is payable by the recipient on reverse charge basis. Details of debit and credit notes are to be mentioned separately. 2. Table 4B of FORM GSTR-1 may be used for filling up these details.
		5D, 5E & 5F. Exempted, Nil Rated and Non -GST Supply (includes ‘no supply’)	1. Aggregate value of Exempted, Nil Rated and Non-GST supplies shall be declared here. 2. Table 8 of FORM GSTR-1 may be used for filling up these details. The value of “no supply” shall also be declared here.
		5G. Sub-total (A to F above)	This field shall be auto calculated
		5H. Credit Notes issued in respect of transactions specified  in A to F above (-)	1. Aggregate value of credit notes issued in respect of supplies declared in 5A, 5B, 5C, 5D, 5E and 5F shall be declared here. 2. Table 9B of FORM GSTR-1 may be used for filling up these details.
		5I. Debit Notes issued in respect of transactions specified  in A to F above (+)	1. Aggregate value of debit notes issued in respect of supplies declared in 5A, 5B, 5C, 5D, 5E and 5F shall be declared here. 2. Table 9B of FORM GSTR-1 may be used for filling up these details.
		5J.  Supplies declared through Amendments (+) 5K. Supplies reduced through Amendments (-)	Details of amendments made to exports (except supplies to SEZs) and supplies to SEZs on which tax has not been paid shall be declared here. Table 9A and Table 9C of FORM GSTR-1 may be used for filling up these details.
		5L. Sub-Total (H to K above) 5M. Turnover on which tax is not to be paid  (G + L above). 5N. Total Turnover (including advances) (4N + 5M – 4G above)	These fields shall be auto calculated.
Pt. III Details of ITC for the financial year
6. ITC availed	6A. Details of ITC availed during the financial year	6A. Total amount of input tax credit availed through FORM GSTR-3B (sum total of Table 4A of FORM GSTR-3B)	1. Total input tax credit availed in Table 4A of FORM GSTR-3B by the taxpayer would be auto-populated here. 2. This field is Non-editable. Taxpayer shall import the JSON file in to offline tool to check the total amount of input tax credit availed through Form GSTR 3B. 3. Taxpayer shall import the JSON file to check the auto filled details in table no 6A.
		6B. Inward supplies (other than imports and inward supplies liable to reverse charge but includes services received from SEZs)	1. Aggregate value of ITC availed on all inward supplies except those on which tax is payable on reverse charge basis but includes supply of services received from SEZs shall be declared here. It may be noted that the total ITC availed is to be classified as ITC on inputs, capital goods and input services. 2. Table 4(A)(5) of FORM GSTR-3B may be used for filling up these details. This shall not include ITC which was availed, reversed and then reclaimed in the ITC ledger. This is to be declared separately under 6(H).
		6C. Inward supplies received from unregistered persons liable to reverse charge (other than B above) on which tax is paid & ITC availed	1. Aggregate value of input tax credit availed on all inward supplies received from unregistered persons (other than import of services) on which tax is payable on reverse charge basis shall be declared here. It may be noted that the total ITC availed is to be classified as ITC on inputs, capital goods and input services. 2. Table 4(A)(3) of FORM GSTR-3B may be used for filling up these details
		6D. Inward supplies received from registered persons liable to reverse charge (other than B above) on which tax is paid and ITC availed	1. Aggregate value of input tax credit availed on all inward supplies received from registered persons on which tax is payable on reverse charge basis shall be declared here. It may be noted that the total ITC availed is to be classified as ITC on inputs, capital goods and input services. 2. Table 4(A)(3) of FORM GSTR-3B may be used for filling up these details.
		6E. Import of goods (including supplies from SEZs)	1. Details of input tax credit availed on import of goods including supply of goods received from SEZs shall be declared here. It may be noted that the total ITC availed is to be classified as ITC on inputs and capital goods. 2. Table 4(A)(1) of FORM GSTR-3B may be used for filling up these details
		6F. Import of services (excluding inward supplies from SEZs)	1. Details of input tax credit availed on import of services (excluding inward supplies from SEZs) shall be declared here. 2. Table 4(A)(2) of FORM GSTR3B may be used for filling up these details
		6G. Input Tax credit received from ISD	1. Aggregate value of input tax credit received from input service distributor shall be declared here. 2. Table 4(A)(4) of FORM GSTR-3B may be used for filling up these details.
		6H. Amount of ITC reclaimed (other than B above) under the provisions of the Act	Aggregate value of input tax credit availed, reversed and reclaimed under the provisions of the Act shall be declared here
		6I. Sub-total (B to H above) 6J. Difference (I – A above)	These fields shall be auto calculated.
		6K. Transition Credit through TRAN-I (including revisions if any)	Details of transition credit received in the electronic credit ledger on filing of FORM GST TRAN-I including revision of thereof (whether upwards or downwards), if any shall be declared here. This field shall be auto filled based on the credit availed through Tran 1. However this field is allowed for edit.
		6L. Transition Credit through TRAN-II	Details of transition credit received in the electronic credit ledger after filing of FORM GST TRAN-2 shall be declared here. This field shall be auto filled based on the credit availed through Tran 2. However this field is allowed for edit.
		6M. Any other ITC availed but not specified above	Details of ITC availed but not covered in any of heads specified under 6B to 6L above shall be declared here. Details of ITC availed through FORM ITC-01 and FORM ITC-02 in the financial year shall be declared here.
		6N. Sub-total (K to M  above) 6O. Total ITC availed (I +  N above)	These fields shall be auto calculated.
Pt. IV Details of tax paid as declared in returns filed during the financial year
7. ITC Rev	7. Details of ITC Reversed and Ineligible ITC for the financial year	Table no 7A to 7H	1. Details of input tax credit reversed due to ineligibility or reversals required under rule 37, 39, 42 and 43 of the CGST/SGST Rules, 2017 shall be declared here. 2. This column should also contain details of any input tax credit reversed under section 17(5) of the CGST/SGST Act, 2017 and details of ineligible transition credit claimed through FORM GST TRAN-1 or FORM GST TRAN-2 and then subsequently reversed. 3. Table 4(B) of FORM GSTR-3B may be used for filling up these details. Any ITC reversed through FORM GST ITC -03 shall be declared in 7H. If taxpayer wants to specify more reversals then he can click on ‘+’ symbol to add more rows. 4. If the amount stated in Table 4D of FORM GSTR-3B was not included in table 4A of FORM GSTR-3B, then no entry should be made in table 7E of FORM GSTR-9. However, if amount mentioned in table 4D of FORM GSTR-3B was included in table 4A of FORM GSTR-3B, then entry will come in 7E of FORM GSTR-9.
8. Other ITC	8. Other ITC related information	8A. ITC as per GSTR-2A (Table 3 & 5 thereof)	1. The total credit available for inwards supplies (other than imports and inwards supplies liable to reverse charge but includes services received from SEZs) received during the relevant Financial Year and reflected in FORM GSTR-2A (table 3 & 5 only) shall be auto-populated in this table. 2. This would be the aggregate of all the input tax credit that has been declared by the corresponding suppliers in their FORM GSTR-1 3. This field shall be auto-filled based on your GSTR-2A and the same is not allowed for Edit. 4. Taxpayer shall import the JSON file to check the auto filled details in table no 6A.
		8B. ITC as per sum total of 6(B) and 6(H) above	The input tax credit as declared in Table 6B and 6H shall be auto-populated here and the same shall not be allowed to be edited.. If you want to make any changes then you need to do it in table no. 6B and 6H
		8C. ITC on inward supplies (other than imports and inward supplies liable to reverse charge but includes services received from SEZs) received during 2017-18 but availed during April to September, 2018	1. Aggregate value of input tax credit availed on all inward supplies (except those on which tax is payable on reverse charge basis but includes supply of services received from SEZs) received during the relevant Financial Year but credit on which was availed between April to September, of the next financial year shall be declared here. 2. Table 4(A)(5) of FORM GSTR-3B may be used for filling up these details.
		8D. Difference [A-(B+C)]	This field shall be auto calculated
		8E. ITC available but not availed 8F. ITC available but ineligible	1. The credit which was available and not availed in FORM GSTR-3B and the credit was not availed in FORM GSTR-3B as the same was ineligible shall be declared here. Ideally, if 8D is positive, the sum of 8E and 8F shall be equal to 8D. 2. You can’t enter negative value in this table
		8G. IGST paid on import of goods (including supplies from SEZ)	Aggregate value of IGST paid at the time of imports (including imports from SEZs) during the financial year shall be declared here
		8H. IGST credit availed on import of goods (as per 6(E) above)	The input tax credit as declared in Table 6E shall be auto-populated here and the same is not allowed for edit. If taxpayer wants to change the credit availed on import of goods, then he shall make changes in table no 6E.
		8I. Difference (G-H)	This field shall be auto calculated
		8J. ITC available but not availed on import of goods (Equal to I)	This field shall be auto calculated. If taxpayer wants to make any change in this field, then he shall make changes in table no 8G and 6E
		8K. Total ITC to be lapsed in current financial year (E + F + J)	The total input tax credit which shall lapse for the current financial year shall be computed (auto filled) in this row
9. Tax Paid	9. Details of tax paid as declared in returns filed during the financial year	1. Actual tax (including Interest, Late fee, Penalty, Others) paid through cash or ITC during the financial year shall be declared year. 2. Payment of tax under Table 6.1 of FORM GSTR-3B may be used for filling up these details. 3. Paid through Cash and Paid through ITC columns shall be auto filled based on table no 6.1 of GSTR -3B and the same is non-editable
Pt. V Particulars of the transactions for the previous FY declared in returns of April to September of current FY or upto date of filing of annual return of previous FY   whichever is earlier
10. PY trans in current FY	10. Supplies / tax declared through Amendments (+) (net of debit notes) 11. Supplies / tax reduced through Amendments (-) (net of credit notes)	Details of additions or amendments to any of the supplies already declared in the returns of the previous financial year but such amendments were furnished in Table 9A, Table 9B and Table 9C of FORM GSTR-1 of April to September of the current financial year or date of filing of Annual Return for the previous financial year, whichever is earlier shall be declared here
	12. Reversal of ITC availed during previous financial year	1. Aggregate value of reversal of ITC which was availed in the previous financial year but reversed in returns filed for the months of April to September  of the current financial year or date of filing of Annual Return for previous financial year , whichever is earlier shall be declared here. 2.  Table 4(B) of FORM GSTR-3B may be used for filling up these details.
	13. ITC availed for the previous financial year	1. Details of ITC for goods or services received in the previous financial year but ITC for the same was availed in returns filed for the months of April to September of the current financial year or date of filing of Annual Return for the previous financial year whichever is earlier, shall be declared here. 2. Table 4(A) of FORM GSTR-3B may be used for filling up these details
14. Differential tax	14. Differential tax paid on account of declaration in 10 & 11 above	Differential tax (including Interest) paid on account of transactions related to the previous financial year but declared in the returns of April to September of current FY, shall be reported in this table
Pt. VI Other Information
15. Demand & Refund	15. Particulars of Demands and Refunds	15A to 15D	1. Aggregate value of refunds claimed, sanctioned, rejected and pending for processing shall be declared here. 2. Refund claimed will be the aggregate value of all the refund claims filed in the financial year and will include refunds which have been sanctioned, rejected or are pending for processing. 3. Refund sanctioned means the aggregate value of all refund sanction orders. 4. Refund pending will be the aggregate amount in all refund application for which acknowledgement has been received and will exclude provisional refunds received. These will not include details of non-GST refund claims.
		15E to 15G	1. Aggregate value of demands of taxes for which an order has been issued by the adjudicating authority shall be declared here. 2. Aggregate value of taxes paid out of the total value of demand as declared in 15E above shall be declared here. 3. Aggregate value of demands pending recovery out of 15E above shall be declared here.
16. Comp DS & Goods sent on appr	16. Information on supplies received from composition taxpayers, deemed supply under section 143 and goods sent on approval basis	16A. Supplies received from Composition taxpayers	1. Aggregate value of supplies received from composition taxpayers shall be declared here 2. Table 5 of FORM GSTR-3B may be used for filling up these details
		16B. Deemed supply  under Section 14	Aggregate value of all deemed supplies from the principal to the job-worker in terms of sub-section (3) and sub-section (4) of Section 143 of the CGST Act shall be declared here
		16C. Goods sent on approval basis but not returned	Aggregate value of all deemed supplies for goods which were sent on approval basis but were not returned to the principal supplier within one hundred eighty days of such supply shall be declared here
17. HSN Outward	17. HSN Wise Summary of outward supplies	1. Summary of outward supplies made against a particular HSN code to be reported only in this table. 2. It will be optional for taxpayers having annual turnover up to ₹ 1.50 Cr. It will be mandatory to report HSN code at two digits level for taxpayers having annual turnover in the preceding year above ₹ 1.50 Cr but upto ₹ 5.00 Cr and at four digits‘ level for taxpayers having annual turnover above ₹ 5.00 Cr. UQC details to be furnished only for supply of goods. 3. Quantity is to be reported net of returns (sold but returned). 4. Table 12 of FORM GSTR-1 may be used for filling up details in Table 17. 5. HSN code field is always user input in offline tool. However, at the time of clicking on Validate sheet, tool shall validate the HSN code details with Master 6.If outward supply is related to services, then please select UQC as ‘OTH-OTHERS’ from the UQC drop down and enter ‘Zero’ in ‘Quantity’ column.
18. HSN Inward	18. HSN Wise Summary of Inward supplies	1. Summary of supplies received against a particular HSN code to be reported only in this table. 2. It will be optional for taxpayers having annual turnover up to ₹ 1.50 Cr. It will be mandatory to report HSN code at two digits level for taxpayers having annual turnover in the preceding year above ₹ 1.50 Cr but up to ₹ 5.00 Cr and at four digits‘ level for taxpayers having annual turnover above ₹ 5.00 Cr. UQC details to be furnished only for supply of goods. 3. Quantity is to be reported net of returns (purchased but returned). 4.HSN code field is always user input in offline tool. However, at the time of clicking on Validate sheet, tool shall validate the HSN code details with Master 5. If inward supply is related to services, then please select UQC as ‘OTH-OTHERS’ from the UQC drop down and enter ‘Zero’ in ‘Quantity’ column.