Tag: GST Reconciliation

GST Reconciliation Using Excel – Step-by-Step Tutorial for Accurate Tax Filing and Error-Free Compliance

Goods and Services Tax (GST) reconciliation is a vital process for every business registered under GST. It ensures that the data recorded in the company’s books matches the data uploaded to the GST portal by suppliers and customers. A mismatch can lead to wrong tax credits, notices from the GST department, and financial penalties.

Performing GST reconciliation using Excel is one of the most efficient and accessible ways for small and medium businesses (SMEs) to ensure data accuracy without expensive software. Excel allows you to organize, compare, and analyze large data sets with formulas, conditional formatting, and pivot tables.

This step-by-step tutorial will guide you through the process of performing GST reconciliation using Excel, helping you understand how to identify mismatches, automate checks, and maintain clean compliance records.

What is GST Reconciliation?

GST reconciliation is the process of matching details of sales and purchases recorded in your books of accounts with the details available on the GST portal (via GSTR-2A/2B, GSTR-1, and GSTR-3B).

The purpose is to ensure that:

Input Tax Credit (ITC) claimed matches supplier filings
All outward supplies (sales) are properly reported
No duplication or omission of invoices occurs
Tax liability is correctly calculated

A proper reconciliation process eliminates discrepancies and reduces the risk of compliance issues.

Why GST Reconciliation is Important

Reason	Explanation
Accurate ITC Claim	Ensures that Input Tax Credit is claimed only on valid and matched invoices.
Avoid GST Notices	Prevents discrepancies that may trigger departmental scrutiny or notices.
Timely Returns Filing	Speeds up the filing process of GSTR-1, GSTR-3B, and annual returns.
Financial Accuracy	Helps maintain accurate accounting books and audit compliance.
Vendor Compliance Tracking	Ensures vendors are filing GST returns correctly for your ITC claims.

Data Required for GST Reconciliation in Excel

Before beginning the reconciliation process, collect the following data sets:

Data Source	File Type / Details
GSTR-2A or GSTR-2B	Download from GST portal (in Excel or JSON format)
Purchase Register	Extract from your accounting software or manual books
GSTR-1	Details of outward supplies filed by you
GSTR-3B	Tax payment and summary return filed by you
Vendor Master	Supplier details, GSTIN, and contact data

Once you have these files, save them in a single folder for easy reference.

Step-by-Step Guide to GST Reconciliation Using Excel

Step 1: Import Data into Excel

Open a new Excel workbook.
Create multiple sheets and rename them as:
- GSTR2A
- Purchase Register
- Reconciliation Report
Paste the GSTR-2A data in the “GSTR2A” sheet.
Paste your purchase register data in the “Purchase Register” sheet.

Each sheet should include these essential columns:

Column Name	Description
Supplier Name	Vendor providing goods or services
GSTIN	Supplier’s GST Identification Number
Invoice Number	Unique number for each invoice
Invoice Date	Date of issue
Taxable Value	Value before GST
Tax Amount	Total GST (IGST, CGST, SGST)
Total Invoice Value	Taxable value + GST
Place of Supply	State code for intra/inter-state
Filing Status	Whether uploaded to portal (optional)

Step 2: Standardize Data Formatting

Before comparing, ensure both datasets (GSTR-2A and Purchase Register) have the same column headers, spelling, and data formats.

Use Text to Columns to correct mismatched formats.
Format Invoice Date columns to DD-MM-YYYY.
Trim extra spaces using the formula =TRIM(A2) if data is inconsistent.
Convert all invoice numbers to uppercase for uniformity:
=UPPER(A2)

Step 3: Match Invoices Using Excel Formulas

Now we compare invoices between the Purchase Register and GSTR-2A to identify matches or mismatches.

In the Reconciliation Report sheet:

Use the formula below to check if an invoice exists in GSTR-2A: =IF(COUNTIFS(GSTR2A!C:C, [@[Invoice Number]], GSTR2A!B:B, [@[GSTIN]])>0,"Matched","Not Matched")
This formula checks both the Invoice Number and GSTIN to confirm if the invoice exists in the supplier filing.

You can extend this formula to also check the Invoice Value:

=IF(AND(COUNTIFS(GSTR2A!C:C,[@[Invoice Number]],GSTR2A!B:B,[@[GSTIN]])>0,ABS(GSTR2A!F:F-[@[Total Value]])<1),"Matched","Mismatch in Value")

Step 4: Use Conditional Formatting for Quick View

To highlight mismatches visually:

Select your reconciliation results column.
Go to Home → Conditional Formatting → Highlight Cell Rules → Text That Contains.
Enter “Not Matched” and choose a red fill.
Enter “Matched” and choose a green fill.

This helps instantly identify which invoices are mismatched.

