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.

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)
No registration wall, unlike some "free" GST utilities, and no macros to enable. Here's what's inside, in the order the tabs appear:
| Sheet | What it's for |
|---|---|
| How to use | The monthly steps, what each status means and the rules, inside the file |
| Books-B2B | Your purchase register (purchase invoices), in the same columns as our Books template |
| 2B-B2B | The portal GSTR-2B file's B2B sheet, pasted from row 7 exactly as downloaded |
| B2B Results | Fills itself: every invoice as Matched, Only in Books or Only in 2B |
| Books-CDNR | Your credit and debit notes |
| 2B-CDNR | The portal file's B2B-CDNR sheet, pasted as downloaded |
| CDNR Results | Fills 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.
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.
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.
It fills itself. Filter the Status column and work through Only in Books first (credit you can't claim yet), then Only in 2B.
Paste your notes into Books-CDNR and the portal file's B2B-CDNR rows into 2B-CDNR, then read CDNR Results.
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:

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:

What each status means
| Status | What it means | What to do |
|---|---|---|
| Matched | The 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 Books | No 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 2B | In your GSTR-2B but not in your Books. | Record the invoice, or check its number in your Books |

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:
| Column | Why it’s there |
|---|---|
| Supplier GSTIN | Half of the match key, and the anchor for supplier-wise totals |
| Supplier name | Human readability; never the match key (names vary) |
| Invoice number | The other half of the match key — and the source of most false misses |
| Invoice date | Period checks; catches invoices booked in the wrong month |
| Invoice value | The bill total including GST |
| Taxable value | The base to compare before tax |
| IGST / CGST / SGST / Cess | Split, because a wrong tax head is a real mismatch |
| Status | Matched / 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
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)
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)
Prefer VLOOKUP? It does the same job here, because the key (X) sits just left of the total tax (Y):
4. Catch duplicate invoices before they inflate ITC (COUNTIFS)
5. Supplier-wise ITC totals (SUMIFS)
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.
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.
Export from Tally, Busy or your accounting software and arrange it into the Books template columns, keeping invoices and credit/debit notes separate.
Extra spaces and small/capital letters make the same invoice look different. The utility ignores both for you.
The same GSTIN and invoice number on both sides means it's the same invoice. The utility does this pairing for you.
These are invoices your supplier hasn't reported yet. The credit isn't in your 2B, so follow up before claiming it.
These are invoices you haven't recorded. Book them, or check the invoice number in your Books.
Keep them out of the invoice list. Credit notes reduce your ITC, so they need their own check.
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 number42, GSTR-2B carries the text0042. The credit is fine; the format killed the match. - FY prefixes don't line up. 2B has
GST/202526/0042, your team typed42. Exact lookup misses it entirely. - O-for-0 and I-for-1 typos.
INVO123vsINV0123— 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 utility | GST Reconcile |
|---|---|
| Doesn't check amounts — a Matched row can still carry the wrong tax | Every tax head compared, with the difference in ₹ |
| Doesn't check dates | Invoice dates compared; small gaps and wrong-month bookings flagged |
| Exact invoice numbers only | Typos, FY prefixes and leading zeros cleaned before matching |
| Misses the supplier's other GSTIN | Paired and flagged as Cross-PAN |
| Misses duplicates | Duplicates in Books and in 2B flagged separately |
| No supplier view | GSTIN 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:

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.

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.
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.
Reconcile Your GSTR-2B Free
No VLOOKUP gymnastics. Every mismatch comes with its reason.
Works with Tally, Busy & ClearTax. Up to 99.99% accurate.
Free plan, no card needed · ₹450+ Cr ITC Saved · 12.5M+ Invoices Matched