KineMaster-mod-apk

Gstr 2a And 3b Reconciliation In Excel Format [best] Download May 2026

While there is no single "official" reconciliation file on the GST Portal

, you can download the raw data in Excel format from the portal and use professional templates or Excel functions like Pivot Tables to reconcile them. GST.gov.in 1. Download Raw Data from GST Portal

To begin your reconciliation, first download your actual return data in Excel format: For GSTR-2A: Log in to the GST Portal

> Services > Returns > Returns Dashboard. Select the Financial Year and Period. Under the GSTR-2A tile, click and select Generate Excel File For GSTR-3B:

Similarly, in the Returns Dashboard, go to the GSTR-3B tile, click , and use the Download GSTR-3B (Excel) button to get your summary data. 2. Available Reconciliation Templates & Tools

Several platforms provide free or sample Excel utilities to automate the matching of GSTR-3B summary ITC against GSTR-2A/2B invoice-level data: GSTZen Sample Report: Provides a Consolidated Reconciliation Excel that compares GSTR-1, 2A, and 3B data. TaxGuru Utility: Free GSTR 2B/2A Matching Excel Utility designed for regular taxpayers. Bizwiz Automated Tool: Provides an Excel Automated Tool to reconcile portal data with your own records for free. 3. DIY Excel Reconciliation Method

If you prefer building your own sheet, experts typically suggest these two methods: Manual > Viewing Form GSTR-2B - GST Portal

Step 3: Use Excel Functions for Reconciliation

A. Find missing invoices in GSTR-2A (vendor not filed)

In Purchase Register sheet, add a column: In GSTR-2A?

=IF(COUNTIFS(GSTR2A!B:B, B2, GSTR2A!C:C, C2, GSTR2A!D:D, D2)>0, "Yes", "No")

(B2 = Supplier GSTIN, C2 = Invoice No., D2 = Date)

Filter “No” – these are invoices where ITC cannot be claimed.

B. Find amount mismatches

Add column: Difference in Taxable Value

= ABS(Purchase_Reg_Taxable - VLOOKUP(Invoice_No, GSTR2A_Range, Column_Index, 0))

Better to use SUMIFS for multi-condition match. gstr 2a and 3b reconciliation in excel format download

C. Compare ITC claimed (3B Table 4) vs ITC available (2A)

| ITC Type | Available in 2A (sum) | Claimed in 3B | Difference | |----------|----------------------|---------------|------------| | IGST | =SUMIFS(...) | Manual entry | Available - Claimed | | CGST | | | | | SGST | | | |

Automating Reconciliation Beyond Excel

While Excel is powerful for up to 5,000 invoices per month, consider tools like:

  • Zoho Books (auto-reconciliation)
  • ClearTax (GST compliance suite)
  • MasterGST (offline Excel utility with API)

But for small and medium taxpayers, the GSTR-2A and 3B reconciliation Excel format remains the most cost-effective, transparent, and audit-friendly method.

8. Excel Formulas and Features to Use

  • Key formulas: VLOOKUP/XLOOKUP/INDEX-MATCH for direct mapping.
  • SUMIFS for aggregation by GSTIN/tax head.
  • IF+ABS for tolerance checks: =IF(ABS(A–B)<=Tolerance,"Match","Mismatch")
  • TEXT/DATE functions to normalize dates.
  • CONCAT/LEFT/RIGHT for key creation.
  • FILTER (or AutoFilter) to extract exceptions.
  • PivotTables for summary analytics.
  • Conditional Formatting to highlight mismatches and aged items.
  • Power Query (Get & Transform) for robust import, transformations, deduplication, and merge operations.
  • Optional: VBA macros to automate refresh, run matching passes, and produce the Reconciliation_Report and Audit_Log.

Conclusion

Reconciling GSTR-2A with GSTR-3B is no longer optional – it’s the backbone of ITC governance. With the ready structure and formulas shared in this article, you can build or download a robust Excel workbook that saves hours, avoids penalties, and keeps your GST compliance flawless.

Immediate Action Items:

  1. Download last month’s GSTR-2A and GSTR-3B data
  2. Set up the Excel columns as shown above
  3. Identify if any mismatch exceeds 5% limit
  4. Reverse excess ITC or ask suppliers to file missing returns

By integrating this simple Excel workflow into your monthly closing process, you transform reconciliation from a headache into a 30-minute routine.

Need a ready-made template? Copy the column headers provided in this article into a new Excel sheet, add VLOOKUPs, and you have your own GSTR-2A vs GSTR-3B reconciliation tool – completely free.

GSTR 2A and 3B reconciliation is a critical process for every GST-registered taxpayer. It ensures that the Input Tax Credit (ITC) claimed in the monthly return (GSTR-3B) matches the data uploaded by suppliers (GSTR-2A). Discrepancies can lead to tax notices, interest penalties, or loss of credit.

