📎 This article includes 1 downloadable practice file ↓
In this article
The sales register is the base for GSTR-1 and GSTR-3B. Keeping it in a clean Excel table with formulas means the monthly summary takes minutes, and you can trace every number back to an invoice.
Register layout
| Invoice | Date | Customer | Buyer state | Taxable | Rate | CGST | SGST | IGST | Total |
|---|
Make it an Excel Table (Ctrl+T) and put your own state code in a Setup cell (07 for Delhi in the practice file).
Tax columns
CGST: =IF([@[Buyer state]]=Setup!$B$2, ROUND([@Taxable]*[@Rate]/2, 2), 0)
SGST: =[@CGST]
IGST: =IF([@[Buyer state]]<>Setup!$B$2, ROUND([@Taxable]*[@Rate], 2), 0)
Total: =[@Taxable]+[@CGST]+[@SGST]+[@IGST]Same state: half the rate as CGST and half as SGST. Different state (place of supply): the full rate as IGST. Rounding each tax line to paise matches how invoices are printed.
Monthly summary
Taxable value: =SUM(T[Taxable])
Taxable at 18%: =SUMIFS(T[Taxable], T[Rate], 0.18)
Total CGST: =SUM(T[CGST])
Inter-state taxable: =SUMPRODUCT((T[Buyer state]<>Setup!B2)*T[Taxable])
B2C taxable: =SUMIFS(T[Taxable], T[Customer], "Consumer (B2C)")A pivot with Rate in rows and Taxable, CGST, SGST, IGST as values gives the rate-wise table most returns need.
Checks before filing
- Invoice numbers continuous and unique (COUNTIF for duplicates; MAX – MIN + 1 = count).
- Every B2B row has a valid-looking GSTIN (15 characters).
- Total of CGST equals total of SGST.
- Register totals tie to the books (sales ledger) for the month.
Where people go wrong
| Mistake | Effect |
|---|---|
| CGST and SGST both at the full rate | Tax doubled |
| State code typed as a number | “07” ≠ 7; every Delhi sale treated as inter-state |
| Rounding only the total | Paise differences against the portal |
More: GST formulas, reconciling purchases with GSTR-2B.
Tax rates, thresholds and rules in this lesson are examples to teach the Excel method. Check current law and your adviser before using figures for filing.
Practice
Download the workbook below. Build each figure in the yellow column of the Practice sheet; the Check column turns green when it matches, and the Answers sheet has a working formula for every task.
📎 Practice files for this article
- 📗Practice workbook40 September invoices with buyer state codes and GST rates.⬇ XLSX · 16 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.