In this article
A clean sales register makes GST returns a 10-minute job. Here is a layout and the formulas.
Columns
| Col | Field | Formula / note |
|---|---|---|
| A | Invoice no. | |
| B | Date | |
| C | Customer GSTIN | blank for unregistered (B2C) |
| D | Place of supply (state code) | =IF(C2="",$K$1,LEFT(C2,2)) |
| E | Taxable value | |
| F | GST rate | 5%, 18% or 40% (or an older rate still applicable) |
| G | Intra-state? | =D2=$K$1 |
| H | CGST | =IF(G2,ROUND(E2*F2/2,2),0) |
| I | SGST | =H2 |
| J | IGST | =IF(G2,0,ROUND(E2*F2,2)) |
K1 holds your own state code (e.g. “07” for Delhi). For B2C sales the place of supply is normally where the customer is; for a simple counter-sale register we assume your own state — adjust if you ship B2C outside your state.
Check the GSTIN
=IF(C2="","B2C",IF(AND(LEN(C2)=15,ISNUMBER(--LEFT(C2,2))),"B2B","CHECK GSTIN"))This catches the common typos (wrong length, missing state code). It does not verify the checksum — use the GST portal search for that.
GSTR-1 summary by rate
Make the register an Excel Table named Sales and build:
B2B taxable at 18%: =SUMIFS(Sales[Taxable value],Sales[Customer GSTIN],"<>",Sales[GST rate],18%)
B2C taxable at 18%: =SUMIFS(Sales[Taxable value],Sales[Customer GSTIN],"",Sales[GST rate],18%)
IGST at 18%: =SUMIFS(Sales[IGST],Sales[GST rate],18%)Put the rates down a column (5%, 18%, 40%) and copy the formulas across. Filter the register by month first, or add a Month column and include it in the SUMIFS.
Need quick one-off numbers? Try our GST calculator.
Stuck on a step? Ask a question and the AI answers using this article.