GST Reconciliation Format in Excel: Free Download & Matching Guide
Reconciling your purchase book against GSTR-2B manually? Download our free GST reconciliation Excel template and learn the exact formula matching workflow.
Download free GST reconciliation Excel template format. Learn step-by-step how to match purchase registers with GSTR-2B using VLOOKUP and avoid tax notices.
For every Indian accountant, tax practitioner, and business owner, the week leading up to the 20th of the month is dominated by a single critical task: GST Purchase Reconciliation.
Under Section 16 of the Central Goods and Services Tax Act, you are strictly prohibited from claiming Input Tax Credit (ITC) on purchase bills unless those exact invoices are uploaded by your suppliers and reflected in your government Form GSTR-2B.
If your purchase register says you have ₹1,50,000 in tax credit, but GSTR-2B only shows ₹1,20,000, claiming the higher amount in your GSTR-3B will instantly trigger an automated DRC-01C scrutiny notice from the tax department. Conversely, failing to claim legitimate available credit means overpaying cash taxes and strangling your business cash flow.
In this comprehensive guide, we provide a structured, battle-tested GST Reconciliation Excel Template Format, explain the formulas to automate invoice matching, and demonstrate how to recover money from non-compliant suppliers.
⚡ Quick Answer (AEO / BLUF): GST reconciliation matches purchase invoices recorded in your accounting ledger against invoices uploaded by suppliers in Form GSTR-2B. Discrepancies (missing bills, GSTIN errors, rate mismatches) block your Input Tax Credit. Download our standardized Excel reconciliation format using VLOOKUP and formula validation from SuperBiller Free GST Templates.
Why Purchase Reconciliation is Mandatory Every Single Month
Before the introduction of static GSTR-2B statements, businesses claimed provisional tax credits based on internal invoices. Today, the reconciliation exercise solves four dangerous operational risks:
- Supplier Default Detection: Uncovering vendors who collected 18% GST from you but never filed their GSTR-1;
- Invoice Number & Date Typos: Catching typographical mismatches (e.g., your book says
INV/2027/045, but vendor entered045); - Double Entry Prevention: Ensuring an invoice is not mistakenly accounted for twice in your purchase book;
- Rule 37A Compliance: Identifying suppliers who filed GSTR-1 but failed to deposit tax in GSTR-3B, protecting you from mandatory ITC reversals with interest.
Recommended Structure for GST Reconciliation Excel Template
A professional GST reconciliation workbook should contain three primary tabs:
Tab 1: "Purchase_Register" (Data from Your Accounting/Billing Software)
Export this directly from your software. Standard columns include:
- Column A: Supplier GSTIN
- Column B: Supplier Trade / Legal Name
- Column C: Invoice Number
- Column D: Invoice Date (
DD-MM-YYYY) - Column E: Invoice Total Value
- Column F: Taxable Value
- Column G: Integrated Tax (IGST)
- Column H: Central Tax (CGST)
- Column I: State Tax (SGST)
- Column J: Unique Composite Key
[GSTIN & Invoice Number]
Tab 2: "GSTR_2B_Data" (Downloaded from Portal)
Download the monthly GSTR-2B Excel from gst.gov.in > Return Dashboard > GSTR-2B > Download Excel. Paste the B2B sheet data into this tab:
- Columns: Supplier GSTIN, Legal Name, Invoice No, Invoice Date, Invoice Value, Taxable Value, IGST, CGST, SGST, ITC Availability.
- Create the same Composite Key in Column A:
=B2&"_"&D2(combining GSTIN and Invoice Number).
Tab 3: "Reconciliation_Summary" (Automated Matching Dashboard)
This sheet compares Tab 1 and Tab 2 using formulas, classifying every invoice into four categories:
- Exact Match: Reflected in both books with identical tax values (100% Eligible to Claim);
- Value Mismatch: Invoice number matches, but tax values differ due to rounding off or rate errors;
- Missing in GSTR-2B: In your purchase book, but supplier has not uploaded (Do NOT Claim; hold vendor payment);
- Missing in Purchase Book: In GSTR-2B, but not entered in your software (Potential unclaimed credit).
The Master Formula: How to Match Invoices in Excel
The biggest hurdle in Excel reconciliation is that different accountants write invoice numbers differently (e.g., INV-102 vs INV/102 vs 102). Here is the step-by-step formula method:
1. Build a Composite Search Key
In both Tab 1 and Tab 2, create a clean lookup key combining GSTIN and Invoice Number:
=TRIM(LOWER(A2)) & "_" & SUBSTITUTE(SUBSTITUTE(TRIM(LOWER(C2)), "/", ""), "-", "")
Why this works: Stripping slashes, hyphens, spaces, and capital letters ensures that 27AAAAA0000A1Z5_INV-045 matches 27AAAAA0000A1Z5_INV/045 seamlessly!
2. Check Match Status Using VLOOKUP / XLOOKUP
In your Purchase Register sheet, add a column named 2B_Status:
=IF(ISNA(VLOOKUP(J2, GSTR_2B_Data!A:G, 6, FALSE)), "MISSING IN 2B", "FOUND IN 2B")
(If using Microsoft Excel 365 or Google Sheets, use XLOOKUP for faster recalculation speed across 10,000+ rows).
3. Compare Tax Differences
In the adjacent column, calculate the tax variance:
=ROUND((IGST_Book + CGST_Book + SGST_Book) - VLOOKUP(J2, GSTR_2B_Data!A:I, 7, FALSE), 2)
- If Variance =
0: Exact Match - If Variance >
0(or <0): Mismatch to be investigated
What Actions to Take on Discrepancies
Once your Excel reconciliation highlights differences, follow this action protocol:
| Status | Immediate Action Required |
|---|---|
| Exact Match | Claim 100% in Table 4(A)(5) of Form GSTR-3B. |
| Missing in 2B | 1. Exclude from current GSTR-3B claim.<br>2. Send automated WhatsApp / Email notice to vendor.<br>3. Deduct or hold GST payment until vendor files in next GSTR-1. |
| Value Mismatch (< ₹5) | Ignore small rounding fractions; claim the amount specified in GSTR-2B. |
| Value Mismatch (> ₹50) | Contact vendor to issue Credit Note or Debit Note for rate adjustment. |
Stop Manual Excel Hassles: Switch to SuperBiller
Exporting CSVs, cleaning messy formulas, and spending 10 hours every month fixing #N/A errors in Excel is an outdated workflow that wastes valuable business time.
With SuperBiller:
- Import your supplier purchase bills with automated OCR and barcode cataloging;
- Upload your downloaded GSTR-2B JSON file in one click;
- SuperBiller runs intelligent fuzzy matching automatically, showing you an instant color-coded breakdown of Matched, Mismatched, and Missing bills;
- Click Send WhatsApp Follow-up to automatically dispatch payment reminders to every defaulting supplier!
Frequently Asked Questions (FAQ)
Where can I download the official GSTR-2B Excel file from the portal?
Log in to gst.gov.in > Return Dashboard, select your filing month, locate the Form GSTR-2B tile, and click Download. Choose Download Excel to obtain the official raw data sheet.
What is the maximum difference allowed between purchase register and GSTR-2B?
Zero difference for unmatched invoices! For rounding off on taxable value or tax amounts, minor rounding differences of up to ₹1 per invoice are accepted by the system. However, any missing invoice cannot be claimed under Section 16(2)(aa).
Can I claim an invoice in GSTR-3B if the supplier filed after the 11th of the month?
No. Invoices filed by suppliers between the 12th and the end of the month will not appear in the current month's GSTR-2B. They will roll forward into next month's GSTR-2B, and you must claim them in that subsequent return.
What is the deadline to reconcile and claim pending ITC for a financial year?
Under Section 16(4) of the CGST Act, you can reconcile, claim missing ITC, or report amendments up to the 30th of November following the end of the financial year, or the date of filing the Annual Return (GSTR-9), whichever is earlier.
How does Rule 37A affect my purchase reconciliation?
Rule 37A states that if you claim ITC on an invoice in GSTR-3B because the supplier filed GSTR-1, but that supplier subsequently fails to file their GSTR-3B by 30th September of the following financial year, you must reverse that claimed ITC along with interest. Regular reconciliation prevents this risk.
Is Excel reconciliation safe for companies with thousands of invoices?
Excel works well up to a few hundred invoices per month. However, for retail stores, wholesalers, and distributors handling over 500 invoices monthly, Excel formulas become slow, crash, and fail to catch subtle character errors. Using dedicated billing software like SuperBiller provides automated, audit-proof matching.
Related In-Depth Guides & Recommended Reading
Expand your tax compliance and store management knowledge with these related guides from our topic cluster:
- How to Check Input Tax Credit (ITC) in GST Portal & GSTR-2B – Essential compliance rules, practical calculations, and step-by-step guidance.
- How to Track GST Challan Status Online & Resolve PMT-07 Grievances – Essential compliance rules, practical calculations, and step-by-step guidance.
- How to Make GST Challan Payment Online: CPIN & PMT-06 Guide – Essential compliance rules, practical calculations, and step-by-step guidance.
- How Much Does GST Filing Cost in India? CA Fees vs Software – Essential compliance rules, practical calculations, and step-by-step guidance.
Statutory References & Official Government Sources
For official guidelines, notification archives, and return filing protocols, consult the authoritative government portals:
- Central Board of Indirect Taxes and Customs (CBIC):
- CBIC GST Central Portal — Statutory acts, rules, and rate notifications
- CBIC Tax Information Portal — Circulars, clarifications, and FAQs
- GST Common Portal (GSTN):
- GST Portal Self-Service System — Return filing, ledger management, and taxpayer services
- E-Way Bill & E-Invoice Infrastructure:
- National E-Way Bill Portal — Inter-state & intra-state consignment tracking
- GST E-Invoice Portal (NIC) — B2B e-invoice generation and IRN verification
- GST Council of India:
- GST Council Secretariat — Council meeting decisions and rate rationalizations
This guide is authored by the SuperBiller Tax & Retail Operations Desk and fact-checked against official Central Board of Indirect Taxes and Customs (CBIC) circulars, the CGST Act 2017 (Rule 46 & Section 31), and latest GST Council threshold updates. SuperBiller provides MSMEs with compliant billing technology, 80mm thermal printing, and automated tax workflows.
Ready to automate your GST Billing & 80mm POS?
Generate your first tax invoice in under 30 seconds. 100% offline-ready.