To help you streamline this, we have detailed the reconciliation process and provided a structure for an automated Excel tool. Why GSTR 2A and 3B Reconciliation is Critical

Avoid Tax Notices: Prevents notices from the GST department regarding excess ITC.

Maximize ITC: Ensures you don’t miss out on credit for which you have paid taxes.

Supplier Compliance: Helps identify suppliers who haven't uploaded invoices or filed returns. While there is no single "official" reconciliation file

Legal Requirement: Rule 36(4) of the CGST Rules mandates strict adherence to the data appearing in GSTR-2B/2A. Step-by-Step Reconciliation Process 1. Data Preparation

Download your GSTR-2A/2B reports from the GST portal for the relevant period. Simultaneously, export your Purchase Register (Books) and GSTR-3B filings for the same duration. 2. Standardize Formats

Ensure that GSTINs, Invoice Numbers, and Dates are in the same format across both sheets. Use Excel functions like TRIM to remove extra spaces. 3. Match Invoices Compare the data points. Look for: Exact matches (GSTIN + Invoice No + Amount). Partial matches (Different invoice numbering styles). Missing invoices (In books but not in 2A, or vice versa). 4. Identify Discrepancies Categorize differences into: Timing differences: Supplier filed in a later month. Data entry errors: Wrong GSTIN or invoice amount. Non-compliance: Supplier has not filed at all. Building Your Excel Reconciliation Tool

If you are looking for a GSTR 2A and 3B reconciliation in excel format download, you can build a robust template using these columns: Sheet 1: Data from Books Vendor Name Invoice No Taxable Value Sheet 2: Data from GSTR-2A Supplier Name Invoice No Taxable Value Sheet 3: Reconciliation Summary

Use VLOOKUP or XLOOKUP to compare the two sheets based on the Invoice Number or a Unique Key (GSTIN + Invoice No). Essential Excel Formulas for Reconciliation:

=VLOOKUP(A2, 'GSTR-2A'!A:H, 5, 0) – To pull taxable value from 2A.

=IF(B2=C2, "Matched", "Difference") – To flag discrepancies.

=SUMIFS(...) – To create a monthly summary of ITC available vs. claimed. Common Challenges and Solutions

Invoice Number Mismatch: Suppliers often add prefixes (e.g., "INV/001" vs "001"). Use the RIGHT or MID function in Excel to extract numeric values for better matching.

Rounding Off: Use the ROUND function to ignore small differences (like ₹1 or ₹2) that occur due to decimal points.

Large Data Volume: For businesses with thousands of invoices, use Power Query in Excel to automate the cleaning and merging of datasets.

✅ Pro Tip: Always reconcile your GSTR-2A data with your GSTR-3B filings before the end of the financial year to make any necessary amendments in the September return.

The reconciliation of ensures that the Input Tax Credit (ITC) claimed in monthly returns (3B) matches the data uploaded by suppliers (2A). You can download several specialized Excel utilities to automate this process. Direct Excel Format Downloads GSTZen Consolidated Report Step 3: Use Excel Functions for Reconciliation A

: A comprehensive sample Excel file that compares GSTR-1, 2A, and 3B data against purchase registers. Download Sample from GSTZen Vyapar App Formats

: Offers free, simplified Excel reconciliation formats for GSTR-3B and GSTR-2A. Access Vyapar Templates TaxGuru Utility

: A community-contributed Excel tool that identifies missing ITC and shortage/excess filing by suppliers. Download from TaxGuru CAclubindia Utility

: An advanced macro-enabled utility specifically designed for GSTR-2A reconciliation. Download from CAclubindia How to Perform Reconciliation in Excel Download GSTR-2A Data : Log in to the Official GST Portal , navigate to Services > Returns > Returns Dashboard

, select the tax period, and click "Generate Excel File" under GSTR-2A. Prepare Purchase Register

: Export your purchase data from your accounting software (e.g., Tally) into a standard Excel format. Use Key Formulas VLOOKUP / XLOOKUP

: To match invoice numbers and GSTINs between your books and the 2A data.

: To total supplier-wise ITC to compare with the consolidated 3B claim. : To identify small rounding differences: =IF(ABS(A2-B2)<=1,"Matched","Mismatch") Identify Mismatches : Invoices present in both books and 2A. Missing in 2A : Supplier hasn't filed; follow up with them immediately. Missing in Books

: You may have missed recording an invoice for which credit is available. Important Reconciliation Categories B2B Invoices

: The most common area for mismatch where both parties are registered. Eligible vs. Ineligible ITC

: Ensure you filter out blocked credits under Section 17(5) before matching with GSTR-3B. Excel formulas to build your own custom reconciliation sheet? GSTR-2A & 2B Reconciliation | Unique Trick in Excel | 27 Oct 2025 —

Since I cannot attach an actual .xlsx file, I have provided the exact column headers, formulas, and layout below. You can copy this into Excel in 2 minutes.