Training By Himanshu

Tag: Excel GST Template

  • How to Prepare GST Summary Report in Excel: A Complete Step-by-Step Guide for Businesses

    Preparing a detailed and accurate GST Summary Report in Excel is essential for GST return filing, internal control, sales analysis, purchase analytics, input tax credit (ITC) tracking, and audit readiness. Whether a business files monthly GSTR-3B or quarterly returns, a GST summary prepared in Excel ensures that every taxable value, rate-wise bifurcation, and tax amount is verified before uploading to the GST portal.

    Excel is the easiest and most flexible tool for preparing GST reports because it allows customized formulas such as SUMIF, SUMIFS, VLOOKUP, INDEX MATCH, Pivot Tables, and Conditional Formatting. In this guide, you will learn how to prepare a professional GST Summary Report in Excel from raw sales and purchase data.

    1. Understanding GST Summary Report

    A GST Summary Report is a consolidated statement that summarises business transactions under GST for a specific period, usually monthly or quarterly. It includes total outward supplies, inward supplies, taxable value, tax rate, CGST, SGST, IGST, exempt supplies, and ITC.

    Typical values included:

    • Total Sales
    • B2B Sales
    • B2C Sales
    • Zero Rated Supplies
    • Nil/Exempt Supply
    • Total Purchases
    • Input Tax Credit (ITC) Availed
    • RCM Liability
    • Net Payable

    2. Data Required to Prepare GST Summary in Excel

    To prepare the GST report, you need:

    1. Detailed Sales Register
    2. Detailed Purchase Register
    3. Debit Notes and Credit Notes
    4. Expenses on which RCM is applicable
    5. Opening and Closing ITC values (if needed)

    A typical sales or purchase register contains:

    • Invoice Number
    • Invoice Date
    • Customer / Supplier Name
    • GSTIN
    • Place of Supply
    • Taxable Value
    • GST Rate
    • CGST, SGST, IGST Amount
    • Total Invoice Amount

    With this data, we can prepare the summary.

    3. Formatting Data to Create GST Summary Report

    The first and most important step is cleaning the raw data. GST data often contains duplicates, wrong tax calculations, missing GSTINs, and unclassified transactions, so formatting is necessary.

    Important Formatting Steps

    1. Convert data into Excel Table (Ctrl + T).
    2. Ensure dates are correct using proper date format.
    3. Verify GST rates: 0%, 5%, 12%, 18%, 28%.
    4. Apply Conditional Formatting to highlight blank GSTIN or wrong rates.
    5. Remove duplicates using Data → Remove Duplicates.
    6. Add rate-wise columns for summary preparation.

    4. Creating Rate-Wise GST Summary Using SUMIFS

    To create a proper GST summary, calculate the taxable value and GST amount rate-wise.

    Example formula:

    To calculate taxable value for 18% sales:

    =SUMIFS(Sales!G:G, Sales!H:H, 18)

    To calculate CGST on 18% rate:

    =SUMIFS(Sales!I:I, Sales!H:H, 18)

    To calculate SGST on 18% rate:

    =SUMIFS(Sales!J:J, Sales!H:H, 18)

    To calculate IGST:

    =SUMIFS(Sales!K:K, Sales!H:H, 18)

    This structure helps create a clean summary section.

    5. GST Summary Table Format (2 Column Table)

    Below is a simple 2-column GST Summary table for presentation.

    https://exceldatapro.com/wp-content/uploads/2017/09/Monthly-GST-Input-Output-Tax-Report.jpg
    https://vyaparapp.in/v/z/wp-content/uploads/2023/08/GST-Reconciliation-Format-in-Excel-excel-01.webp

    GST Summary Table

    ParticularsAmount (₹)
    Total Outward Taxable Value18,50,000
    CGST Collected1,66,500
    SGST Collected1,66,500
    IGST Collected2,10,000
    Zero Rated Supplies3,50,000
    Exempt / Nil Rated Supplies1,10,000
    Inward Purchases (Taxable)14,25,000
    ITC – CGST1,25,500
    ITC – SGST1,25,500
    ITC – IGST1,90,000
    RCM Liability42,000
    Net GST Payable1,02,000

    Values above are an example for representation.

    6. Creating Pivot Table for GST Summary

    Pivot Table is the most accurate tool for GST summary creation because it offers automatic grouping of tax rates, GST categories, supplies type, and place of supply classification.

    Steps to Create Pivot Table

    1. Select the sales register.
    2. Go to Insert → Pivot Table.
    3. Drag “GST Rate” to Rows.
    4. Drag “Taxable Value”, “CGST”, “SGST”, “IGST” to Values.
    5. Apply Number Formatting.
    6. Create one pivot for sales and one for purchases.
    7. Prepare a final summary table using GETPIVOTDATA formula.

    Example Pivot Output

    GST RateTaxable Value
    0%1,10,000
    5%2,85,000
    12%3,75,000
    18%9,80,000
    28%2,10,000
    Total18,50,000

    7. Calculating Input Tax Credit (ITC) in Excel

    To get accurate ITC report:

    Checkpoint 1: Supplier GSTIN must be valid

    Use LEN and ISNUMBER for validation.

    Checkpoint 2: Reverse tax entries (Credit Notes)

    Calculate:

    ITC Net = ITC on Purchases – ITC Reversed – ITC Ineligible

    Sample ITC Calculation Table

    ITC TypeAmount (₹)
    ITC on Purchases4,41,000
    ITC on RCM Services38,000
    ITC Reversed (Rule 42/43)22,000
    Ineligible ITC18,000
    Net ITC Available4,39,000

    8. Creating Final GST Summary Report Format

    A complete GST Summary Report should contain:

    A. Business Details

    • Company Name
    • GSTIN
    • Return Period
    • Return Type (Monthly/Quarterly)

    B. Outward Supply Summary

    • B2B Sales
    • B2C Large
    • B2C Small
    • Zero Rated
    • Exempt

    C. Inward Supply Summary

    • Registered Purchases
    • Unregistered Purchases
    • RCM Purchases
    • Import Purchases

    D. Tax Liability Section

    • Rate-wise Taxable Value
    • CGST, SGST, IGST Breakup

    E. ITC Summary

    • ITC Availed
    • ITC Reversed
    • Net ITC

    F. Final Computation

    • Total Output Tax
    • Total ITC
    • Net GST Payable/Refund

    9. Using Excel Functions to Automate GST Summary

    To make the report fully automated, use:

    1. SUMIFS for rate-wise summary

    2. VLOOKUP or XLOOKUP for linking registers

    3. IFERROR to avoid errors in summary

    4. TEXT function for creating invoice-month formats

    5. Pivot Tables for dynamic analysis

    6. Data Validation for GST rate selection

    10. Common Mistakes to Avoid While Preparing GST Summary

    1. Not matching sales register with GSTR-1.
    2. Missing reverse charge entries.
    3. Wrong GST rate applied in some invoices.
    4. GSTIN written incorrectly due to manual typing.
    5. Duplicate invoices in raw data.
    6. Not reconciling with GSTR-2B for ITC verification.
    7. Not preparing HSN summary separately.

    11. Tips for Accurate Monthly GST Summary

    1. Maintain one standard sales register format across months.
    2. Ensure all debit/credit notes are adjusted in the same month.
    3. Use Excel Tables for dynamic formula adjustment.
    4. Keep separate columns for CGST, SGST, IGST instead of merged tax.
    5. Use Pivot Table filters to verify rate-wise breakup.
    6. Always reconcile ITC with 2B before final submission.

    12. Sample Format of GST Summary Report (Ready for Excel)

    Below is a ready model you can copy into Excel:

    FieldValue
    GST PeriodJuly 2024
    Total Taxable Sales18,50,000
    Total GST on Sales5,43,000
    Total Purchases14,25,000
    Total ITC4,39,000
    GST Payable1,02,000

    Conclusion

    Preparing a GST Summary Report in Excel becomes extremely easy when you follow a structured approach using correct formulas, Pivot Tables, and rate-wise classification. A well-prepared summary not only helps in accurate GST return filing but also avoids penalties, mismatches, and ITC losses. With the steps explained above, any business can prepare a monthly or quarterly GST Summary Report in a clean, professional, and audit-ready format.

    Disclaimer

    This article is written for educational and informational purposes only. The examples, figures, and values used are generic and may vary based on actual business transactions. Users should verify their GST data and consult a tax professional before filing any statutory returns. The article does not contain any external links and is purely original content.

  • How Excel & Automation Streamline GST Filing – Complete Guide to E-Invoicing, GSTR-3B & Tax Reconciliation

    Under India’s GST regime, accuracy and timeliness are non-negotiable. Every month, businesses must prepare and upload data for GSTR-1, GSTR-3B, and increasingly, e-invoices. Manual preparation—especially when handling hundreds of invoices—can lead to omissions, wrong tax rates, and late filing penalties.

    By combining Excel’s flexibility with light automation (macros, Power Query, pivot tables), even small and medium businesses can achieve an ERP-like workflow: cleaner data, faster reconciliation, and fewer last-minute errors.

    Why Automate GST Filing?

    Manual ChallengeRiskBenefit of Automation
    Multiple invoices across branchesMissed invoicesConsolidated real-time view
    Manual tax rate applicationWrong liabilityPre-validated rates & logic
    Repetitive reconciliationFatigue & keying errorsOne-click match with supplier GSTR-2B
    Last-minute return prepLate filing penaltiesRolling reconciliation each month

    Automation turns GST compliance from a deadline-driven scramble into a routine, predictable task.

    Core GST Tasks Perfect for Excel + Automation

    1. E-Invoicing Preparation – Step-by-Step

    The GST Invoice Registration Portal (IRP) only accepts JSON files following a strict schema. A robust Excel model can prepare, validate, and export e-invoices quickly.

    Understand the Schema

    FieldMandatoryExample
    Invoice NoYesINV-2025/007
    DateYes08-09-2025
    Seller GSTINYes29ABCDE1234F1Z5
    Buyer GSTINYes (B2B)27PQRS5678G2H6
    Place of SupplyYesMaharashtra
    HSN CodeYes84081010
    Taxable ValueYes50,000
    CGST, SGST, IGSTConditionalCGST 9%, SGST 9%
    Item DescriptionYesIndustrial Pump

    Build the Excel Template

    Invoice NoDateSeller GSTINBuyer GSTINPlace of SupplyItem DescHSNQtyRateTaxable ValueCGST%SGST%IGST%
    • Format Date as DD-MM-YYYY
    • Lock Seller GSTIN if it never changes
    • Maintain a rate master (5%, 12%, 18%, 28%)

    Add Data Validation

    • GSTIN: =AND(LEN(A2)=15)
    • HSN: dropdown list from a HSN_Master sheet
    • Dates (current FY): =AND(A2>=DATE(2025,4,1),A2<=DATE(2026,3,31))

    Automate Tax Calculation

    =H2*I2               'Taxable Value
