GST Reconciliation Format in Excel: Free Template + Formulas

The 5 Excel formulas that do the matching

Here's the whole matching engine in five formulas. Assume Books is your working sheet and 2B[...] is the GSTR-2B table.

1. Pull the 2B value against each Books invoice (XLOOKUP)

2. Flag a real value mismatch — with a ₹1 tolerance (ABS + IF)

The >1 matters: the portal rounds tax to the rupee, your books may carry paise. Without the tolerance, Excel flags identical invoices as mismatches all day.

3. Supplier-wise ITC totals (SUMIFS)

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

5. Clean the invoice number before you match (TRIM + UPPER)

This removes trailing spaces and case differences — the two most common reasons a perfectly valid credit shows as "missing."

How to reconcile GSTR-2B with your books in Excel, step by step

That's a clean reconciliation for a few dozen invoices. Now the part the template pages skip.

Where Excel quietly fails (and silently costs you ITC)

Here's what breaks a spreadsheet reconciliation at real volume — every one of these shows up as a false "Missing in 2B," 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 — XLOOKUP returns #N/A. 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.
• ₹1 rounding. Covered by the ABS tolerance above — but the default exact-match templates don't include it, so they over-report mismatches.
• CDNR mixed into B2B. Credit and debit notes carry negative tax and reduce your ITC. XLOOKUP them in the same pass as B2B invoices and you either get no match or the wrong sign — quietly overstating your claim. CDNR must be a separate pass.
• Duplicates hide in plain sight. Enter the same invoice twice in Books and XLOOKUP returns the first match only — the duplicate silently inflates ITC and never gets flagged unless you added the COUNTIFS check (most templates don't).
• Excel tells you "unmatched," never "why." You still open every mismatch 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.

None of these are edge cases you can ignore — they're the normal state of Indian invoice data. A reconciliation that assumes clean, exact-matching invoice numbers isn't reconciling; it's generating a mismatch list that's mostly noise.

When to stop fighting the spreadsheet

Excel is fine for a handful of invoices. The moment you're running a full month — or multiple clients — the failures above stop being annoyances and start being ITC leakage.

That's the gap GST Reconcile was built for. It runs the same match as your XLOOKUP, but through a 21-rule classification engine that normalises the invoice number before comparing — the typo map, FY-prefix stripping, leading-zero and date-string handling — so the false "missing" rows disappear and what's left is genuinely at risk. It reconciles CDNR separately from B2B, flags duplicates in both Books and 2B, and gives you a GSTIN Summary showing ITC at risk per supplier in rupees — the "why," not just the "unmatched." It's rules-based and deterministic (same inputs, same result every run), it runs in your browser so client files never leave your machine, and it takes the same Books template you'd have built in Excel anyway.

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.

Start with the Excel format above — it's the right way to learn what reconciliation actually checks. Just don't mistake a clean-looking spreadsheet for a clean ITC claim.

Frequently Asked Questions