Step 5: Generate Summary Using Pivot Table

After formula comparison, create a Pivot Table to summarize the results:

Status	Count of Invoices	Total Value (₹)
Matched	420	58,50,000
Mismatch	36	5,20,000
Missing in GSTR-2A	18	2,10,000
Missing in Books	10	1,45,000

To create this:

Select your reconciliation data.
Go to Insert → PivotTable.
Drag “Status” to Rows and “Invoice Number” to Values (Count).
Drag “Total Value” to Values (Sum).

Step 6: Analyze and Correct Mismatches

Now identify the causes of mismatches:

Mismatch Type	Possible Reason	Action Required
Missing in GSTR-2A	Supplier did not upload invoice	Contact supplier and request correction
Missing in Books	Recorded only in portal	Verify for duplicate or forgotten entry
Value Mismatch	Typographical or rounding errors	Cross-check and correct in accounting books
GSTIN Error	Wrong GST number entered	Correct GSTIN in records
Date Difference	Invoice date mismatch	Verify correct financial period

After analyzing, adjust entries in your books or communicate with vendors for corrections.

Step 7: Prepare Final GST Reconciliation Report

Your Final Report should include the following summary table:

Reconciliation Parameter	Value
Total Purchase Invoices	484
Invoices Matched	420
Invoices with Mismatch	36
Invoices Missing in GSTR-2A	18
Invoices Missing in Books	10
Accuracy Percentage	86.7%
ITC Eligible Value (Matched)	₹58,50,000
ITC Discrepancy Value	₹8,75,000

To calculate Accuracy Percentage, use:
= (Matched Invoices / Total Invoices) * 100

This report helps management and auditors to understand the level of compliance at a glance.

Tips for Efficient GST Reconciliation in Excel

Always use unique invoice numbers to avoid duplication.
Perform reconciliation monthly to reduce year-end workload.
Backup data regularly in Excel and on cloud storage.
Use Excel Table Format (Ctrl + T) to make formula references dynamic.
Avoid manual typing; copy-paste directly from GSTR files to prevent errors.
Use Data Validation to restrict incorrect GSTIN entries.
Keep GSTR-2A download date noted, as data may change with supplier revisions.

Advantages of Doing GST Reconciliation in Excel

Feature	Benefit
Flexibility	Customize columns, formulas, and filters easily
Low Cost	No need for external reconciliation tools
Transparency	Every step can be reviewed and audited
Accuracy	Formula-based checks minimize manual mistakes
Speed	Pivot tables and lookups make comparisons fast

Common Mistakes to Avoid

Not reconciling invoices with the same financial year
Ignoring small rounding differences
Using inconsistent invoice formats
Forgetting to update GSTR-2A download files regularly
Failing to verify the GSTIN master list before reconciliation

Conclusion

Performing GST Reconciliation using Excel is one of the most practical, transparent, and cost-effective ways for businesses to maintain compliance. With a structured workbook, accurate formulas, and monthly reviews, you can ensure that your tax credits are accurate, your books are clean, and your GST filings are audit-ready.

By following this step-by-step Excel process, you’ll not only save time and cost but also strengthen your tax reporting accuracy. Regular reconciliation builds trust with vendors, improves cash flow by ensuring proper ITC claims, and keeps your organization compliant with GST laws.

Disclaimer

The information provided in this article is for educational purposes only. While every effort has been made to ensure accuracy, users are advised to verify data and compliance rules as per the latest GST notifications and their business requirements. The author is not responsible for any financial or legal consequences arising from the use of this information.

November 8, 2025

How Excel & Automation Streamline GST Filing – Complete Guide to E-Invoicing, GSTR-3B & Tax Reconciliation

Under India’s GST regime, accuracy and timeliness are non-negotiable. Every month, businesses must prepare and upload data for GSTR-1, GSTR-3B, and increasingly, e-invoices. Manual preparation—especially when handling hundreds of invoices—can lead to omissions, wrong tax rates, and late filing penalties.

By combining Excel’s flexibility with light automation (macros, Power Query, pivot tables), even small and medium businesses can achieve an ERP-like workflow: cleaner data, faster reconciliation, and fewer last-minute errors.

Why Automate GST Filing?

Manual Challenge	Risk	Benefit of Automation
Multiple invoices across branches	Missed invoices	Consolidated real-time view
Manual tax rate application	Wrong liability	Pre-validated rates & logic
Repetitive reconciliation	Fatigue & keying errors	One-click match with supplier GSTR-2B
Last-minute return prep	Late filing penalties	Rolling reconciliation each month

Automation turns GST compliance from a deadline-driven scramble into a routine, predictable task.

Core GST Tasks Perfect for Excel + Automation

1. E-Invoicing Preparation – Step-by-Step

