How to Track GST in Excel: A Simple Dashboard for Small Businesses
Track GST in Excel with one simple dashboard: taxable sales, output tax, input tax credit, net GST payable and filing status, laid out for Indian small businesses.
For most small businesses in India, GST is not hard to calculate. It is hard to keep track of: which invoices are in, how much input credit you can claim, and how much you will actually pay this month. A simple Excel dashboard solves that.
This guide is about organising your numbers. It is not tax advice. Always confirm rates for your HSN or SAC codes and your filing dates on the official GST portal or with your CA.
What your GST dashboard should show
| KPI | How it's calculated |
|---|---|
| Taxable sales | Sum of taxable value on your sales invoices |
| Output tax | Sum of (Taxable value × GST rate) on sales |
| Input tax credit (ITC) | Sum of eligible GST on purchase invoices |
| Net GST payable | Output tax − Input tax credit |
| Sales by tax slab | Taxable value grouped by rate |
| Mismatched invoices | Purchase invoices not matching your supplier's filing |
Step 1: Set up two clean tables
Create a Sales sheet and a Purchases sheet. Each needs one row per invoice with these columns: Invoice date, Invoice number, Party name, GSTIN, Place of supply, Taxable value, GST rate, CGST, SGST, IGST and Total. Turn each range into an Excel Table with Ctrl + T.
Step 2: Split CGST, SGST and IGST correctly
For a sale within your state, GST is split equally into CGST and SGST. For a sale to another state, the full amount is IGST. If your state is in cell Settings!B2:
- CGST:
=IF([@[Place of supply]]=Settings!$B$2, [@[Taxable value]]*[@[GST rate]]/2, 0) - SGST: the same formula as CGST
- IGST:
=IF([@[Place of supply]]<>Settings!$B$2, [@[Taxable value]]*[@[GST rate]], 0)
Enter rates as percentages (5%, 18%) so the maths stays simple.
Step 3: Calculate the monthly summary
On a Summary sheet, list the months in column A. Then use SUMIFS with a start and end date for each month:
- Output tax:
=SUMIFS(tblSales[CGST],tblSales[Invoice date],">="&A2,tblSales[Invoice date],"<="&EOMONTH(A2,0)) + the same for SGST and IGST - ITC: the same pattern on the Purchases table
- Net payable:
=Output tax − ITC, with a note when the result is negative (a credit to carry forward)
Step 4: Add a slab-wise view
After the GST rate changes that took effect in September 2025, most goods and services fall into the 5% and 18% slabs, with a higher rate for a short list of luxury and sin goods. A PivotTable with GST rate in Rows and Taxable value in Values shows how your sales split across slabs, which helps you spot invoices with a wrong rate.
Step 5: Track filing status
Add a small table with each return, its due date and a Filed? column. Monthly filers usually file GSTR-1 around the 11th and GSTR-3B around the 20th of the next month, but check the GST portal for your own dates and any extensions. Conditional formatting can turn a row red when the due date is near and the return is not filed.
Step 6: Lay out the dashboard
Put four KPI cards at the top (taxable sales, output tax, ITC, net payable), a column chart of net GST by month in the middle, and the slab-wise table and filing tracker at the bottom. Add a slicer for month so you can review any period quickly.
Get a ready-made GST dashboard
If you'd rather not build it yourself, Kpiloom's GST dashboards include the sales and purchase tables, CGST/SGST/IGST split, slab-wise view, ITC tracking and a filing tracker. Popular versions include the Kirana Store GST Dashboard, the Wholesale GST Dashboard and the Pharmacy GST Dashboard, all with Indian rupee and lakh/crore formatting.
Frequently asked questions
Can Excel calculate GST automatically?
Yes. Once each invoice has a taxable value, rate and place of supply, formulas calculate CGST, SGST, IGST and the monthly totals automatically.
How do I calculate net GST payable?
Subtract your eligible input tax credit from the output tax on your sales for the same period. If the result is negative, it is a credit you can usually carry forward.
Is an Excel GST dashboard enough for filing?
It helps you prepare and check your numbers, but returns are filed on the GST portal. Confirm figures with your CA, especially ITC eligibility.
Skip the build. Start with a ready dashboard.
Templates for Excel and Google Sheets, with live previews.