Introduction
Every month, tax professionals and accountants face the same tedious task — matching Input Tax Credit (ITC) as per GSTR-2B with the purchase entries recorded in the books of accounts (Tally). Doing this manually in Excel with VLOOKUP formulas is time-consuming, error-prone, and painful when the data runs into hundreds or thousands of rows.
To solve this problem, I have developed a fully automated Excel-based GST Reconciliation Tool that reconciles Books data with GSTR-2B data at the click of a button — no manual formulas, no copy-pasting, no VLOOKUP headaches.
In this article, I am sharing complete details of the tool, how it works, and a step-by-step guide to use it.
What This Tool Does
The tool works entirely inside Excel (macro-enabled) and has five simple buttons that automate the entire reconciliation workflow:
1. Import Book Data (Tally)– Pulls your purchase register exported from Tally into the tool.
2. Import 2B Data– Pulls your GSTR-2B data (downloaded from the GST Portal) into the tool.
3. Run Reco– Automatically matches Books vs 2B on parameters like GSTIN, Document Number, and Taxable Value/Tax Amount, and flags mismatches.
4. Generate Summary– Creates a ready-to-use summary report of matched, unmatched, and mismatched entries.
5. Clear All Sheet– Resets the tool in one click so you can start a fresh reconciliation for the next month/client.
No manual formulas. No dragging VLOOKUP. Just import, click, and reconcile.
Step-by-Step Guide to Use the Tool
Step 1: Prepare Your Books Data (from Tally)
Export your purchase register from Tally in Excel format. Make sure while exporting GST Number and Document Number columns are turned ON — the reconciliation logic depends on these two fields matching correctly.
Step 2: Prepare Your GSTR-2B Data
Download the GSTR-2B Excel file from the GST Portal. Before importing:
- Replace all negative (-) values in Credit Notes with blank, otherwise the reco engine may misread them.
- Replace the “/” in the date format with “-“(e.g., convert 01/10/2025 to 01-10-2025), since Excel date functions used in the tool require this format.
Step 3: Import Book Data
Click on the “Import Book Data Tally” button and select your Tally-exported purchase register. The data will be pulled automatically into the tool’s Books sheet.
Step 4: Import 2B Data
Click on the “Import 2B Data” button and select your cleaned GSTR-2B Excel file. The 2B data will populate into its respective sheet.
Step 5: Run Reconciliation
Click “Run Reco”. The tool will automatically compare both datasets and highlight:
- Invoices matched in both Books and 2B
- Invoices available in Books but missing in 2B (vendor hasn’t filed)
- Invoices available in 2B but missing in Books (possible booking omission)
- Value/tax mismatches between the two
Step 6: Generate Summary
Click “Generate Summary” to get a clean, presentable summary sheet — ideal for client reporting or internal review, and useful as backup while filing GSTR-3B.
Step 7: Clear and Reuse
Once done, click “Clear All Sheet” to reset all data with one click and start the next reconciliation (next month or next client) with a clean sheet.
Important Points to Remember
| # | Point |
| 1 | Always export the Books/3B data from Tally with GST Number and Document Number columns enabled. |
| 2 | Replace negative values in Credit Notes with blank before importing. |
| 3 | While downloading GSTR-2B, replace “/” with “-“ in the date column. |
| 4 | Always follow the sequence: Import Books → Import 2B → Run Reco. Following this order avoids errors and makes troubleshooting easy. |
| 5 | If you face any issue while using the tool, feel free to reach out — genuine queries are always welcome. |
Who Can Use This Tool?
- Practicing Chartered Accountants / Tax Consultants doing monthly ITC reconciliation for multiple clients
- In-house accounts/finance teams handling GST compliance
- GST practitioners who currently rely on manual VLOOKUP-based reconciliation
Conclusion
GST reconciliation between Books and GSTR-2B is a recurring monthly compliance requirement, and doing it manually eats up valuable time that could be used for more productive client work. This Excel-based automation tool aims to make that process simple, fast, and error-free — with just five buttons doing all the heavy lifting.
I have built this tool based on real practical challenges faced during GST return filing, and I am sharing it so that fellow professionals can benefit from it as well.
******
For queries, feedback, or support, feel free to reach out at: santubittu@zohomail.in
Disclaimer: This tool is shared for general utility purposes to assist in GST reconciliation. Users are advised to independently verify the reconciled data before relying on it for statutory filings such as GSTR-3B.
