GSTR-2B reconciliation in Excel: a free template and how to use it

Most accountants start the month with two exports and a blank workbook. This template is that workbook, already wired: the match key, the lookup, the ₹1 tax tolerance and the totals are done, so the time goes into reading exceptions. What follows describes the file as built, including what it leaves out, and how you can build your own using XLOOKUP or INDEX MATCH.

The standard Excel layout: required columns

To run an accurate comparison, both your purchase register export and your GSTR-2B data must follow an identical column structure. When you download GSTR-2B in Excel format from the GST portal, the file contains separate tabs for B2B, B2BA, CDNR, and CDNRA tables. For regular inward supplies, copy the rows from the B2B tab into your working sheet.

Whether using our template or building your own, set up two input sheets named Purchase register and GSTR-2B. Both sheets should use the following nine columns from column A to column I:

In column J of both sheets, calculate the total tax using the formula =SUM(F2:I2). Matching total tax rather than individual tax heads prevents false mismatches when an intra-state supply is mistakenly entered across CGST and SGST columns in reverse order.

What is inside the free workbook

How to use it, step by step

1. Clear the samples. On Purchase register select A2:I9 and press Delete. On GSTR-2B select A2:I8 and press Delete. Clear contents only. Do not delete rows, columns or sheets, because the other sheets point at them.
2. Paste the register into Purchase register from row 2, as values (Paste Special, then Values).
3. Paste the GSTR-2B B2B rows into GSTR-2B from row 2, as values, in the same nine-column order. Blank tax cells are fine.
4. Merge split invoices. If one invoice sits on several rows, for example one per tax rate, add them into a single row first. The template reads each GSTIN and invoice number once and treats a second row as a duplicate.
5. Filter the Status column on Reconciliation to Only in books, then Value mismatch. Then open 2B check and filter to Only in 2B. Filter, do not sort: sorting the formula sheets scrambles the lookups. If Excel opens the file in Protected View, click Enable Editing so the formulas calculate.
6. Read Summary. The Checks block at the bottom must say OK twice. If it says CHECK, a row was lost or typed over.

Building an exact match key

Looking up records by invoice number alone will cause errors. Multiple vendors frequently issue invoices sharing common numbering series like INV-01 or 001. You must create a unique composite key combining the supplier GSTIN and the invoice number.

Under Rule 46(b) of the CGST Rules, an invoice number is a consecutive serial number of up to 16 characters containing alphabets, numerals, and special characters such as hyphens and slashes. Because suppliers and accounting software enter punctuation differently, one system might record INV/2026/101 while another records INV-2026-101 or INV 2026 101.

In our template, we build a key from the GSTIN and a cleaned invoice number: capital letters, the characters space / - . , _ \ ( ) # * ? ~ removed, and leading zeros dropped. So INV/2026/101 and inv-2026-101 become the same key, and so do 000542 and 542.

Zeros inside a number stay: CP-0779 becomes CP0779, which will not match CP-779. Only zeros at the very start go.

Formula matching: XLOOKUP versus INDEX MATCH

Once both sheets have match keys and total tax figures, you can compare them on a reconciliation sheet. Our free template uses INDEX and MATCH for universal compatibility across Excel 2016 and Google Sheets.

Method 1: INDEX and MATCH (used in the template)

For broad compatibility, use INDEX combined with MATCH. In the template, MATCH finds where the key sits and INDEX reads the tax from that position:

=IFERROR(INDEX('GSTR-2B'!$J$2:$J$500, MATCH(K2, 'GSTR-2B'!$K$2:$K$500, 0)), "Missing")

Method 2: XLOOKUP (for Microsoft 365 and Excel 2021)

If you are building your own sheet and use Microsoft 365, XLOOKUP is the cleanest formula because it does not require the match key to be in the first column and provides clean fallback values for missing records.

=XLOOKUP(K2, 'GSTR-2B'!$K$2:$K$500, 'GSTR-2B'!$J$2:$J$500, "Missing", 0)

Calculating tax differences with round-off tolerance

Under Section 170 of the CGST Act, tax amounts are rounded off to the nearest rupee. A difference of up to ₹1.00 is a standard rounding variance, not a tax mismatch. In our template, the status test ABS(H2)>1 means a difference of exactly ₹1.00 is still Matched, while ₹1.01 is a Value mismatch.

Worked example from the sample rows