The GST Invoice Registration Portal (IRP) only accepts JSON files following a strict schema. A robust Excel model can prepare, validate, and export e-invoices quickly.

Understand the Schema

Field	Mandatory	Example
Invoice No	Yes	INV-2025/007
Date	Yes	08-09-2025
Seller GSTIN	Yes	29ABCDE1234F1Z5
Buyer GSTIN	Yes (B2B)	27PQRS5678G2H6
Place of Supply	Yes	Maharashtra
HSN Code	Yes	84081010
Taxable Value	Yes	50,000
CGST, SGST, IGST	Conditional	CGST 9%, SGST 9%
Item Description	Yes	Industrial Pump

Build the Excel Template

Invoice No	Date	Seller GSTIN	Buyer GSTIN	Place of Supply	Item Desc	HSN	Qty	Rate	Taxable Value	CGST%	SGST%	IGST%

Format Date as DD-MM-YYYY
Lock Seller GSTIN if it never changes
Maintain a rate master (5%, 12%, 18%, 28%)

Add Data Validation

GSTIN: =AND(LEN(A2)=15)
HSN: dropdown list from a HSN_Master sheet
Dates (current FY): =AND(A2>=DATE(2025,4,1),A2<=DATE(2026,3,31))

Automate Tax Calculation

=H2*I2               'Taxable Value
=IF(State="Same",TaxableValue*9%,0)   'CGST
=IF(State="Same",TaxableValue*9%,0)   'SGST
=IF(State<>"Same",TaxableValue*18%,0) 'IGST

Sample Rows

Invoice No	Date	Seller GSTIN	Buyer GSTIN	Place	Item	HSN	Qty	Rate	Taxable	CGST	SGST	IGST
INV-2025/001	08-09-2025	29ABCDE1234F1Z5	27PQRS5678G2H6	Maharashtra	Industrial Pump	84081010	2	25000	50000	4500	4500	0
INV-2025/002	08-09-2025	29ABCDE1234F1Z5	29LMNO9123Z5X4	Karnataka	Control Panel	85371000	1	70000	70000	0	0	12600

Export JSON via VBA Macro

Sub ExportToJSON()
    Dim rng As Range, i As Long, json As String
    Dim fso As Object, txt As Object
    
    json = "{""InvoiceList"":["
    Set rng = Sheet1.Range("A2:A51")
    For i = 1 To rng.Rows.Count
        If rng.Cells(i, 1).Value <> "" Then
            json = json & "{""No"":""" & rng.Cells(i, 1).Value & """,""Dt"":""" _
                   & Format(rng.Cells(i, 2).Value, "dd/mm/yyyy") & """," _
                   & """Buyer"":""" & rng.Cells(i, 4).Value & """},"
        End If
    Next i
    json = Left(json, Len(json) - 1) & "]}"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set txt = fso.CreateTextFile(Environ("USERPROFILE") & "\Desktop\eInvoice.json", True)
    txt.Write json: txt.Close
    MsgBox "JSON File Saved!"
End Sub

Tip: Validate the JSON in the NIC Offline Tool before uploading. Archive both Excel & JSON for audit readiness.

2. GSTR-3B Auto-Computation

Create pivot tables splitting taxable values by rate
Link input tax credit (ITC) sheets to auto-calculate eligible credits
Use SUMIFS to consolidate Table 3.1 (Outward Supplies) & Table 4 (ITC)

3. Input vs Output Tax Reconciliation

Match supplier invoices (GSTR-2B) with purchase register using VLOOKUP/XLOOKUP
Apply conditional formatting for mismatches
Generate exception reports for follow-up

4. Compliance Dashboards

Use Power Query to import Tally data
Build slicers for state, branch, GST rate
Chart month-wise liability & pending ITC

Practical Workflow Example

Export sales & purchase registers from TallyPrime
Clean & merge in Power Query
Apply tax formulas and reconciliation rules
Generate variance report against GSTR-2B
Export JSON for e-invoices or prepare GSTR-3B

Tools That Enhance Efficiency

Tool	Use Case
Power Query	Automates repetitive cleaning
VBA Macros	JSON export, exception reporting
Pivot Table	Quick summaries & rate-wise totals
Power BI	Multi-branch dashboards

Tips for Error-Free Automation

Lock tax rate tables to avoid accidental edits
Validate GSTIN, dates, and HSN codes before export
Keep a monthly reconciliation log
Test macros on sample data before production use

Conclusion

Excel isn’t just for number crunching—it’s a cost-effective automation hub for e-invoicing, GSTR-3B computation, and ITC reconciliation. With a well-designed template, data validation, and light VBA scripting, GST filing becomes faster, cheaper, and error-resistant.

September 8, 2025