· 14 min read· Updated November 13, 2026

GST Reconciliation Format in Excel: Free Template + Formulas

Free GST reconciliation Excel utility download: a ready .xlsx that matches GSTR-2B with your books, the formulas behind it, and where Excel misses ITC.

Pavan Kumar
Pavan Kumar
Founder at GST Reconcile
TwitterLinkedIn
Free GST reconciliation Excel utility: B2B Results sheet matching Books with GSTR-2B by GSTIN and invoice number, with Matched, Only in Books and Only in 2B statuses

Every month it's the same ritual: export the purchase register, download GSTR-2B, and match the two before the ITC claim goes into GSTR-3B. This page gives you a working GST reconciliation Excel utility you can download and use today, the GST reconciliation format in Excel behind it, the formulas that do the matching — and an honest list of where the Excel method stops and quietly costs you ITC.

Free GST reconciliation Excel utility (download)

GST Reconcile
Free GST Reconciliation Excel Utility: GSTR-2B vs Books (with formulas)
Paste your Books and your GSTR-2B portal download; the Results sheets fill themselves. B2B invoices and credit/debit notes.
No sign-upNo macrosB2B invoices + credit/debit notes
Download free .xlsx ↓GST_Reconciliation_Excel_Utility_Lite.xlsx · Excel workbook · 2.5 MB

No registration wall, unlike some "free" GST utilities, and no macros to enable. Here's what's inside, in the order the tabs appear:

SheetWhat it's for
How to useThe monthly steps, what each status means and the rules, inside the file
Books-B2BYour purchase register (purchase invoices), in the same columns as our Books template
2B-B2BThe portal GSTR-2B file's B2B sheet, pasted from row 7 exactly as downloaded
B2B ResultsFills itself: every invoice as Matched, Only in Books or Only in 2B
Books-CDNRYour credit and debit notes
2B-CDNRThe portal file's B2B-CDNR sheet, pasted as downloaded
CDNR ResultsFills itself, like B2B Results, for notes

How to use the utility every month

Prefer to watch? This 15-minute walkthrough downloads the utility, pastes a month of Books and GSTR-2B, reads the results, and shows on real invoices what Excel can't catch.

01
Paste your purchase invoices into Books-B2B

Click cell A7 and paste. Rows 1–6 are locked headers, so a paste that starts higher is refused. Paste Special → Values keeps your formatting out.

02
Download GSTR-2B and paste it into 2B-B2B

GST portal → Returns → GSTR-2B → Download Excel. In its B2B sheet select row 7 to the last row, copy, click A7 in 2B-B2B and paste. Don't rearrange the columns; anything after column M is ignored.

03
Read B2B Results

