Gstr 2a And 3b Reconciliation In Excel Format Download ((free)) 🆓
Review: GSTR 2A and 3B Reconciliation Excel Downloads
Verdict: ⭐⭐⭐⭐ (Essential for compliance, but manual maintenance is high)
For GST compliance in India, the reconciliation of GSTR-2A (auto-drafted purchases) and GSTR-3B (summary returns) is a critical monthly activity. While the government provides reconciliation tools on the GST portal, they are often slow and cumbersome. Consequently, finance professionals and business owners heavily prefer downloading Excel-based utilities to perform this reconciliation offline.
Here is a detailed breakdown of what to expect from these Excel downloads and how to choose the right one.
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.
📌 Step 1: Download Your Data
- GSTR-2A (JSON → Excel): Go to GST Portal → Returns → GSTR-2A → Download JSON → Use a free online converter (e.g., ClearTax 2A to Excel converter or MasterGST) to convert JSON to Excel.
- GSTR-3B Data: Download your filed GSTR-3B from the portal (Excel format available in the returns dashboard).
Step 3: Use Excel Functions for Reconciliation
A. Find missing invoices in GSTR-2A (vendor not filed) gstr 2a and 3b reconciliation in excel format download
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. Review: GSTR 2A and 3B Reconciliation Excel Downloads
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. Key formulas: VLOOKUP/XLOOKUP/INDEX-MATCH for direct mapping
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 | | | |
How to Perform the Reconciliation (Formula-Based)
Appendix C — User Guide (3 steps)
- Paste/download GSTR‑2A CSV into GSTR2A_Raw; refresh Power Query.
- Paste GSTR‑3B monthly totals and/or purchase ledger into GSTR3B_Raw.
- Click "Run Reconciliation" macro or refresh match sheet; review Reconciliation_Report and Exceptions, escalate unresolved items.
🟢 Auto-calc Total ITC in 2A (eligible only)
=SUMIF(GSTR2A_RAW!H:H,"Yes",GSTR2A_RAW!E:E)
(Assumes Col H in 2A sheet says "Yes" if ITC eligible)
Sheet 2: GSTR-3B (Claimed ITC – Table 4)
| GSTIN of Supplier | Invoice Number | Invoice Date | Taxable Value | CGST Claimed | SGST Claimed | IGST Claimed | Cess Claimed | Total ITC Claimed | |------------------|----------------|--------------|----------------|--------------|--------------|--------------|--------------|--------------------|