=IF(State="Same",TaxableValue*9%,0)   'CGST
=IF(State="Same",TaxableValue*9%,0)   'SGST
=IF(State<>"Same",TaxableValue*18%,0) 'IGST

    Sample Rows

    Invoice NoDateSeller GSTINBuyer GSTINPlaceItemHSNQtyRateTaxableCGSTSGSTIGST
    INV-2025/00108-09-202529ABCDE1234F1Z527PQRS5678G2H6MaharashtraIndustrial Pump8408101022500050000450045000
    INV-2025/00208-09-202529ABCDE1234F1Z529LMNO9123Z5X4KarnatakaControl Panel85371000170000700000012600

    Export JSON via VBA Macro

    Sub ExportToJSON()
    Dim rng As Range, i As Long, json As String
    Dim fso As Object, txt As Object
    
    json = "{""InvoiceList"":["
    Set rng = Sheet1.Range("A2:A51")
    For i = 1 To rng.Rows.Count
        If rng.Cells(i, 1).Value <> "" Then
            json = json & "{""No"":""" & rng.Cells(i, 1).Value & """,""Dt"":""" _
                   & Format(rng.Cells(i, 2).Value, "dd/mm/yyyy") & """," _
                   & """Buyer"":""" & rng.Cells(i, 4).Value & """},"
        End If
    Next i
    json = Left(json, Len(json) - 1) & "]}"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set txt = fso.CreateTextFile(Environ("USERPROFILE") & "\Desktop\eInvoice.json", True)
    txt.Write json: txt.Close
    MsgBox "JSON File Saved!"