It fills itself. Filter the Status column and work through Only in Books first (credit you can't claim yet), then Only in 2B.

04
Repeat for credit and debit notes

Paste your notes into Books-CDNR and the portal file's B2B-CDNR rows into 2B-CDNR, then read CDNR Results.

05
Next month

On the four Books and 2B sheets, select from row 7 down and press Delete. Then paste the new month.

Results not updating after you paste? Your Excel is set to manual calculation. Press F9, or go to Formulas → Calculation Options → Automatic.

The same steps, and the Books column guide, are on the How to use sheet inside the file:

How to use sheet of the GST reconciliation Excel utility showing the 7 monthly steps and the Books sheet column guide

Credit and debit notes get their own Results sheet. Note numbers are matched on GSTIN + note number, and amounts are shown without their sign so credit notes line up:

CDNR Results sheet of the GST reconciliation Excel utility matching credit and debit notes from Books with GSTR-2B B2B-CDNR

What each status means

StatusWhat it meansWhat to do
MatchedThe same GSTIN + invoice number is in both files. Amounts and dates are shown but not checked.Compare the Books and 2B amounts side by side before you claim
Only in BooksNo 2B entry with this GSTIN + invoice number.Check for a typo or FY prefix first; if it's really missing, the supplier hasn't filed, so follow up before claiming
Only in 2BIn your GSTR-2B but not in your Books.Record the invoice, or check its number in your Books
B2B Results sheet in the GST reconciliation Excel utility with Matched, Only in Books and Only in 2B rows and Books vs 2B amounts side by side

Matched in the free utility means "found", not "correct". An invoice with the wrong tax still shows Matched. That's deliberate: the utility answers one question — is it in both files? — and leaves the judgement to you.

The GST reconciliation format in Excel: the columns you actually need

Whether you use the utility or build your own sheet, a working reconciliation needs the same columns on both sides — your Books (purchase register) and GSTR-2B — so a formula can line them up:

ColumnWhy it’s there
Supplier GSTINHalf of the match key, and the anchor for supplier-wise totals
Supplier nameHuman readability; never the match key (names vary)
Invoice numberThe other half of the match key — and the source of most false misses
Invoice datePeriod checks; catches invoices booked in the wrong month
Invoice valueThe bill total including GST
Taxable valueThe base to compare before tax
IGST / CGST / SGST / CessSplit, because a wrong tax head is a real mismatch
StatusMatched / Only in Books / Only in 2B

Match on GSTIN + invoice number, never on supplier name or amount alone. Two vendors can raise the same invoice number; one vendor can raise two invoices for the same amount. GSTIN + invoice number is the only combination that's actually unique.

Your Books side should follow the Books template — the utility's Books sheets use exactly those columns. The GSTR-2B side is the portal download as-is.

How the formulas work

Here is the same logic the utility uses, so you can follow what it does or build it in your own sheet. The examples use the utility's sheet names and columns: Books-B2B (your purchase register, GSTIN in A, invoice number in C, taxes in H–K) and 2B-B2B (the portal layout, GSTIN in A, invoice number in C, taxes in J–M). Add two helper columns on both sheets, from row 7 down: a match key in X and the total tax in Y.

1. Build the match key and the total tax

X7: =UPPER(TRIM(A7))&"|"&UPPER(TRIM(C7)) Y7 on 2B-B2B: =SUM(J7:M7) Y7 on Books-B2B: =SUM(H7:K7)

The key ignores extra spaces and small/capital letters — the two most common reasons a perfectly valid credit shows as missing. The "|" keeps GSTIN and invoice number from running into each other. Y adds IGST, CGST, SGST and Cess, so no tax head is missed.

2. Is this Books invoice in 2B? (COUNTIF)

=IF(COUNTIF('2B-B2B'!$X$7:$X$2006, X7)>0, "Matched", "Only in Books")

Run the same check from 2B-B2B against the Books-B2B keys to find Only in 2B.

3. Pull the 2B tax next to your Books tax (INDEX + MATCH, or VLOOKUP)

=IFERROR(INDEX('2B-B2B'!$Y$7:$Y$2006, MATCH(X7, '2B-B2B'!$X$7:$X$2006, 0)), "")

Prefer VLOOKUP? It does the same job here, because the key (X) sits just left of the total tax (Y):

=IFERROR(VLOOKUP(X7, '2B-B2B'!$X$7:$Y$2006, 2, FALSE), "")

4. Catch duplicate invoices before they inflate ITC (COUNTIFS)

=COUNTIFS('Books-B2B'!$A$7:$A$2006, A7, 'Books-B2B'!$C$7:$C$2006, C7)>1

5. Supplier-wise ITC totals (SUMIFS)

=SUMIFS('2B-B2B'!$Y$7:$Y$2006, '2B-B2B'!$A$7:$A$2006, A7)

GSTR-2B vs Books (Purchase Register) reconciliation in Excel, step by step

Whether you use the utility or your own sheet, a GSTR-2B vs Books reconciliation in Excel comes down to the same eight steps every month. The utility does steps 3 to 7 for you.

01
Download GSTR-2B

Download the Excel from the GST portal for the period. It's fixed once generated around the 14th, so it won't change while you work.

02
Get your purchase register into the Books columns

Export from Tally, Busy or your accounting software and arrange it into the Books template columns, keeping invoices and credit/debit notes separate.

03
Tidy the invoice numbers and GSTINs

Extra spaces and small/capital letters make the same invoice look different. The utility ignores both for you.

04
Pair each invoice by GSTIN + invoice number

The same GSTIN and invoice number on both sides means it's the same invoice. The utility does this pairing for you.

05
List what's only in your Books

These are invoices your supplier hasn't reported yet. The credit isn't in your 2B, so follow up before claiming it.

06
List what's only in 2B

These are invoices you haven't recorded. Book them, or check the invoice number in your Books.

07
Do credit and debit notes separately

Keep them out of the invoice list. Credit notes reduce your ITC, so they need their own check.

08
Check the amounts on the matched invoices

Compare each tax head and the date, then claim only what's reflected in 2B, per Section 16(2)(aa).

Where Excel quietly fails (and silently costs you ITC)

Excel lookups are exact-match. Real invoice data almost never matches exactly. That gap is where credits go missing — not because the ITC is gone, but because the formula couldn't see it.

Every one of these shows up as a false "Only in Books", and every false miss is a credit you might not claim:

  • Leading zeros vanish. Books shows 0042, Excel silently converts it to the number 42, GSTR-2B carries the text 0042. The credit is fine; the format killed the match.
  • FY prefixes don't line up. 2B has GST/202526/0042, your team typed 42. Exact lookup misses it entirely.
  • O-for-0 and I-for-1 typos. INVO123 vs INV0123 — one character, and the invoice reads as missing.
  • The supplier's other GSTIN. The invoice is in 2B, but under the supplier's GSTIN for another state. Excel shows it twice — once Only in Books, once Only in 2B.
  • Duplicates hide in plain sight. Enter the same invoice twice and a lookup happily matches both copies to one 2B line — the duplicate inflates ITC unless you added the COUNTIFS check.
  • Excel tells you "unmatched," never "why." You still open every miss by hand to decide: typo, vendor hasn't filed, or genuinely at risk. On 30 invoices that's an afternoon. On 3,000 across a dozen clients it's the whole week.

Your lookup returns #N/A? Check these first

  • The invoice number is a number on one side and text on the other (42 vs "42").
  • A trailing space in the GSTIN or invoice number — TRIM both sides.
  • The FY prefix or leading zeros differ between Books and 2B.
  • The lookup range isn't locked with $ signs, so it shifted when you copied the formula down.
  • VLOOKUP only: the key isn't the first column of the lookup range, or the last argument isn't FALSE.
  • The invoice really is missing from 2B — the supplier hasn't filed GSTR-1 yet.

What the free utility can't do — and GST Reconcile can

The utility answers one question well: is this invoice in both files? Everything after that is what the GSTR-2B reconciliation tool is for:

The free utilityGST Reconcile
Doesn't check amounts — a Matched row can still carry the wrong taxEvery tax head compared, with the difference in ₹
Doesn't check datesInvoice dates compared; small gaps and wrong-month bookings flagged
Exact invoice numbers onlyTypos, FY prefixes and leading zeros cleaned before matching
Misses the supplier's other GSTINPaired and flagged as Cross-PAN
Misses duplicatesDuplicates in Books and in 2B flagged separately
No supplier viewGSTIN Summary with ITC at risk per supplier, plus an ITC Summary for GSTR-3B

The utility says the same thing about itself, on its How to use sheet:

How to use sheet of the GST reconciliation Excel utility showing what each status means, the rules, and what the free utility can't do compared with GST Reconcile

Doing the year-end check instead? Use the free GSTR-9 reconciliation sheet (GSTR-1 vs 3B vs 2B vs Books).

Where GST Reconcile picks up

That's as far as the free utility goes. Here's what gstreconcile.in does with the same two files.

It takes the same Books columns you've already filled in and classifies every row through a 21-rule engine, with the reason beside each one. The ITC at risk per supplier comes out in rupees, sorted by exposure, so you know which vendor to chase first. To see it run on a full month, watch the step-by-step GSTR-2B reconciliation demo video.

GSTIN Summary in GST Reconcile showing ITC at risk per supplier in rupees, sorted by largest exposure

And when a mismatch is real, knowing whether the credit is recoverable or gone is its own decision — our guide on which ITC mismatches are recoverable picks up where the sheet leaves off. For the full monthly workflow end to end, see the GSTR-2B reconciliation guide.

GST Reconcile
Done with exact matches? Run the full reconciliation
Same Books columns, 21 rules, every row explained. Try it free with the sample files.
Try Free →

Frequently Asked Questions

Can I download a free GST reconciliation Excel utility?

Yes. Download the free GST Reconciliation Excel Utility (Lite) from this page. No sign-up, no macros, no licence key. It has sheets for your Books and your GSTR-2B (B2B invoices and credit/debit notes) and two Results sheets that fill themselves the moment you paste.

How do I make a GST reconciliation in Excel?

Put your purchase register (Books) and your GSTR-2B in two sheets, and pair each invoice by GSTIN + invoice number, for example with COUNTIF or VLOOKUP on a combined key. Found on both sides is Matched, found only in Books is Only in Books, and found only in 2B is Only in 2B. Never match on invoice number alone: two suppliers can use the same number. Or download the free utility above, which does this for you.

How do I do GSTR2B reconciliation in Excel?

Download GSTR-2B in Excel from the GST portal (Returns → GSTR-2B → Download Excel). In the free utility, paste your purchase invoices into Books-B2B from row 7, paste the portal file's B2B sheet rows into 2B-B2B from row 7 exactly as downloaded, and read B2B Results. Repeat with Books-CDNR and 2B-CDNR for credit and debit notes.

How do I match GSTR-2B with my purchase register in Excel?

Export your purchase register from your accounting software and arrange it in the Books template columns (GSTIN, party name, invoice number, date, values and taxes). Paste it into Books-B2B in the free utility from row 7, paste the portal GSTR-2B file's B2B sheet into 2B-B2B from row 7, and read B2B Results: every invoice shows as Matched, Only in Books or Only in 2B.

What columns should a GST reconciliation format in Excel have?

Ten core columns on both the Books and GSTR-2B sides: Supplier GSTIN, Supplier name, Invoice number, Invoice date, Invoice value, Taxable value, IGST, CGST, SGST and Cess, plus a Status column. GSTIN + invoice number together are the match key.

Why does an invoice show Only in Books when the supplier has filed?

Usually because the invoice number is written differently on the two sides: a dropped leading zero (0042 vs 42), an FY prefix (GST/2025-26/42 vs 42) or an O-for-0 typo. Exact matching in Excel can't see past that. GST Reconcile cleans invoice numbers before it matches, so those rows pair up.

Does Matched mean my ITC is safe to claim?

Not on its own. In the free utility, Matched only means the same GSTIN + invoice number is in both files. Amounts and dates are shown side by side but not checked, so compare them before you claim. GST Reconcile checks every tax head and the date for you.

Can I upload this Excel utility to GST Reconcile?

No, the utility is for working in Excel. To run the full reconciliation, copy your rows from Books-B2B and Books-CDNR into the Books template (same columns) and upload it with your unmodified GSTR-2B portal file.

Free Tool

Reconcile Your GSTR-2B Free

No VLOOKUP gymnastics. Every mismatch comes with its reason.
Works with Tally, Busy & ClearTax. Up to 99.99% accurate.

Start Reconciling Free →

Free plan, no card needed · ₹450+ Cr ITC Saved · 12.5M+ Invoices Matched

Pavan Kumar
Pavan Kumar
Founder at GST Reconcile

Founder @ GST Reconcile. Building India's fastest GSTR-2B reconciliation tool for CAs. Turning 8 hours of Excel into 8 seconds.

GST Reconciliation Format in Excel: Free Template + Formulas