How to Do GST Reconciliation in Excel (4 Suitable Methods)

In this article, you’ll learn how to do GST reconciliation in Excel with 4 suitable methods. The methods include using different functions like the VLOOKUP, INDEX, MATCH and SUM functions.
You’ll also learn how to do GST reconciliation using the Pivot Table feature in Excel.
GST reconciliation in Excel is a common task for businesses that deal with Goods and Services Tax. Reconciliation involves matching the data in your GST returns with the data in your books of accounts to ensure accuracy and compliance.
Below you’ll see an overview image showing the use of the VLOOKUP function to perform GST reconciliation in Excel.

Download Practice Workbook

You can download the Excel file from here for practice.

What Is GST Reconciliation in Excel?

GST, which stands for Goods and Services Tax, is a type of tax on things you buy or services you use. It’s added to the cost of items and services you get. GST reconciliation in Excel refers to the process of comparing and matching the data from various sources to ensure the accuracy and completeness of GST-related transactions recorded in a business’s accounting or financial records. This reconciliation is important to identify any discrepancies between the GST amounts declared in the tax returns and the GST amounts reported in the business’s books.

4 Suitable Methods to Do GST Reconciliation in Excel

We have the following 2 datasets. The first one is the purchase book data with the GSTIN of the supplier, trade name, invoice date, invoice number, invoice value, taxable value and different types of GST values. This dataset can normally be obtained from a company’s accounting book.

The following is the GSTR-2A data having the same heading as the purchase book data. The GSTR-2A data provides a comprehensive view of all the inward supplies received during a particular tax period. This dataset is available to registered taxpayers on the GST portal.

We’ll use these 2 datasets to do GST reconciliation in Excel by 4 suitable methods.

1. Using VLOOKUP Function

Here. we’ll use the VLOOKUP function to do GST reconciliation between 2 datasets.

• Go to purchase book dataset and copy the invoice numbers and invoice values by pressing Ctrl + C together.

• Open a new sheet and select cell B5 then paste the values by pressing Ctrl + V together.
• Put the following formula in cell D5 and apply the Fill Handle tool to extract the invoice values from the GSTR-2A dataset based on invoice numbers-

• Type the following formula in cell E5 and apply the Fill Handle tool to obtain the difference in invoice values between the purchase book data and the GSTR-2A data-

• If the difference is 0 then the reconciliation is done.
• From the differences you can now easily locate where the discrepancy occurred and can correct them.

2. Using INDEX and MATCH Functions

The process from method 1 can also be repeated using the combination of the INDEX and MATCH functions instead of the VLOOKUP function.

• Put the following formula in cell D5 and apply the Fill Handle tool to extract the invoice values from the GSTR-2A dataset based on invoice numbers-

• For getting the difference in invoice values between the purchase book data and the GSTR-2A data, enter the formula below in cell E5 and use the Fill Handle tool-

3. Using SUM Function

Here, we’ll combine the two dataset into a single one and then use the SUM function to do GST reconciliation.

• Go to purchase book dataset and copy the whole dataset with header row by pressing Ctrl + C together.

• Open a new sheet titled “Combined Sheet” and select cell B4, then paste the values by pressing Ctrl + V together.

• Again, go to the GSTR-2A dataset and copy the whole dataset without the header row by pressing Ctrl + C together.

• Open the “Combined Sheet” and select cell B24, then paste the values by pressing Ctrl + V together.
• Both datasets are now in a single sheet.

• Open a new sheet and put the following formula in cell D5 and apply the Fill Handle tool to get the total integrated tax (IGST), central tax (CGST), state tax (SGST) and taxable values as per book data-

• Type the following formula in cell D6 and apply the Fill Handle tool to get the total GST values as per GSTR-2A data-

• Now, simply sum the GST values as invoice values by putting the following formula in cell C5 and applying the Fill Handle tool-

• Finally, show the differences between the datasets by typing the following formula in cell C7 and applying the Fill Handle tool-

• The reconciliation is complete.

4. Using Pivot Table

We can use the Pivot Table feature to quickly reconcile large datasets.

• Go to the “Combined Sheet” and insert a new column titled Remarks.
• Put Book and GSTR-2A as remarks to show the source of the data.

• Select any cell, say cell B4 and go to Insert > PivotTable.

• Choose New Worksheet as the position of the Pivot Table and click OK.

• In the PivotTable Fileds window, drag the values in the required fields.
• Drag Trade Name to the Rows field and Remarks to the Columns field.
• Also, drag Invoice Value, Integrated Tax (IGST), Central Tax (CGST), State Tax (SGST) and Taxable Value to the Values field.

• The Pivot Table is ready.
• Type the following formula in cell C14. Apply the Fill Handle tool in either direction to obtain the difference of GST and total values between the purchase book data and the GSTR-2A data altogether-

Frequently Asked Questions(FAQs)

1. What precautions should I take during GST reconciliation in Excel?

Always maintain backups of your original data. Make sure your Excel formulas are accurate. Double-check your calculations and reconciliations. Document the reconciliation process for auditing purposes.

2. How often should I perform GST reconciliation in Excel?

It’s recommended to perform GST reconciliation monthly to catch any discrepancies early and ensure accurate reporting.

Conclusion

In conclusion, GST reconciliation is a vital process for businesses dealing with Goods and Services Tax. You have learned how to do GST reconciliation in Excel using several formulas and the Pivot Table feature in this article. Please let us know in the comment section if there is any query or suggestions related to this topic.