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.

How to do 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.

Showing purchase book data to Do GST reconciliation in Excel

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.

Showing GSTR-2A data to Do GST reconciliation in Excel

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.

Copying invoice numbers and invoice values from purchase book dataset

  • 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-
=VLOOKUP(B5,'GSTR-2A'!$E$4:$F$23,2,0)

Applying VLOOKUP function to Do GST reconciliation in Excel

  • 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-
=C5-D5
  • 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.

Obtaining the difference of invoice values to Do GST reconciliation in Excel


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-
=INDEX('GSTR-2A'!$F$5:$F$23,MATCH(B5,'GSTR-2A'!$E$5:$E$23,0))
  • 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-
=C5-D5

Applying INDEX and MATCH functions to Do GST reconciliation in Excel

Formula Breakdown

  • MATCH(B5,’GSTR-2A’!$E$5:$E$23,0)– The MATCH function is used to find the position of a specified value (B5 in this case) within a given range (‘GSTR-2A’!$E$5:$E$23).
  • INDEX(‘GSTR-2A’!$F$5:$F$23,MATCH(B5,’GSTR-2A’!$E$5:$E$23,0))– The INDEX function is used to return a value from the range ‘GSTR-2A’!$F$5:$F$23 using the row number obtained from the MATCH function.

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.

Copying whole purchase book dataset

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

Pasting whole purchase book dataset in “Combined Sheet”

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

Copying whole GSTR-2A dataset

  • 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.

Pasting whole GSTR-2A dataset in “Combined 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-
=SUM('Combined Sheet'!G5:G23)

Applying SUM function to get total GST 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-
=SUM('Combined Sheet'!G24:G42)

Applying SUM function to get 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-
=SUM(D5:G5)

Applying SUM function to get total invoice values

  • Finally, show the differences between the datasets by typing the following formula in cell C7 and applying the Fill Handle tool-
=C5-C6
  • The reconciliation is complete.

Applying SUM function to get the difference of invoice and GST values to Do GST reconciliation in Excel


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.

Inserting Remarks column in “Combined Sheet”

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

Selecting PivotTable option from Insert menu for inserting a Pivot Table

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

Choosing New Worksheet as the position of the Pivot Table

  • 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.

Inserting different values as fields in PivotTable Fileds window

  • 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-
=C5-D5

Obtaining the difference of invoice and GST values from the Pivot table to Do GST reconciliation in Excel


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.

Get FREE Advanced Excel Exercises with Solutions!
Sajid Ahmed
Sajid Ahmed

Sajid Ahmed, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, assumes the position of an Excel & VBA Content Developer at ExcelDemy. A self-motivated individual, his profound interest in research and innovation aligns seamlessly with his passion for Excel. In this role, Sajid not only adeptly addresses challenges but also demonstrates enthusiasm and expertise in gracefully navigating complex situations. This underscores his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo