How to Do GSTR-2B Reconciliation in Excel Step by Step

GSTR 2B reconciliation in Excel means comparing the purchase invoices recorded in your books with the invoices appearing in GSTR-2B downloaded from the GST Portal.

In simple words, your Purchase Register shows what you have entered in your books, while GSTR-2B shows the inward-supply and ITC-related information communicated to you through the GST system for the relevant period. Under Section 16, supplier-furnished invoice or debit-note details communicated to the recipient are one of the conditions connected with ITC eligibility.

You can do this matching manually using Excel, XLOOKUP or VLOOKUP. This guide explains the complete process in simple steps. At the end, we will also explain how CAWork Suite works as a GSTR 2B reconciliation tool when manual Excel matching becomes time-consuming.

Table of Contents

1. What is GSTR-2B reconciliation in Excel?

2. Which files are required?

3. Which columns should you compare?

4. How to prepare the Excel files

5. How to clean invoice numbers

6. How to match GSTR-2B with Purchase Register

7. How to find amount and GST differences

8. How to identify missing invoices

9. How to reconcile GSTR-2B with Tally

10. Common Excel reconciliation mistakes

11. How CAWork Suite automates reconciliation

12. FAQs

What Is GSTR-2B Reconciliation in Excel?

GSTR-2B reconciliation in Excel is a manual process where you compare invoice-level data from two sources:

GSTR-2B
        +
Purchase Register
        ↓
Invoice Matching
        ↓
Matched / Mismatched / Missing
        ↓
ITC Review

GSTR-2B is generated monthly under Rule 60, and the GST rules specifically refer to invoice/debit-note ITC details being communicated to registered persons in Form GSTR-2B.

The objective is not simply to check whether the totals are equal.

You should check each invoice and ask:

• Is the supplier GSTIN the same?

• Is the invoice number the same?

• Is the invoice date correct?

• Is the taxable value the same?

• Does IGST match?

• Do CGST and SGST match?

• Is there any credit or debit note?

• Is an invoice missing from either file?

This is also called GSTR 2B reconciliation with books or reconciliation of GSTR 2B with books.

Which Files Are Required?

You normally need two files.

File 1: GSTR-2B

Download GSTR-2B for the required GSTIN and return period from the GST Portal.

Keep the original downloaded file unchanged.

File 2: Purchase Register

Export your Purchase Register from:

• Tally

• ERP software

• Accounting software

• Excel

• Another bookkeeping system

Your Purchase Register should ideally contain:

• Supplier GSTIN

• Invoice Number

• Invoice Date

• Taxable Value

• IGST

• CGST

• SGST/UTGST

• Cess, where applicable

• Total Invoice Value

Now create a separate Excel working file so you do not change your original data.

Which Fields Should You Match?

A good manual reconciliation starts with the correct matching fields.

Do not compare only the final total of GSTR-2B with the final total of your Purchase Register.

Two totals can be equal even when individual invoices are wrong.

How to Do GSTR-2B Reconciliation in Excel

Step 1: Create Two Separate Excel Tables

Keep one table for:

Purchase Register

and another for:

GSTR-2B

For easier formulas, convert both datasets into Excel Tables using:

Insert → Table

You can name them:

PurchaseRegister

and

GSTR2B

This makes XLOOKUP formulas easier to understand.

Step 2: Clean Supplier GSTIN

GSTIN should normally be compared after removing accidental spaces and changing text to a common case.

For example:

24ABCDE1234F1Z5

and:

24abcde1234f1z5

should not fail only because one value is lowercase.

A simple Excel Table formula is:

=UPPER(TRIM([@[Supplier GSTIN]]))

Create a helper column named:

Clean GSTIN

Do this in both the Purchase Register and GSTR-2B working.

Step 3: Clean Invoice Numbers

Invoice-number formatting is one of the most common reconciliation problems.

For example:

INV-001
INV/001
inv 001
INV001

may represent the same invoice.

Create a helper column called:

Clean Invoice Number

A simple Excel Table formula can be:

=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM([@[Invoice Number]]),"-",""),"/","")," ",""))

This removes:

• Spaces

• Hyphens

• Slashes

• Upper/lower-case differences

Do not remove characters blindly where doing so could change the actual document identity. Always keep the original invoice number in a separate column.

Step 4: Create a Matching Key

Now combine:

Clean GSTIN + Clean Invoice Number

Example:

24ABCDE1234F1Z5|INV001

Excel Table formula:

=[@[Clean GSTIN]]&"|"&[@[Clean Invoice Number]]

Name this column:

Match Key

Create the same key in both datasets.

This simple key becomes the base for your GSTR 2B matching tool in Excel.

Step 5: Find the Invoice in GSTR-2B

Now use XLOOKUP.

For example, create a new column in the Purchase Register called:

2B Taxable Value

Use:

=XLOOKUP([@[Match Key]],GSTR2B[Match Key],GSTR2B[Taxable Value],"Not Found")

Excel will search the Purchase Register invoice in GSTR-2B.

If the invoice exists, it returns the GSTR-2B taxable value.

If not, it shows:

Not Found

You can repeat the same method for:

• IGST

• CGST

• SGST

• Invoice Date

• Invoice Value

If your Excel version does not support XLOOKUP, VLOOKUP, INDEX-MATCH or Power Query can also be used.

Step 6: Identify Exact Matches

Suppose:

Purchase Register:

Supplier GSTIN: 24XXXXXXXXXXXXX
Invoice: INV-105
Taxable Value: ₹10,000
IGST: ₹1,800

GSTR-2B:

Supplier GSTIN: 24XXXXXXXXXXXXX
Invoice: INV105
Taxable Value: ₹10,000
IGST: ₹1,800

After cleaning the invoice number:

INV-105 → INV105

The invoice details match.

Mark this as:

Matched

Step 7: Find Amount Differences

An invoice can have the correct GSTIN and invoice number but a different amount.

Example:

Purchase Register:

Taxable Value = ₹10,000

GSTR-2B:

Taxable Value = ₹12,000

Difference:

₹12,000 − ₹10,000 = ₹2,000

This should be reviewed.

A simple Excel check is:

=IF(ROUND([@[Purchase Taxable Value]],2)=ROUND([@[2B Taxable Value]],2),"Matched","Amount Difference")

You should similarly compare:

Purchase IGST vs GSTR-2B IGST

Purchase CGST vs GSTR-2B CGST

Purchase SGST vs GSTR-2B SGST

Small paise-level rounding differences can be reviewed separately from material differences.

Step 8: Identify Missing in GSTR-2B

If XLOOKUP returns:

Not Found

the purchase invoice exists in your books but no matching document was found in the GSTR-2B dataset being compared.

Possible reasons can include:

• Supplier has not reported the invoice correctly

• Wrong recipient GSTIN

• Invoice number differs

• Invoice is reflected in another period

• Supplier amendment is involved

• Your Purchase Register contains an incorrect invoice

GST Portal documentation also provides a taxpayer communication facility where recipients can inform suppliers about missing documents or request corrections.

Do not immediately assume the supplier failed to file. First check all document details.

Step 9: Find Invoices Missing from Your Books

Reconciliation should also work in the opposite direction.

Ask:

Which invoices are in GSTR-2B but not in my Purchase Register?

Possible reasons:

• Purchase was not entered in books

• Invoice belongs to another branch

• Supplier reported the wrong GSTIN

• Duplicate invoice

• Invoice does not belong to your business

For complete GSTR 2B reconciliation with books, you need to check both directions.

Step 10: Create a Final Status Column

Your final Excel working can classify invoices into:

This is much more useful than simply writing:

Purchase Register total ₹10 lakh
GSTR-2B total ₹9.8 lakh
Difference ₹20,000

Invoice-level reconciliation tells you where that ₹20,000 difference comes from.

How to Reconcile GSTR-2B with Tally in Excel

If you use Tally, first export your Purchase Register.

Then follow the same process:

Tally Purchase Register
        +
GSTR-2B
        ↓
Clean GSTIN & Invoice Number
        ↓
Create Match Key
        ↓
XLOOKUP / Power Query
        ↓
Compare Taxable Value & GST
        ↓
Review Missing and Mismatched Invoices

This workflow is useful for users searching reconcile GSTR 2B with Tally or looking for a GSTR 2B and purchase data reconciliation tool.

The important point is that the source of your books can be Tally, ERP or Excel. The matching logic remains invoice-based.

Important: A Match Does Not Automatically Guarantee ITC

Finding the same invoice in both files is important, but it does not automatically prove that ITC is finally eligible.

Section 16 includes conditions relating to prescribed documents, supplier-furnished details being communicated to the recipient, receipt of goods/services, tax conditions and filing of the relevant return. The law also provides for restrictions communicated through the GST system.

So:

Matched = the uploaded/book records agree.

It does not mean:

ITC is legally guaranteed.

Final ITC eligibility should be reviewed separately.

Common Mistakes in GSTR-2B Reconciliation in Excel

Avoid these mistakes:

• Comparing only grand totals

• Matching invoice number without supplier GSTIN

• Ignoring spaces, - and / in invoice numbers

• Deleting original invoice numbers after cleaning

• Ignoring credit notes and debit notes

• Matching only taxable value

• Ignoring IGST, CGST and SGST differences

• Checking only invoices missing from GSTR-2B

• Ignoring invoices missing from books

• Mixing different GSTINs or return periods

• Treating every matched invoice as automatically eligible ITC

• Overwriting the original GST Portal file

For small datasets, Excel can work well. For thousands of invoices, maintaining formulas and manually reviewing differences becomes much more time-consuming.

That is where GST reconciliation software or a dedicated GST reconciliation tool becomes useful.

How CAWork Suite Automates GSTR-2B Reconciliation

Why Repeat the Same Excel Formulas Every Month?

Manual GSTR 2B reconciliation in Excel means:

• Cleaning invoice numbers

• Creating matching keys

• Writing XLOOKUP formulas

• Checking GSTINs

• Comparing taxable values

• Comparing GST

• Finding missing invoices

• Reviewing differences one by one

CAWork Suite simplifies this through an organised GSTR 2B reconciliation tool.

Three Supported Reconciliation Modes

CAWork Suite supports:

1. GSTR-2B vs Purchase Register
For normal monthly reconciliation.

2. GSTR-2A vs Purchase Register
For comparing Purchase Register data with GSTR-2A.

3. GSTR-2B vs GSTR-2A
For diagnostic comparison between GST statement data.

Step 1: Select Reconciliation Type

Choose the comparison you want to run.

Step 2: Upload the Two Files

Upload supported:

• GSTR-2A/GSTR-2B JSON or Excel downloaded manually from GST Portal

• Purchase Register exported from Tally or another ERP

CAWork Suite does not connect directly to GSTN through an API.

You download and upload the files yourself.

Step 3: Automatic Matching

CAWork Suite normalises supported invoice information such as:

• Supplier GSTIN

• Invoice number

• Invoice date

• Taxable amount

• CGST

• SGST

• IGST

It also normalises common invoice-number formatting differences such as spaces, hyphens, slashes, case and leading zeros.

Current CAWork Suite Matching Logic

For its current exact matching:

Same GSTIN + Same normalised invoice number

If the amount difference is within the tool's configured ₹2 tolerance, the result is shown as matched.

If the difference exceeds that tolerance, it is shown as an amount difference.

The tool also performs another matching pass where an invoice number can match while GSTIN differs, provided taxable amount remains within its configured 2% tolerance. This helps surface possible GSTIN differences.

₹2 and 2% are CAWork Suite matching tolerances designed for reconciliation. They are not statutory GST tolerances or legal ITC rules.

Step 4: Review the Dashboard

The reconciliation dashboard can show:

• Matched invoices

• Amount differences

• GSTIN differences

• Missing invoices

• Extra records

• ITC-related summary

• Searchable invoice results

