Meta Pixel
LIMITED OFFERSave upto 65% today on SuperBiller Pro EditionExplore Now
Back to All Articles
GST & Compliance 31 October 2026• 8 min read

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.

S
SuperBiller Editorial Team
Verified GST & SaaS Guide
148 views
Financial analyst performing GST reconciliation in Excel spreadsheet
Financial analyst performing GST reconciliation in Excel spreadsheet
Quick Takeaway (BLUF)

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:

  1. Supplier Default Detection: Uncovering vendors who collected 18% GST from you but never filed their GSTR-1;
  2. Invoice Number & Date Typos: Catching typographical mismatches (e.g., your book says INV/2027/045, but vendor entered 045);
  3. Double Entry Prevention: Ensuring an invoice is not mistakenly accounted for twice in your purchase book;
  4. 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:

StatusImmediate Action Required
Exact MatchClaim 100% in Table 4(A)(5) of Form GSTR-3B.
Missing in 2B1. 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:

Statutory References & Official Government Sources

For official guidelines, notification archives, and return filing protocols, consult the authoritative government portals:

Tags:#GST Reconciliation#Excel Template#GSTR-2B Matching#Purchase Register#Tax Audit
Editorial Integrity & Statutory ComplianceCBIC & GST Council Validated

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.

Related Free Indian MSME Tools100% Free
SuperBiller ERP Software

Ready to automate your GST Billing & 80mm POS?

Generate your first tax invoice in under 30 seconds. 100% offline-ready.

Get Started Free