- Monthly Compliance: Download GSTR-2B on 14th, reconcile before 20th filing - prevents 80% of audit issues
- Tally Users: ₹800-1,000 add-on solves credit note export problem (saves 45-60 min/cycle)
- Excel Method: Simple VLOOKUP formulas work - no expensive software needed for most businesses
- Notice Response: Use 6 ready-made formats with invoice-level reconciliation and supporting documents
- Key Tool: GSTR-2B (not 2A) is official since Jan 2021 - only claim ITC shown in 2B
GST Audit Summary Sheets: Complete Excel Format Guide 2025 - Learn With Amrut
Introduction: The GST Audit Reality
GST compliance relies on automated systems that instantly detect differences between filed returns and accounting records. When mismatches appear, you receive notices requiring immediate explanation.
This guide walks you through the entire process—from understanding notices to building practical reconciliation systems using simple Excel formulas.
What You'll Learn
- Responding effectively to GST mismatch notices
- Essential documents GST officers expect during audits
- Correct methods for exporting data from Tally
- Simple Excel formulas for invoice-level matching
- Ready-to-use formats for reconciliation statements
Part 1: Understanding and Responding to GST Mismatch Notices
During a GST audit summary sheet review, officers often detect issues like GSTR-3B reconciliation errors, GSTR-2B ITC matching gaps, or missing Rule 42 ITC reversals. These lead to mismatch notices requiring prompt replies.
To handle them effectively, businesses must rely on:
- Proper GST compliance documents (invoices, bank proofs, GSTR-2B screenshots)
- Clean master sales register formats and master purchase register formats
- Accurate accounting reconciliation statements prepared in Excel
- Invoice-level reconciliation between GSTR-3B vs books format
- Use of Excel formulas for GST and Tally credit note exports (sometimes with Tally add-ons for credit notes)
- Supporting schedules like HSN-wise summaries and supplier compliance tracking
🔎 Quick Reference: Common GST Notices & Risk Levels
| Notice | Section | What It Means | Timeline | Risk Level |
|---|---|---|---|---|
| ASMT-10 | Sec 61 | Discrepancy in returns vs. system | 30 days | High |
| DRC-01 | Sec 73 | Demand for unpaid/short-paid tax | 30 days | Very High |
| DRC-01A | Sec 74 | Demand with fraud/willful misstatement | 30 days | Critical |
| GSTR-3A | Sec 62 | Best-judgment assessment (non-filing) | Immediate | Critical |
| ADT-01 / ADT-02 | Sec 65 | Audit / Special Audit notice | 15 days / as given | High–Very High |
| REG-03/17/31 | Rule 22–25 | Registration cancellation/rejection | 7–30 days | High–Critical |
| MOV-01 / MOV-06 | Rule 138B / Sec 129 | Detention or confiscation of goods/vehicle | 7 days | High–Very High |
| SPL-01 / SPL-02 | Sec 83 | Provisional attachment of property/bank a/c | Immediate / 30 days appeal | Critical |
Part 2: Essential Documents for GST Audit
📑 Primary Documents
| Document | Must Include |
|---|---|
| Master Sales Register |
|
| Master Purchase Register |
|
| GSTR-3B vs Books Reconciliation |
|
| GSTR-2B ITC Matching Statement |
|
🔔 Important Note: GSTR-2B replaced GSTR-2A from Jan 2021.
• GSTR-2B = static (generated on 14th of month)
• GSTR-2A = dynamic (view-only reference)
📂 Supporting Documents
| Document Type | Purpose / Details |
|---|---|
| Original Tax Invoices | Especially for disputed transactions & values above ₹50,000 |
| Payment Proof | RTGS, NEFT, or cheque entries in bank statements |
| ITC Reversal Register | Rule 42 & 43 workings with GSTR-3B linkage |
| E-way Bill Register | Transportation records linked with invoices |
| HSN/SAC Summary | Classification & tax rates for goods/services |
| Supplier Communication | Emails showing follow-up for mismatches |
| Bank Reconciliation | GST challans vs actual bank debit entries |
| Amendment Register | Corrections filed (returns & invoices) |
Part 3: Exporting Data from Tally
🔍 Understanding Tally's Limitations
Tally was originally designed for accounting. GST modules were added later, which creates gaps in exports:
- Sales register supports Pivot Table export, but the Credit Note register does not
- Department requires item-wise HSN details, not just summaries
- Purchase register exports miss Input Tax Credit (ITC) fields
📦 Sales Register with Credit Notes
The Problem
- Export → Excel (Pivot Table) works for invoices
- But credit notes export only summaries, without item-wise HSN breakup
- Auditors demand invoice + item-level linkage with HSN
The Solution: Tally Add-on
- Cost: ₹800 – ₹1,000 one-time
- Buy from: Authorized Tally Partners only
- Trial: Test with 3-day trial version before purchase
- Requirements: Licensed Tally version needed for full functionality
- Benefits:
- Item-wise details with HSN codes
- CGST, SGST, IGST per line item
- Credit note linked to original invoice
- Audit-ready format
Quick Steps: Contact authorized Tally Partner → Request "Sales Register with Credit Note Export Add-on" → Test trial version → Install on licensed Tally → Follow partner's documentation for usage
After Installation: Export Steps
Step 1: Navigate to Sales Register
Gateway of Tally → Display → Accounts Books → Sales Register
Step 2: Select Period & Press Enter
Press Enter on any month and specify the date range
Step 3: Alt+E
Press Alt+E then Select Current and Press Enter
Step 4: Configure Export Options
Press Configure to configure export settings
Step 5: Enable Pivot Table Export
Select 'Export for Pivot table' and set to 'Yes'
Step 6: Accept Configuration
Press Ctrl+A to Accept the Screen configuration
Step 7: Export to Excel
From Sales Register screen, Press Export button and save as Sales_Master.xlsx
✓ Result: You will get invoice-level information with item-wise GST breakup including HSN codes, CGST, SGST, and IGST details.
📥 Purchase Register with ITC
The Challenge
Purchase Register export doesn't include ITC split (CGST/SGST/IGST).
The Solution
Export ITC Ledgers Separately & Merge in Excel
Step 1: Export Input CGST Ledger
- Display → Account Books → Ledger → Input CGST
- Press Alt+F1 for details
- Press Alt+C to configure columns (Date, Voucher No, Party, Amount)
- Press Ctrl+Alt+E to Export to Excel
- Save as:
Input_CGST.xlsx
Step 2: Export Input SGST & IGST
- Repeat the above process for Input SGST → Save as
Input_SGST.xlsx - Repeat for Input IGST → Save as
Input_IGST.xlsx
Step 3: Export Purchase Register
- Display → Inventory Books → Purchase Register
- Press Alt+F1 for Detailed View
- Press Ctrl+Alt+E → Select Excel Pivot Table
- Select YES for Stock Summary
- Save as:
Purchase_Register.xlsx
Step 4: Combine in Excel
- Open
Purchase_Register.xlsx - Use VLOOKUP to pull ITC from the three ledgers
- Example formula:
=VLOOKUP(A2,Input_CGST!A:F,5,FALSE)Final Structure Needed
| Date | Voucher No | Supplier | GSTIN | Taxable Value | Input CGST | Input SGST | Input IGST | Total ITC |
|---|---|---|---|---|---|---|---|---|
| Merge data from Purchase Register + ITC Ledgers using VLOOKUP | ||||||||
📝 Tally Data Entry Essentials
For Sales
- Enter GSTIN in all party ledgers
- Use correct voucher type: Sales Invoice / Credit Note
- Fill HSN codes in item masters
- Enter Place of Supply & Buyer Reference
For Purchases
- Enter supplier GSTIN in ledgers
- Use Purchase Invoice voucher type only
- Link Input CGST, SGST, IGST ledgers properly
- Separate ledger for blocked ITC (Sec 17(5))
- Enter supplier invoice no. in Reference field
- Mark reverse charge purchases clearly
💡 Author's Suggestion: Automate with Tally Add-on
Instead of manually merging Excel files and using VLOOKUP formulas, consider investing in a Tally add-on from authorized partners. Here's why automation is better:
- Less time-consuming (saves 45-60 min/cycle)
- Error-free data extraction
- No complex Excel formulas needed
- Simple one-click export process
- Sales Register: Item-wise & invoice-wise details automatically
- ITC Register: Complete ITC breakup (CGST/SGST/IGST) in one click
- Audit-ready format with HSN codes
- Credit notes linked to original invoices
Important Note: The ₹800-1,000 cost mentioned above is specifically for the credit note export add-on only. Additional charges may apply for automating Sales Register and ITC Register reports—pricing varies by Tally Partner. Always request a detailed quotation covering all modules you need before making a purchase decision.
Recommendation: For businesses processing 50+ invoices monthly, discuss with management whether to use the manual Excel method (free but time-intensive) or invest in Tally add-ons (one-time cost but automated). Most businesses recover the investment within 2-3 months through time savings alone.
Part 4: Simple Excel Formulas for Reconciliation
📘 Workbook Organization
Create one workbook with these sheets:
- Sales_Data
- Purchase_Data
- GSTR3B_Filed
- GSTR2B_Downloaded
- Reconciliation_3B_Books
- ITC_Matching
- Variance_Register
- Dashboard
🎯 The Goal
Compare your purchase invoices (Books) with GSTR-2B data (Suppliers) to identify mismatches in ITC.
🛠️ Step-by-Step Setup
| Step | Action |
|---|---|
| 1. Prepare Data |
Sheet 1 – Purchase Data • Col A: Invoice No • Col B: Supplier GSTIN • Col C: Invoice Date • Col D: ITC (Books) Sheet 2 – GSTR-2B Data • Col A: Invoice No • Col B: Supplier GSTIN • Col C: Invoice Date • Col D: ITC (2B) (No formula yet – just structured input) |
| 2. Create Matching Sheet |
In Sheet 3 – ITC Matching: • Copy purchase data • Add columns: GSTR-2B ITC, Difference, Status (Setup stage – no formula) |
| 3. Fetch GSTR-2B ITC |
Use VLOOKUP to pull ITC from GSTR-2B for each invoice Formula: =IFERROR(VLOOKUP(A2,GSTR2B_Data!A:D,4,FALSE),"Not in 2B")
|
| 4. Calculate Difference |
Subtract GSTR-2B ITC from Books ITC Formula: =IF(E2="Not in 2B",D2,D2-E2)(D2 = Books ITC, E2 = 2B ITC) |
| 5. Add Status |
Classify the result Formula: =IF(F2=0,"Matched",IF(E2="Not in 2B","Supplier Not Filed",IF(F2>0,"Excess Claimed","Short Claimed")))
|
📊 Essential Formula Snippets
Formula Reference Table
📥 Download Excel Format| Purpose | Formula | Output |
|---|---|---|
| Difference with Status | =IF(B2-C2=0,"Matched",B2-C2) |
Shows "Matched" or the difference |
| Priority Flag | =IF(ABS(D2)>50000,"HIGH",IF(ABS(D2)>10000,"MEDIUM","LOW")) |
Flags high/medium/low based on ₹ difference |
| % Variance | =IF(C2=0,"NA",(B2-C2)/C2) |
Shows difference % vs supplier data |
| Category Sum | =SUMIF(Status_Column,"Excess Claimed",Amount_Column) |
Totals excess claimed ITC |
📈 Creating Summary Reports
Monthly Tax Summary (Pivot Table)
-
Select Sales DataHighlight all sales data including headers
-
Insert → PivotTableClick Insert tab, then PivotTable button
-
Set Rows = MonthDrag Month field to Rows area
-
Set Columns = Tax TypeDrag Tax Type (CGST/SGST/IGST) to Columns area
-
Set Values = Sum of Tax AmountDrag Tax Amount to Values area, ensure it's set to SUM
Supplier Compliance Report (Pivot Table)
-
Select ITC Matching DataHighlight all ITC matching data
-
Insert → PivotTableClick Insert tab, then PivotTable button
-
Set RowsDrag Supplier Name + Status to Rows area
-
Set ValuesAdd Count of Invoices + Sum of Difference
-
Apply FilterFilter Status = "Supplier Not Filed"
⚡ Advanced Option: Power Query
If you want automation:
- Auto-refresh from multiple Excel/CSV files
- Handle messy data better
- Merge GSTR-2B with Books automatically
Note: For most businesses, invoice-level VLOOKUP reconciliation is sufficient.
Part 5: Audit-Ready Formats
Format 1: GSTR-3B vs Books Reconciliation
GSTR-3B Reconciliation Table
📥 Download Excel Format| Particulars | Books (₹) | GSTR-3B (₹) | Difference (₹) | Remarks |
|---|---|---|---|---|
| Outward Supplies | ||||
| Taxable Value | 25,00,000 | 25,00,000 | 0 | Matched |
| IGST | 4,50,000 | 4,50,000 | 0 | Matched |
| CGST | 2,25,000 | 2,25,000 | 0 | Matched |
| SGST | 2,25,000 | 2,25,000 | 0 | Matched |
| ITC Available | ||||
| IGST on Inputs | 1,50,000 | 1,45,000 | 5,000 | Supplier ABC filed late, now in Feb 2B |
| CGST on Inputs | 75,000 | 75,000 | 0 | Matched |
| SGST on Inputs | 75,000 | 75,000 | 0 | Matched |
| ITC Reversed | 8,000 | 8,000 | 0 | Rule 42 calculation attached |
| Net Position | ||||
| Total Output Tax | 9,00,000 | 9,00,000 | 0 | Matched |
| Total ITC | 2,92,000 | 2,87,000 | 5,000 | Timing issue |
| Net Tax Liability | 6,08,000 | 6,13,000 | 5,000 | See above |
Be specific:
- ❌ Bad: "Under review"
- ✓ Good: "Invoice INV/156 dated 28-Jan, supplier filed GSTR-1 on 12-Feb"
Reference supporting documents for ₹10,000+ differences:
- "See Annexure A - invoice and payment proof"
Format 2: GSTR-2B ITC Matching
ITC Matching Table
📥 Download Excel Format| Sr | Supplier | Invoice No | Date | ITC Books | ITC 2B | ITC 3B | Difference | Status | Action |
|---|---|---|---|---|---|---|---|---|---|
| 1 | ABC Suppliers | INV/001 | 15-Jan | 9,000 | 9,000 | 9,000 | 0 | Matched | None |
| 2 | XYZ Traders | INV/045 | 20-Jan | 13,500 | 0 | 13,500 | 13,500 | Not in 2B | Reverse ITC |
| 3 | LMN Industries | INV/112 | 25-Jan | 21,600 | 21,600 | 21,600 | 0 | Matched | None |
| 4 | KLM Enterprises | INV/078 | 28-Jan | 8,100 | 8,100 | 0 | -8,100 | Not claimed | Claim next month |
📱 Scroll horizontally to view all columns on mobile devices
Status Classification:
- Matched: All aligned
- Not in 2B: Supplier hasn't filed (follow up needed)
- Not Claimed: Available but you didn't claim (opportunity)
- Excess Claimed: You claimed more than 2B (reverse immediately)
Color Coding:
- ● Green: Matched
- ● Yellow: Not in 2B (medium risk)
- ● Red: Excess claimed (high risk)
- ● Blue: Not claimed (opportunity)
Format 3: Mismatch Notice Response
Cover Letter
To: The Superintendent/Assistant Commissioner Subject: Response to Notice No. [NUMBER] dated [DATE] - GSTIN [YOUR_GSTIN] Summary: - Total ITC Claimed: ₹ [AMOUNT] - Total ITC in GSTR-2B: ₹ [AMOUNT] - Gross Difference: ₹ [AMOUNT] - Net After Adjustments: ₹ [AMOUNT] Attached: - Annexure A: Invoice-wise reconciliation - Annexure B: Supporting documents - Annexure C: Corrective actions taken Detailed explanation follows. [Signature] [Name, Date]
Invoice-wise Table
📥 Download Excel Format| Sr | Invoice Details | ITC Claimed | ITC in 2B | Difference | Reason | Documents | Action Taken |
|---|---|---|---|---|---|---|---|
| 1 | INV/045, XYZ, 20-Jan | 13,500 | 0 | 13,500 | Supplier filed late on 15-Feb | Invoice, Bank statement | Now in Feb 2B, no reversal needed |
| 2 | INV/112, ABC, 28-Jan | 5,400 | 4,800 | 600 | Amount mismatch, credit note issued | Invoice, Credit note | Reversed ₹600 in March 3B Table 4B |
Net Position Summary
- Original Mismatch: ₹ [AMOUNT]
- Less: Timing differences resolved: ₹ [AMOUNT]
- Less: ITC reversed in [Month]: ₹ [AMOUNT]
- Less: Supplier filed late, now in 2B: ₹ [AMOUNT]
- Net Remaining: ₹ [AMOUNT]
Format 4: ITC Reversal Register (Rule 42/43)
ITC Reversal Register
📥 Download Excel Format| Month | Total Turnover | Exempt Sales | Exempt % | Total ITC | ITC to Reverse | Reversed in 3B | Remarks |
|---|---|---|---|---|---|---|---|
| Jan-25 | 15,00,000 | 2,00,000 | 13.33% | 1,50,000 | 20,000 | Table 4B(2) | Rule 42 |
| Feb-25 | 18,00,000 | 1,50,000 | 8.33% | 1,75,000 | 14,583 | Table 4B(2) | Rule 42 |
| Mar-25 | 20,00,000 | 3,00,000 | 15.00% | 2,00,000 | 30,000 | Table 4B(2) | Rule 42 |
- Exempt % = (Exempt Sales / Total Turnover) × 100
- ITC to Reverse = Total ITC × Exempt %
Format 5: Supplier Follow-up Tracker
Supplier Follow-up Tracker
📥 Download Excel Format| Supplier | GSTIN | Invoices | ITC Books | ITC in 2B | Pending ITC | Last Follow-up | Next Follow-up | Status |
|---|---|---|---|---|---|---|---|---|
| ABC Suppliers | 27AABCU... | 12 | 45,000 | 45,000 | 0 | - | - | Compliant |
| XYZ Traders | 29DEFGH... | 8 | 32,400 | 18,000 | 14,400 | 10-Feb | 25-Feb | Pending |
| KLM Enterprises | 19UVWXY... | 15 | 67,500 | 54,000 | 13,500 | 15-Feb | 28-Feb | Partial |
📱 Scroll horizontally to view all columns on mobile devices
Format 6: Monthly Checklist
Pre-Filing (Before 20th)
- ☐ GSTR-2B downloaded
- ☐ Purchase register exported
- ☐ ITC matching completed
- ☐ Mismatches identified
- ☐ Supplier follow-ups sent
- ☐ Excess ITC reversal calculated
- ☐ Rule 42/43 reversals done
- ☐ GSTR-3B draft prepared
- ☐ Books vs 3B reconciliation done
- ☐ Management approval obtained
Post-Filing (Within 5 days)
- ☐ GSTR-3B acknowledgment saved
- ☐ Payment challans matched
- ☐ Variance register updated
- ☐ Pending supplier list prepared
- ☐ Follow-up emails sent
- ☐ Next month planning done
Part 6: Frequently Asked Questions
What's the difference between GSTR-2A and GSTR-2B?
GSTR-2A changes in real-time as suppliers file. GSTR-2B is static, generated on 14th of the month showing data up to 13th. Since January 2021, GSTR-2B is the official statement for ITC reconciliation. Always use GSTR-2B for filing decisions.
Can I claim ITC if invoice isn't in GSTR-2B?
No. From January 2021, you can only claim ITC for invoices in GSTR-2B. Claiming without 2B support triggers mismatch notices and requires reversal with interest. Exception: import of goods and reverse charge supplies.
What if supplier hasn't filed GSTR-1?
Provide original invoice, payment proof, and follow-up evidence in notice response. Show you reversed ITC in subsequent GSTR-3B Table 4B. Re-claim when supplier files and invoice appears in your GSTR-2B.
What's the penalty for excess ITC claim?
Excess ITC amount plus 18% interest per annum from claim date to reversal date. For deliberate fraud, penalty can be 100% of tax amount. Monthly GSTR-2B reconciliation prevents this.
How far back can department audit?
Normal period: 3 years from annual return due date. For fraud or suppression: 5 years. Maintain reconciliation records for minimum 5 years.
How do I handle timing differences?
Create timing difference register showing invoice number, booked month, supplier filing month, amount, expected resolution. Document in reconciliation with specific details. Officers accept genuine timing differences if properly documented and reversed in subsequent months.
Why doesn't Tally export credit notes properly?
Credit Note Register lacks item-wise export—this is a design limitation. Purchase add-on from Tally Partners (₹800–1,000) that combines sales and credit notes with item details. Manual export gives only summaries without HSN codes auditors need.
Do I need Power Query or can I use simple formulas?
Simple VLOOKUP formulas work fine for most businesses. Power Query is optional for those comfortable with advanced Excel—it automates refreshing and handles messy data better. But invoice-level matching works perfectly with basic formulas.
What is IMS in GST?
IMS (Invoice Management System) on GST portal shows invoice-level matching between GSTR-2B and GSTR-3B. It highlights mismatches and lets you accept/reject invoices. Use for real-time supplier compliance monitoring.
How often should I reconcile?
Download GSTR-2B on 14th of every month, complete reconciliation before filing GSTR-3B by 20th. Monthly discipline prevents year-end chaos and catches errors when fresh in memory.
Conclusion
GST audit preparation requires monthly discipline, not last-minute scrambling.
✅ Key Takeaways
Invoice-level reconciliation with specific explanations and supporting documents.
GSTR-3B vs Books and GSTR-2B ITC matching are your primary defense.
₹800–1,000 for sales with credit notes saves hours and ensures audit compliance.
VLOOKUP formulas achieve invoice-level matching. Power Query is optional for advanced users.
Download GSTR-2B on 14th, reconcile before 20th filing. This single habit prevents 80% of audit issues.
GSTR-2B is the official static statement since January 2021.
🚀 Getting Started
Start with one format this month. Master invoice-level matching. Build your system gradually. Within a quarter, you'll have a routine that works smoothly.
💡 Remember:Reconciliation isn't just compliance—it's financial discipline that improves cash flow, reveals supplier issues early, and gives management real-time tax position visibility.
📚 Related Resources from Learn With Amrut
📖 Featured Articles
Need Help with GST Audit Preparation?
Subscribe to Learn with Amrut on YouTube for step-by-step video tutorials on Tally export, Excel reconciliation, and GST compliance
Need personalized support? Contact us for GST audit training and consulting services