Excel Utility for Automated GSTR-2B vs Books ITC Matching

Automated ITC Matching Utility: A Practical Excel-Based Solution for GSTR-2B vs Books Reconciliation

Input Tax Credit (ITC) reconciliation has become a critical compliance task for every business registered under GST in India. With the introduction of GSTR-2B as a static, auto-drafted statement, matching ITC between the books and GSTR-2B is now essential to avoid ITC mismatches, unnecessary tax outflows, interest on excess ITC claims, and notices such as DRC-01A, DRC-01, scrutiny, or audit queries.​

To make this process faster and more reliable, this fully automated, Excel-based ITC Matching Utility has been designed to reconcile GSTR-2B with your purchase register without the need for any additional software. It works smoothly across commonly used Excel versions (from Excel 2007 to Office 365) and is particularly handy for accountants, tax professionals, and businesses looking to automate reconciliation using familiar tools.​

Why use this Excel ITC Matching Utility?

This utility is built around real-world issues faced by taxpayers, accountants, and compliance teams during GST reconciliation. Key advantages include:​

• Plug-and-play usage: Simply paste GSTR-2B and Books data in the specified sheets and the utility performs reconciliation automatically.

• Protected, formula-driven logic

  • All workings run through formulas in the backend, which are locked to maintain accuracy and prevent accidental changes.

  • Users only interact with input areas; core logic remains intact.

• Works on almost all Excel versions: Compatible with Excel 2007, 2010, 2013, 2016, 2019, 2021, and Office 365, so no version-specific dependency issues.

• No macros or add-ins: Many organisations restrict macro-enabled files, so this tool is built purely with formulas to ensure better security and easier IT approvals.​

• Suitable for teams: The file can be safely shared with junior staff or clients, as the computation logic is protected and cannot be altered.

Utility structure: sheet-wise layout

The file is organised into clearly defined sheets, each with a specific purpose:

1. GSTR2B sheet (user input)

• Paste GSTR-2B data downloaded from the GST portal into predefined columns such as:

  • Supplier GSTIN

  • Invoice number and date

  • Invoice value and taxable value

  • IGST, CGST, SGST, CESS

• Only these input fields are editable; the rest of the sheet works through formulas.

2. BOOKS sheet (user input)

• Paste the purchase register exported from accounting systems like Tally, Busy, Marg, SAP, Oracle, Zoho Books, QuickBooks, etc.​

• The column structure matches the GSTR-2B sheet to allow seamless one-to-one matching.

3. 2B_Reco sheet (core reconciliation)

• This sheet performs the main comparison and shows:

  • Invoices that match exactly with the books

  • Cases where tax amounts differ

  • Invoices appearing in GSTR-2B but not in Books

  • Cross-references to the corresponding Books rows

  • Differences in IGST, CGST, and SGST values

• No manual input is required on this sheet.

4. Books_Not_in_2B sheet

• Lists invoices recorded in the books but not appearing in GSTR-2B for that period.

• Helps identify vendors who have not filed/updated returns, returns or cancellations, wrong entries, or ITC that may not be eligible for that month.​

5. 2B_Mismatch sheet

• Consolidates:

  • Tax mismatch cases

  • Invoices reflected in GSTR-2B but not found in Books

• Useful for preparing replies to notices, scrutiny, or GST audit questions.

6. Party Summary sheet

• Provides supplier-wise ITC analysis, including:

  • Total invoices

  • Count of exact matches and mismatches

  • Invoices missing in Books

  • ITC as per GSTR-2B vs ITC as per Books and the difference between them

• Makes vendor-level follow-up and communication more focused and data driven.​

7. Management Report (Mgmt Report) sheet

• A ready-to-print one-page MIS report summarising:

  • Entity details and period

  • Overall monthly ITC position

  • Total mismatch value and reasons

  • Space for remarks, “Prepared by” and “Checked by” details

• Designed for sharing with management or clients in a professional format.

Key benefits of using this utility

• Major time savings: Manual matching of large invoice volumes is replaced by automated comparison, typically cutting reconciliation time by around 80% in routine use.​

• Fewer errors: Formula-based matching reduces manual mistakes and improves accuracy in ITC claims.

• Better audit readiness: Mismatch reports and summary sheets support GST, internal, statutory, and departmental audits by making data easily traceable.​

• Easy for teams to adopt: Only two sheets require data entry (GSTR2B and BOOKS); all other outputs are auto-generated.

• Professional documentation: MIS and party-wise reports help present a consulting-quality output to clients and management.

• No extra software cost: Uses only Excel, so it acts as a cost-effective alternative to specialised GST reconciliation software.​

How to use the ITC Matching Utility

• Step 1: Paste GSTR-2B data in the GSTR2B sheet starting from cell A2.

• Step 2: Paste purchase data from the accounting system into the BOOKS sheet starting from cell A2.

• Step 3: Open the 2B_Reco sheet to view the reconciliation results.

• Step 4: Review differences under the 2B_Mismatch and Books_Not_in_2B sheets.

• Step 5: Check supplier-wise analysis in the Party Summary sheet.

• Step 6: Generate and print the monthly summary from the Mgmt Report sheet for management or client review.

In most cases, this end-to-end process can be completed in just a few minutes per tax period once data exports are ready.​

Who can benefit the most?

This utility is suitable for a wide range of users handling GST compliance, including:

• Chartered Accountants and CA firms

• Tax consultants and GST practitioners

• In-house finance and accounts teams

• MSMEs and growing businesses

• Corporate accounts and compliance departments

Overall, this Excel-based ITC Matching Utility offers a secure, practical, and efficient way to reconcile ITC between GSTR-2B and the books, without depending on external software or complex implementations. It improves accuracy, speeds up reconciliation, and enhances the quality of reports, making it a strong fit for professionals, corporates, and MSMEs across India who want better control over their ITC claims.​