The sample data is built to hit every status. These are the results the formulas give for the eight sample purchase rows. Amounts are tax (IGST + CGST + SGST), in rupees.

Two-way matching: why checking 2B against books is mandatory

Matching only from your purchase register to GSTR-2B is a one-way audit that leaves a significant blind spot. You must also perform a reverse check: looking up GSTR-2B records against your purchase register to detect entries that exist in GSTR-2B but are missing in your books.

Our template handles this via the 2B check sheet, applying the lookup logic in reverse. Common reasons for records appearing only in GSTR-2B include:

1. Goods in transit: The supplier issued an invoice on the 29th of the month and filed GSTR-1, but the goods arrived at your warehouse in the following month. Under Section 16(2)(b) of the CGST Act, you cannot claim ITC until you have actually received the goods. Defer this credit to next month.
2. Incorrect GSTIN used by vendor: An unknown supplier entered your GSTIN by mistake on an invoice intended for another customer. You are not entitled to this ITC and must not claim it.
3. Invoice Management System (IMS) status: Under GSTN advisories active from October 2024, taxpayers can Accept, Reject, or mark inward invoices as Pending in IMS. Invoices marked Pending do not flow into that period's GSTR-2B. If an invoice you expected is missing, check your IMS dashboard before assuming the vendor failed to file.

What the Excel template does not do

• Only B2B rows are compared. The portal's GSTR-2B Excel has B2B, B2BA, CDNR and CDNRA tables. Amendments and credit or debit notes are not read.
• No eligibility check. The template does not read the ITC availability or reverse charge flags of the GSTR-2B file. A Matched invoice can still be one on which ITC is not available.
• Exact match after clean-up, nothing smarter. INV-101 against INV-0101A is not guessed. Our post on invoice number mismatches explains the patterns you will pair by hand.
• No view of IMS. A record kept Pending in the Invoice Management System is not part of GSTR-2B, so it shows as Only in books. Check the IMS Pending guide before you chase the supplier.
• 500 invoices a side, one GSTIN at a time. Rows 2 to 501. Going beyond that means copying the formulas down on three sheets. Keep one copy of the workbook per GSTIN of yours.

Where Excel formulas fail and automation takes over

While an Excel template is practical for businesses with 50 to 200 monthly invoices, standard spreadsheet formulas run into hard technical boundaries as transaction volumes grow:

• Multi-rate invoices: If an invoice contains line items taxed at 12% and 18%, GSTR-2B lists them on separate rows. Standard XLOOKUP and VLOOKUP functions only match the first row, generating false value mismatches unless you pre-aggregate the data with Pivot Tables.
• Credit notes and amendments: Netting credit notes against original invoices across four sheets in Excel requires complex multi-sheet queries.
• Complex punctuation differences: If a vendor writes 0098/26-27 and your accountant writes 98/2026-27, simple formula replacements fail, requiring manual inspection.

Excel or the browser tool

For a few dozen invoices with tidy numbering, this template is enough. As volume grows the cost moves to cleaning exports and rereading exceptions. Our comparison of a reconciliation tool against manual matching works through a 300-invoice month, and reconciling GSTR-2B in Excel without Tally covers the spreadsheet route from scratch.

The browser tool cleans invoice numbers the same way (capitals, punctuation removed, leading zeros dropped, though it removes every character that is not a letter or number) and uses the same ₹1 tolerance, and it adds what a spreadsheet makes tedious. It reads the portal's GSTR-2B Excel or JSON directly, asks you to confirm which of your columns is which instead of making you rearrange them, and splits Only in books into two buckets by whether the supplier appears anywhere in GSTR-2B. Credit and debit notes are listed for information only; it does not do full two-way matching on them either. It does not file returns or read your live IMS dashboard.

Related guides

• What kinds of GSTR-2B reconciliation software exist, and which fits me
• How to use a GSTR-2B reconciliation tool
• How to Download GSTR-2B in Excel Format: step-by-Step Portal Guide & Tab Structure
• GSTR-2B JSON to Excel conversion guide
• How accountants can automate GSTR-2B reconciliation

Frequently asked questions

Last checked: 10 October 2026. Sources: CGST Act, Section 16, Notification 40/2021-Central Tax (Rule 36(4)), CGST Rules, Rule 46, GSTN FAQs on comparison of liability declared and ITC claimed, GSTN FAQs on the Invoice Management System, GSTN user guide on viewing e-filed returns, GSTN GSTR-3B user guide.