End Sub

    Tip: Validate the JSON in the NIC Offline Tool before uploading. Archive both Excel & JSON for audit readiness.

    2. GSTR-3B Auto-Computation

    • Create pivot tables splitting taxable values by rate
    • Link input tax credit (ITC) sheets to auto-calculate eligible credits
    • Use SUMIFS to consolidate Table 3.1 (Outward Supplies) & Table 4 (ITC)

    3. Input vs Output Tax Reconciliation

    • Match supplier invoices (GSTR-2B) with purchase register using VLOOKUP/XLOOKUP
    • Apply conditional formatting for mismatches
    • Generate exception reports for follow-up

    4. Compliance Dashboards

    • Use Power Query to import Tally data
    • Build slicers for state, branch, GST rate
    • Chart month-wise liability & pending ITC

    Practical Workflow Example

    1. Export sales & purchase registers from TallyPrime
    2. Clean & merge in Power Query
    3. Apply tax formulas and reconciliation rules
    4. Generate variance report against GSTR-2B
    5. Export JSON for e-invoices or prepare GSTR-3B

    Tools That Enhance Efficiency

    ToolUse Case
    Power QueryAutomates repetitive cleaning
    VBA MacrosJSON export, exception reporting
    Pivot TableQuick summaries & rate-wise totals
    Power BIMulti-branch dashboards

    Tips for Error-Free Automation

    • Lock tax rate tables to avoid accidental edits
    • Validate GSTIN, dates, and HSN codes before export
    • Keep a monthly reconciliation log
    • Test macros on sample data before production use

    Conclusion

    Excel isn’t just for number crunching—it’s a cost-effective automation hub for e-invoicing, GSTR-3B computation, and ITC reconciliation. With a well-designed template, data validation, and light VBA scripting, GST filing becomes faster, cheaper, and error-resistant.