GSTR-2B reconciliation in Excel: the manual method, and where it quietly breaks

Almost every Indian business reconciles GSTR-2B in Excel before it reconciles it any other way. The method is intuitive: download the GSTR-2B statement the GST portal generates each month, put it next to your purchase register, and check that every invoice you have booked input tax credit against actually appears in 2B — because under Rule 36(4) and Section 16(2)(aa), credit that is not in your 2B is not credit you can claim. For a business with a few dozen suppliers a month, a clean Excel workflow genuinely works. This post walks through that manual method honestly — the exact steps a careful accountant follows — and then the four places it quietly breaks as the business grows, because knowing where the spreadsheet fails is the difference between a clean filing and a notice six months later.

The manual method has five steps. Download the GSTR-2B JSON from the portal (Returns Dashboard, open the period, Download, Generate JSON) and flatten it into a table, or use the portal's Excel export. Export your purchase register from Tally, Zoho, or whichever ERP you run, carrying at least supplier GSTIN, invoice number, invoice date, taxable value and tax. Build a join key on each side — most people concatenate GSTIN and invoice number — and run an XLOOKUP (or the older VLOOKUP) from books into 2B, then again from 2B into books. Bucket the result into three piles: matched on both sides, in your books but not in 2B, and in 2B but not in your books. The first pile is claimable credit, the second is credit at risk, and the third is something your supplier reported that you have not booked. That is the whole exercise, and at small scale it is genuinely enough.

The first thing that breaks is the join key, because invoice numbers almost never match character for character. Your books carry INV/2026/0042; the supplier reported INV-2026-42 to the portal, dropping the leading zeros and swapping the separator. A lookup on the raw string treats those as two different invoices, so a genuine match lands in your 'at risk' pile and you spend an afternoon chasing a supplier who actually filed correctly. The Excel fix is a tower of SUBSTITUTE and TEXT functions to normalise separators and pad numbers — rebuilt every time a new supplier joins with a new numbering convention, and every normalisation rule you add is a fresh place for a real mismatch to hide.

The second break is tolerance. Tax computed in your books can differ from the portal figure by a rupee or two on rounding, so an exact-value match flags clean invoices as mismatched and you end up eyeballing a column of ₹0.50 and ₹1.20 differences, deciding by hand which to ignore — exactly the judgement that stops being reliable at volume. The third break is the one Excel cannot solve at all: an invoice missing from 2B because the supplier has not filed their GSTR-1. That is not a formula error, it is blocked working capital, and what you actually need is to total it, group it by supplier, chase those suppliers before the deadline, and check next month whether the credit has appeared. A spreadsheet shows you this month's gap; it does not carry it forward to confirm the recovery.

The fourth break is scale meeting the new rules. Past a few hundred invoices across dozens of suppliers, the per-row review simply exceeds what anyone will do reliably every month, and silent errors creep in. On top of that, since the Invoice Management System arrived in late 2024, invoices flow through Accept, Reject and Pending states before they settle into 2B, and GSTR-3B has been hard-locked — the figures auto-populate and you file against them. That turns reconciliation from a leisurely post-filing clean-up into a pre-filing gate: it has to be right before you file, not discovered to be wrong in a notice months later. An Excel process that takes three days does not fit inside that gate.

This is where a purpose-built tool earns its place — not because Excel is wrong, but because the failure modes above are mechanical and repeatable. ReconPe matches each invoice on the e-invoice IRN where it exists and falls back to GSTIN plus a normalised invoice number and date within a small tax tolerance, so format drift and rounding stop generating false mismatches. Booked-but-missing invoices roll up into a single input-tax-credit-at-risk figure and a per-supplier chase-list; attach next month's 2B and anything the supplier filed late is reclassified as deferred credit rather than written off. It does not file your return and it does not decide on your behalf — it surfaces the matched, the missing and the mismatched, and you approve. Three GST reconciliations are free, which is enough to run a full month's close and see the credit your spreadsheet was quietly leaving on the table.