• Plain-language guidance

Supported reports can also be exported in Excel and PDF format.

CAWork Suite's ITC figures are reconciliation indicators based on uploaded records. Final eligibility remains subject to GST law.

Stop Doing GSTR-2B Reconciliation Manually

Instead of maintaining VLOOKUPs, XLOOKUPs and multiple working sheets every month, CAWork Suite provides a repeatable GSTR 2B matching tool workflow:

Select Reconciliation Type → Upload GSTR-2A/2B + Purchase Register → Automatic Matching → Review Differences → Export Report

For businesses, accountants and CA firms with large Purchase Registers, a dedicated GSTR 2B reconciliation software can save repeated manual Excel work.

CTA Button: Start GSTR-2B Reconciliation

Related GST & Accounting Guides

To build a complete GST and accounting workflow, you can also explore dedicated guides for:

• GSTR 2B reconciliation

• GSTR 2B reconciliation with books

• Reconciliation of GSTR 2B with books

• GSTR 2B matching tool

• GSTR 2B reconciliation software

• Reconcile GSTR 2B with Tally

• GST reconciliation software

• GST reconciliation tool

• Bank statement to Tally

• Bank statement import in Tally Prime

• Import bank statement in Tally

• Tally Prime bank statement import

• Bank statement PDF to Tally import

• GSTR-1 for ecommerce sellers

• Tally for ecommerce

These topics should have their own dedicated pages rather than forcing every keyword into this Excel guide.

Frequently Asked Questions

1. What is GSTR-2B reconciliation in Excel?

It means using Excel to compare GSTR-2B invoice data with your Purchase Register and identify matched, missing and mismatched records.

2. Which fields should I match?

Start with supplier GSTIN and invoice number, then compare invoice date, taxable value, IGST, CGST, SGST and cess where applicable.

3. Can I use XLOOKUP for GSTR-2B reconciliation?

Yes. XLOOKUP can search a cleaned GSTIN-and-invoice matching key and return taxable value, GST or other GSTR-2B fields.

4. Can I use VLOOKUP instead?

Yes. VLOOKUP can also be used, although XLOOKUP or Power Query can be easier for larger or more flexible reconciliation work.

5. Why does an invoice in my books not appear in GSTR-2B?

Possible reasons include supplier reporting issues, wrong GSTIN, invoice-number differences, period differences or amendments. Verify the details before taking action.

6. What if an invoice is in GSTR-2B but not in my books?

Check whether the purchase was not booked, belongs to another branch, was wrongly reported against your GSTIN or is a duplicate.

7. Can I reconcile GSTR-2B with Tally using Excel?

Yes. Export your Tally Purchase Register and compare it with GSTR-2B using supplier GSTIN, invoice number and tax values.

8. Does a matched invoice automatically mean ITC is eligible?

No. Reconciliation confirms that records agree. Final ITC eligibility is subject to the applicable conditions under GST law.

9. When should I use GSTR-2B reconciliation software?

Software becomes useful when you have many invoices and manual cleaning, XLOOKUP formulas, mismatch checking and repeated monthly reconciliation take too much time.

10. How does CAWork Suite help?

CAWork Suite lets you upload supported GST statement and Purchase Register files, automatically matches invoices, highlights differences and missing records, and provides reconciliation results for review and export.

Conclusion

GSTR 2B reconciliation in Excel is useful when you have a manageable number of purchase invoices and want full control over the matching process.

Start by downloading GSTR-2B and exporting your Purchase Register. Clean GSTINs and invoice numbers, create a matching key, use XLOOKUP or another Excel method, compare taxable value and GST, and then separate matched, mismatched and missing invoices.

For larger datasets, the same process can become repetitive. CAWork Suite provides a GST reconciliation software workflow where you upload the supported files, automatically match the invoices, review differences and export the reconciliation report.

Disclaimer: GST information in this guide was reviewed in August 2026. This article is for educational purposes and is not tax or legal advice. GST rules and ITC conditions can change. Review important mismatches and applicable ITC conditions before filing.