GST Reconciliation in Excel: GSTR-2B vs Purchase Register, Step by Step
Your input tax credit depends on your purchase register and the portal agreeing. This guide covers the Excel method — the key, the match, the tolerance, and the five categories every mismatch falls into.
Short answer
This is a technique guide, not tax advice
The reconciliations a GST-registered business actually runs
| Reconciliation | Books side | Portal side | Purpose |
|---|---|---|---|
| Inward — ITC | Purchase register | GSTR-2B for the period | Confirm the input tax credit you are claiming appears in the portal |
| Outward | Sales register | GSTR-1 filed | Confirm what you reported matches what you billed |
| Return vs return | GSTR-3B as filed | GSTR-1 as filed | Catch differences between the two returns for the same period |
| Annual | Books for the year | GSTR-9 / 9C position | Reconcile the year, not just the month |
This article deals with the first row, because it is the one that affects cash: input tax credit you have claimed in your books but which is not visible in the portal is credit you may not be able to use.
What you download, and why the export is messy
| File | Source | What to expect |
|---|---|---|
| GSTR-2B | GST portal — Returns Dashboard, then download for the period | A JSON or Excel download depending on how you export it; the Excel route often needs the portal's own utility, so many teams convert to CSV first |
| Purchase register | Tally, your ERP, or the accounting package | One row per invoice line, which means one invoice can appear across several rows |
One invoice, many rows
SUMIFS rather than a lookup.Step 1: build the key on both sides
The key is the supplier GSTIN and the invoice number, joined. The GSTIN is already a fixed 15 characters, so the work is almost entirely in the invoice number.
| Same invoice, written four ways | Normalised |
|---|---|
| INV/2025-26/0417 | 0417 |
| INV-2025-0417 | 0417 |
| 0417 | 0417 |
| INV20250417 | 0417 |
In Excel, build the key in a helper column on both sheets:
- 1Strip separators and spaces from the invoice number:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"/",""),"-","")," ","")) - 2Force the case so that a supplier's lower-case entry does not become a difference: wrap the above in
UPPER(...). - 3Join it to the GSTIN:
=UPPER(TRIM(B2))&"|"&UPPER(cleaned_invoice) - 4Do exactly the same on the other side, with exactly the same formula. The two formulas must be identical, not merely similar.
Trailing and non-printing characters
TRIM does not remove. If two keys still look identical but do not match, wrap the value in CLEAN() as well and re-check. This one issue accounts for a large share of “impossible” mismatches.Step 2: match with SUMIFS, not a lookup
On the 2B side, pull the taxable value and each tax head from the purchase register using SUMIFS against the normalised key. Because SUMIFS sums every matching row, a multi-line invoice totals correctly where a lookup would return only the first line.
| Column | Formula shape | What it gives you |
|---|---|---|
| Taxable value in books | =SUMIFS(Purchase!$E:$E, Purchase!$H:$H, $A2) | Total taxable value booked for that key |
| Tax in books | =SUMIFS(Purchase!$G:$G, Purchase!$H:$H, $A2) | Total tax booked for that key |
| Difference | =C2-D2 | Books minus 2B, so the direction is visible |
| Status | =IF(COUNTIF(Purchase!$H:$H,$A2)=0, "Missing in books", IF(ABS(E2)<=1, "Matched", "Amount difference")) | A provisional category per row |
Step 3: apply the tolerance before you judge anything
Line-level rounding produces paise differences that are not mismatches. Compare the absolute difference against a stated tolerance rather than testing for exact equality, and write the tolerance into the working paper.
| Difference in the row | Treatment |
|---|---|
| ₹0.00 | Matched |
| Up to ₹1.00 | Matched within the documented tolerance |
| ₹1.01 to ₹100 | Amount difference — usually a rate or discount applied on one side only |
| Over ₹100 | Amount difference — investigate before claiming the credit |
| No match at all on the key | Missing on one side — categorise which side |
Step 4: put every exception in a category
| Category | What it means | Consequence to check |
|---|---|---|
| Missing in 2B | In your purchase register, not in the portal for this period | The supplier may not have filed, or filed late — the credit is exposed until they do |
| Missing in books | In the portal, not in your purchase register | An invoice received but not booked, or booked in a different period |
| Amount difference | Present on both sides, different values | A rate, discount or freight amount treated differently by the two sides |
| GSTIN difference | The invoice exists but under a different registration | Common where a supplier bills from a different state unit than the one you have on file |
| Credit note | A reduction recorded by one side only | Check whether the credit note is reflected in both sets of records |
The working paper is finished when every row carries one of those five labels and the totals on both sides have been stated. What you do with each category afterwards is a tax decision, not a spreadsheet one.
Credit notes, amendments and unusual cases
- Credit notes rarely match cleanly. They are often recorded against the original invoice number on one side and as a separate sequence on the other. Match credit notes on amount and party where the number does not correspond.
- Amendments create two records for one document. Where a supplier has amended an invoice, the portal may show both the original and the amendment. Decide how you will treat the pair before you start, or you will double-count.
- Multi-GSTIN businesses need a per-registration reconciliation. Mixing registrations into one sheet produces GSTIN differences that are not real.
- Reverse charge entries have no supplier invoice. Remove them from the matching population rather than leaving them to sit as permanent exceptions.
What breaks in Excel
- Large registers slow the file down. A whole-column reference inside
SUMIFS, repeated down 40,000 rows, is a heavy calculation — limit the ranges to the actual data. - Portal export formats change. The columns you mapped last quarter may not be the columns you get this quarter, which breaks every formula that referenced them by position.
- Nothing is recorded about why a row matched. If the reconciliation is questioned later, the reasoning has to come from the person who built the sheet.
- The tolerance is invisible unless you write it down. A hard-coded ₹1 buried in a formula is not a documented policy.
When to stop rebuilding the sheet each month
For one registration, Excel is the right tool and the method above will serve you every month. The case for something else arrives when you are doing this for several registrations or several clients, when the key-building steps have to be redone because the export format changed, and when the tolerance and category rules live only in your head.
The Piloteq Automate reconciliation workflow applies the same rules as a saved set rather than a formula sheet. If your situation is many clients rather than many registrations, the guide to automating GST reconciliation across clients is the more relevant read.
If you would rather not build this by hand
Reconcile books against portal data without rebuilding the sheet
Piloteq Automate runs the same match you would build in Excel — GSTIN plus invoice number, with exact and last-digit rules, amount tolerance and group matching — and writes the reason for every record. Matched, unmatched and amount differences come back as separate result sets you can review before filing.
- ✓Composite key matching with an amount tolerance you set
- ✓Group matching for invoices split across several lines
- ✓Duplicate detection for repeated invoice numbers
- ✓Amount difference analysis with the delta per record
Frequently asked questions
How do I reconcile GSTR-2B with my purchase register in Excel?+
Build a key on both sides by joining the supplier GSTIN and the invoice number, normalised to remove slashes, spaces and leading zeros. Match the two using SUMIFS on that key so that a supplier invoice split across several lines returns one total, then compare the taxable value and the tax amounts within a small tolerance. Everything that does not match goes into one of five categories — missing in 2B, missing in books, amount difference, GSTIN difference or credit note.
Why do invoices match in GSTR-2B but not in my purchase register?+
Usually because the invoice number was recorded differently — the supplier used a slash where you used a hyphen, or their software added a prefix. Less often, it is a genuine timing difference where the supplier filed in a later period. Normalising the key resolves the majority of apparent mismatches before you treat any of them as a real ITC problem.
What tolerance should I use when comparing GST amounts?+
A rupee-level tolerance is common and practical, because rounding on line-level tax calculations produces small differences that are not real mismatches. Whatever tolerance you choose, state it on the working paper. A documented tolerance is a reconciled difference; an undocumented one looks like an unexplained figure.
What is the difference between GSTR-2A and GSTR-2B?+
GSTR-2A is a dynamic statement that changes as suppliers file, so the same download on two different days can differ. GSTR-2B is a static, period-specific statement generated for each tax period, which is why it is the practical basis for a monthly input tax credit reconciliation. Reconcile against 2B for the period you are filing, and treat 2A as a view of supplier filing activity rather than as your reconciliation basis.
Do I need to reconcile GST every month?+
Monthly reconciliation keeps the number of open items small enough to follow up on. If you reconcile only at the end of the year, the supplier chase becomes impractical because the periods are closed and the supplier's own records may no longer be adjustable. For most businesses, monthly reconciliation of 2B against the purchase register before filing the period's return is the workable cadence.
Has the GST reconciliation process changed recently?+
GST rules and portal features change frequently, and the Invoice Management System and the treatment of mismatches have both been the subject of recent changes. Treat the mechanics in this guide — building a key, matching, categorising differences — as stable, and check the current position for the rules that apply to a specific period against the GST portal or the relevant notification before you rely on it. This guide describes Excel technique, not tax advice.
Related guides
Excel Reconciliation: The Complete Guide
Match keys, normalisation and tolerance — the foundation this guide builds on.
TDS Reconciliation in Excel
The same approach applied to 26AS and your TDS ledger.
Automating GST Reconciliation Across Clients
How CA firms turn this monthly task into a repeatable workflow.